Re: POI excel formula

[Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>]

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