Forum Discussion
Troubleshooting a published Excel formula
I want to use a formula to calculate gain scores that was published in a journal article (https://aapt.scitation.org/doi/pdf/10.1119/1.2372468). The formula is: =IF(A2<B2,(B2−A2)/(100−A2),IF(AND(A2...
For me the problem was that the minus signs were actually dashes and the quotes were smart quotes. There also seemed to be a comma missing before the quotes. Mind you, I found the presentation of the formula to be a mess; I prefer to use defined names. Thus if 'difference' were
= postTest - preTest
and for 'normalisation' you use
= IF( difference>0, 100 - preTest, preTest )
the result could be expressed
= IF( difference=0, 0, difference / normalisation )
Since hardly anyone else works this way, do not feel any obligation to change; I certainly won't.
@mlmat184 PeterBartholomew1 is correct. the characters - & " and a missing , were the problems. Try this:
=IF(A2<B2,(B2-A2)/(100-A2),IF(AND(A2=B2,OR(A2=100,A2=0)),"",IF(A2=B2,0,(B2-A2)/A2)))