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=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
13 Replies
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.
PeterBartholomew1 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.
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.
@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)))
PeterBartholomew1 Thanks. I will fiddle with it some more.
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.
- Hello,
I believe using PivotTable will make life much easier for you than trying to write complicated formula- 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
