Forum Discussion

Copper Contributor

Apr 06, 2020

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 07, 2020

mlmat184

Stripping down the formula in line with mathetes suggestion and rebuilding one might finish with

= IF( B2=A2, 0, (B2-A2) / IF(B2>A2, 100-A2, A2) )

I still do not like it because of the direct cell referencing but, I suggest, it offers an improvement over the original.