I am required to analyse respondent data in an spss data sheet but i believe it may be best to copy this data into excel or google sheets for this particular analysis I am about to describe.
600+ participants have been asked to respond to 100 questions. The participants will each have their own row in the data and their response to the questions will be in 100 columns from left to right in their respective row. Each response will either be coded as 1 or 2 (it is a binary response with only one of these two options to be provided in each column/cell).
There should be a trend in the data (left to right) where all participants will reach a point (i.e. column/cell) where they change from strictly responding with a 1 to strictly responding with a 2 from that point onwards. If the participants are paying attention and following logic, they will then only answer 2 for the remainder of their responses.
I need to identify all participants that did not strictly continue answering with a 2 response after switching over to first providing a 2 response instead of strictly 1 responses.
i.e. if someone answers 1, 1, 1, 1, 1, 1 then switches to 2, 2, 2, 2, 2, but switches back to 1 instead of continuing to answer 2 for the remainder of the questions, i need to identify these outliers so that they can be isolated from the rest of the data-set.
Does anyone know of a function (either in excel, google sheets or a platform of your choice) that will allow me to bulk identify which participants erroneously entered a 1 after first changing their response to 2's.
I hope all that makes sense and sorry if it was a bit of a ramble.
You can do a TEXTJOIN() for each row and look for the pattern "21" (i.e. an '1' after a '2'). If found, this is an outliner.
Sth like the followings:
=IF(ISERROR(FIND("21",TEXTJOIN("",TRUE,[data range of a row]))),FALSE,TRUE)
TRUE will be an outliner