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...

excel

Formulas and Functions

PeterBartholomew1

Silver Contributor

Apr 06, 2020

mlmat184

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.