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