How do I recode ACE-R scores (prior to summing) in EXCEL?
Recoding continuous scores into groups
Suppose we wish to standardise scores based upon which interval they are in using EXCEL. Suppose scores less than 2 are coded as 0, those between 3 and 5 as 1 and so on upto scores of 17 and higher coded as 7.
This can be done in at least two ways firstly using nested IFs as below.
=IF(D3<2,0,IF(D3<3,1,IF(D3<5,2,IF(D3<7,3,IF(D3<10,4,IF(D3<13,5,IF(D3<17,6,7)))))))
You can also sum IFs if you additionally use the AND function as given below. This method is slightly less onerous in not needing to match so many brackets at the end of the statement and also is usable for more than seven intervals unlike the nested IFs since EXCEL allows only upto seven nested IFs in one statement.
=IF(AND(D3>2,D3<3),1,0)+IF(AND(D3>=3,D3<5),2,0)+IF(AND(D3>=5,D3<7),3,0)+IF(AND(D3>=7,D3<10),4,0)+IF(AND(D3>=10,D3<13),5,0)+IF(AND(D3>=13,D3<17),6,0)+IF(D3>=17,7,0)
Both these computations are illustrated in this EXCEL spreadsheet.
Checking correct subtractions
In addition the participants are also asked questions of the form “take away 7 from 100” and are asked to do this 5 times ie. the correct answer should be 93, 86, 79, 72, 65. Their verbal responses are then put into the spreadsheet. The score that they get is for each correct subtraction of 7. So for example if they responded “93, 86, 79, 72, 65” then they would get a score of 4.
The below shows how to score the results in EXCEL bearing in mind some of the mixed, incorrect responses are as follows:
Suppose the five numbers are in cells A1,B1,C1,D1 and E1 ie each set of five numbers is a separate row
e.g. A B C D E 93 86 82 75 68 row 1 100 93 91 80 777 row 2
Then the score showing the number of correct subtractions by 7 is:
=IF(B1=A1-7,1,0)+IF(C1=B1-7,1,0)+IF(D1=C1-7,1,0)+IF(E1=D1-7,1,0)
So we get a ‘1’ if the current number is seven less than the preceding one or A ‘0’ otherwise.
The EXACT function for comparing strings
Another useful function is the EXACT function which checks to see if a string has a particular form: If we have two cells (anywhere in the EXCEL file) containing the strings dlrow and DLROW. So, for example, we could put dlrow in cell E1 and DLROW in cell F1. The Exact function then compares the strings in a particular cell in a column (e.g. A1) to, for example, assess whether a participant has given either of two correct backward spellings (allowing capitals and lower case) and returns a 1 if it finds a match (ie is correct) or zero otherwise.
Putting the below in an empty cell displays the result:
=IF(EXACT(A1,E$1)+EXACT(A1,F$1)>0,1,0)
The EXACT function is case insensitive so that DLrow would not be regarded as a correct response if compared to dlrow due to the uppercase letters. To compare ignoring differences in case we can use the IF function. For example, putting dlrow in cell E1 and comparing to the response in cell A1 we have
=IF(A1=E$1,"same","different")
More EXCEL code for a more complex case is described at the link below