Here's what I see in Excel's help:
IF(logical_test, value_if_true, value_if_false)
In your sample, you have this:
=IF(AG37=0,"00%",AG28-K28-M28-E28-G28)/AG37
Let's break that down into what Excel will do:
a) If AG37=0, then put "00%" into some temporary location.
b) If AG37<>0, then run the formula AG28-K28-M28-E28-G28, and
store the result in the temporary location.
c) Divide the temporary location (from a or b) by the contents of AG37.
(Your /AG37 is outside of the parenthesis for the IF statement, so
it'll do this division either way!)
So if AG37=0, it'll come up with "%00"/0 which is obviously invalid,
because you can never divide anything by zero.
You state the following:
The above formula works fine, except if AG37 is 0, Then I get a #div
error.
Makes perfect sense. You can't divide anything by zero (not even a
character string!) without getting a division error.
You then suggest the following as a replacement:
=IF(AG37=0,"00%",(AG28-K28-M28-E28-G28)/AG37
This one stuns me, because I don't understand the logic you went through
to come up with that fix. "00%" happens when AG37 is zero -- in other
words, it happens when you have the problem you're trying to solve.
Yet, you didn't do anything to that part of the code when you tried to
solve the problem! Instead you changed the part of the equasion that
was WORKING, by adding a parenthesis before it.
Ironically, it solved the problem. Not because of the extra
parenthesis, but because you forgot the CLOSING parenthesis. Notice
that your equasion has two opening parenthesis (one after IF, one at the
start of your "false" condition). But only one closing parenthesis
(just before the division).
Excel tries to help you when you make a mistake like that. Since the
parenthesis aren't balanced, it adds an additional closing parenthesis
onto the end.
The actual formula is, therefore, the following one:
=IF(AG37=0,"00%",(AG28-K28-M28-E28-G28)/AG37)
Notice that this (by luck) happens to move the division into the false
condition. It's no longer outside the parenthesis, it's now only run
when AG37 is not zero, thanks to Excel tacking on that extra closing
parenthesis.
It changed the meaning of the equasion, but by some stroke of luck, it
ended up being what you intended it to be from the beginning! That the
division is only done when AG37 isn't zero.
Anyway... change your RPG code to add that extra parenthesis at the end,
and you should be good to go:
formulastr = 'IF('+drtot_c + '=0,"00%", '+
%trim(sbtot_c) + '-' +
%trim(gaprt_k) + '-' +
%trim(gaprt_m) + '-' +
%trim(cpirt_e) + '-' +
%trim(cpirt_g) + ')' + '/' +
%trim(drtot_c) + ')';
Good luck
midrange.com
