Troubleshooting a published Excel formula

[mlmat184]

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=B2,OR(A2=100,A2=0))“ “,IF(A2=B2,0,(B2−A2)/A2)))

However, when I retyped it and tried it in my spreadsheet, I got an error message and I can't figure out how to fix it. Can someone with a better "eye" for formulas than me help? What it should do is the following,

"A student’s pre-test score (out of 100%) is in cell A2 and the
post-test score (out of 100%) is in B2. This formula first
determines if the pre-test score is lower than the post-test
score. If so, it returns the ratio of the gain to the maximum
possible gain. If not, it moves to the next if-statement, where
it determines if the two scores are perfect or 0. If so, it
returns a blank cell; if not, it moves to the final if-statement
where it determines if the scores are equal. If so, it returns 0;
if not, it returns the ratio of the loss to the maximum possible
loss."

Any help would be much appreciated!

MLMAT

[#2]

Hello,

I believe using PivotTable will make life much easier for you than trying to write complicated formula

[#3]

@Abiola1 Thanks, but I don't have experience with Pivot Tables. Just need to finish this one calculation to finish a manuscript on a consulting job...but I will look at Pivot Tables after this job. Thanks for the suggestion.

[#4]

Hello,

PivotTable is very easy to use and understand than formulas. If you can upload the file or a sample one, I can easily create the Pivottable for you

[#5]

@Abiola1 Thanks for the offer but I'm doing this on a consulting basis and am not authorized to share the data.

[#6]

@mlmat184 

A variation on @Abiola1's suggestion: rather than trying to write a single nested (and therefore complicated) IF formula, break it into several columns (adjacent cells) where those several steps are broken down. The sequence might look something like this:

• first determine if the pre-test score is lower than the post-test score. If so, it returns the ratio of the gain to the maximum possible gain, or blank
• second, determine if the two scores are perfect or 0. If so, it returns a blank cell;
• third determine if the scores are equal. If so, it returns 0; or if not, it returns the ratio of the loss to the maximum possible loss.

I wasn't able to access the article through the link you provided (requires membership or a payment), and don't fully understand where this formula leaves you. I'm presuming there's more to follow it in determining a final figure that represents gain or loss or something.

If you have a spreadsheet into which this all fits and are in a position to share it (no confidential or private information), perhaps you could consider uploading it so some of us here could take a look at the full context, as well as at this formula in context.

[#7]

@mathetes THanks. Let me try your suggestion. I may come back with some dummy data to try this. Appreciate the fast response.

[#8]

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

[#9]

@Peter Bartholomew Thanks. I will fiddle with it some more.

[#10]

@mlmat184 @Peter Bartholomew 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)))

[#11]

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

[#12]

@Peter Bartholomew  You wrote: 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.

I guess I'm one of those "hardly anyone else" folks. I do use defined names, but usually for something like RateOfReturn in a "what-if" scenario's list of changeable assumptions. You take it a lot further, it appears. I've never thought of doing so to this extent; you really go to the very basic level, from what you're saying here. By that I mean, I might put a label in an adjacent cell (or column heading) that says "PreTest" but it hadn't occurred to me to use range names for whatever content is IN the cell, and doing so across the board. (Of course, if it's an Excel Table, that naming ends up happening by default now, but that's another story.)

You're absolutely right, though: using defined names as a fundamental practice makes formulas like this FAR more comprehensible, and therefore easier to debug. Thanks for speaking up in this connection. You've gained a convert.

[#13]

@mathetes 

I am glad that I have given you something interesting to experiment with.  I first started working exclusively with named referencing after disagreeing with the FAST financial modelling standard.  How it plays out can depend on whether you prefer to apply a name to a relative referenced cell, an entire range with CSE, an entire range but using implicit intersection.  If you use tables then, of course, the focus is on the structured references to entire columns or to single records using the '@' operator.

Best of all is to use Office 365, in which dynamic arrays offer the path of least resistance and are superbly effective.  What does change is the way one thinks of problems and formulates solutions.

Good luck and stay well!

[#14]

@mathetes 

BTW I forgot to mention that postTest and preTest were not single values.  Each was an array of 121 values {0;10;20;... 100} and {0;0;...10;10;...100} that allowed me to test every such combination of the two scores with one dynamic array formula.