Analyzing survey data

You could start with finding column number (probably easier on raw data than such "somewhat digested", as you presented) of the answer for each respondee and then calculate difference between these numbers.

Either sum ABS values of thes differences or (in my opinion better) use squared values of such differences.
This way you will neglect minor differences like ageree-strongly agree but focus on large differences strongly agree-strongly disagree.

See attached where retrieving information on column number for each answer has been done with MATCH and proper row to be matched retrieved with OFFSET function.

Originally Posted by Kaper

You could start with finding column number (probably easier on raw data than such "somewhat digested", as you presented) of the answer for each respondee and then calculate difference between these numbers.

Either sum ABS values of thes differences or (in my opinion better) use squared values of such differences.
This way you will neglect minor differences like ageree-strongly agree but focus on large differences strongly agree-strongly disagree.

See attached where retrieving information on column number for each answer has been done with MATCH and proper row to be matched retrieved with OFFSET function.

Thanks for your help, I truly appreciate it.

I actually have in my other excel spreadsheet a raw data sheet where answers of the 75 original questions are coded into numbers, the spreadsheet you saw was requested to look that way.

I don't know much about excel but is the squared values the same as the Pearson and the Correlation formula in excel? If so, the comparison will be between pairs, it can't be, for example: the 10 respondents, right?

Yes, the sum of squared distances will be will be a measure of difference between pair.