How to automatically calculate the SUS Score for a given spreadsheet in LibreOffice Calc?

Question

I have several spreadsheets for a SUS-Score usability test.

They have this form:

                                            | Strongly disagree | | | | Strongly agree |
I think, that I would use this system often |        x          | | | |                |
I found the system too complex              |                   |x| | |                |
(..)                                        |                   | | | |       x        |
(...)                                       |        x          | | | |                |

To calculate the SUS-Score you have 3 rules:

• Odd item: Pos - 1
• Even item: 5 - Pos
• Add Score, multiply by 2.5

So for the first entry (odd item) you have: Pos - 1 = 1 - 1 = 0

Second item (even): 5 - Pos = 5 - 2 = 3

Now I have several of those spreadsheets and want to calculate the SUS-Score automatically. I've changed the x to a 1 and tried to use IF(F5=1,5-1). But I would need an IF-condition for every column: =IF(F5=1;5-1;IF(E5=1;4-1;IF(D5=1;3-1;IF(C5=1;2-1;IF(B5=1;1-1))))), so is there an easier way to calculate the score, based on the position in the table?

Answer 1

I would use a helper table and then SUM() all the cells of the helper table and multiply by 2.5. This formula (modified as needed, see notes below) can start your helper table and be copy-pasted to fill out the entire table:

=IF(D2="x";IF(MOD(ROW();2)=1;5-D$1;D$1-1);"")

• Here D is an answer column
• Depending on what row (odd/even) your answers start you may need to change the =1 after the MOD function to =0
• This assumes the position number is in row 1; if position numbers are in a different row change the number after the $ appropriately