Forum Discussion
Troubleshooting a published Excel formula
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.
