Division by Zero 5

[tdiboll]

I am doing a profit percent report. What do I do if I have negative amounts? My forumula pops up with the division by zero error. What do I need to add to the end to correct this.

 

[synapsevampire]

Negative numbers do not produce a divide by zero error, trying to divide by zero (or perhaps null depending upon your settings).

Generally you would test for null/zero in the divisor as follows:

If not(isnull({table.divisor}))
and
{table.divisor} <> 0 then
{table.field}/{table.divisor}
else
0

-k

 

[mattalin]

I have a similar problem. I am trying to calculate % complete. and used the above formula...however, I get a zero result.

Here is my formula:

If not(isnull({@BAC}))
and
{@BAC} <> 0 then
{@BCWP}/{@BAC}
else
0

The funny thing is that when I run my report...I have zero values for @BAC in some areas but all of my % complete calculations show 0. Could the fact that I am dividing formulas be the problem??

 

[Ngolem]

What is in the formula @BAC...this is where you should test/correct for being NULL...not in subsequent formulas

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mattalin]

This is what I have in my @BAC formula:

IF {Virtual RRM.rrmValueType} = 'S' THEN {Virtual RRM.rrmTDlrs} ELSE 0

The above formula is used to report baseline information.

 

[pgtek]

hi

try this

IF {Virtual RRM.rrmValueType} = 'S' or
isnull({Virtual RRM.rrmValueType})
THEN 0
ELSE
{Virtual RRM.rrmTDlrs}

cheers





pgtek

 

[mattalin]

pgtek,

I don't seem to have a problem with @BAC, it extracts the correct data...the problem I am having is when creating a % complete formula using @BAC as the denominator:

If not(isnull({@BAC}))
and
{@BAC} <> 0 then
{@BCWP}/{@BAC}
else
0

It took care of the divide by zero error but now all my results are 0.

Here's an example of what I am trying to do:

Task BCWP BAC % Complete
1 500 1000 50%
2 250 1000 25%
3 500 2000 25%
4 750 1000 75%

I am able to get the BCWP and BAC data but when I try the above formula to calculate % complete I keep getting zero's. (I am grouping by Task)

 

[bdreed35]

I think you need to modify the formula slightly. The number is between 0 and 1 and you probably don't have the field formatted for decimal places.

Change your formula so that you multiply the number by 100 like this:

If not(isnull({@BAC}))
and
{@BAC} <> 0 then
({@BCWP}/{@BAC}*100)
else
0

You can also add the % sign from the formatting toolbar if desired.

~Brian

 

[mattalin]

I do have the format set at %. I also tried your formula suggestion but still get zeros throughout.

 

[midearth]

Just curious - where is your % Complete formula inserted into the report?

 

[mattalin]

I currently have it in the task group header. I tried to place it in the footer but same result.

 

[midearth]

OK. I can understand why it's not working in the header, but should be working in the footer.

 

[pgtek]

hi
try this

If isnull({@BAC})
and
{@BAC} = 0
then
0.0
else
totext({@BCWP}/{@BAC})*100


place this formula in a text box and add % after you insert the formula
you will have
either
0.0%
or
50%


cheers







pgtek

 

[mattalin]

I get the following error when I use this formula:

&quot;A number or currency amount is required here&quot; - looks like it is looking for something after 'else'

 

[pgtek]

hi
try this
If totext(isnull({@BAC}))
and
totext({@BAC}) = &quot;0&quot;
then
&quot;0.0&quot;
else
totext({@BCWP}/{@BAC})*100

place this formula in a text box and add % after you insert the formula
you will have
either
0.0%
or
50%


cheers










pgtek

 

[mattalin]

Now it tells me a boolean is required between the If and totext(isnull({@BAC}))

 

[synapsevampire]

I think PGTEK is trying to do something like:

If isnull({@BAC}) //I don't think that this is ever the case
OR // NOT AND
trim(totext({@BAC})) in [&quot;0&quot;,&quot;&quot;] // Check for blank
then
&quot;0.0&quot;
else
totext(({@BCWP}/{@BAC})*100,6,&quot;&quot //Parenthetical error and added precision

Note that the AND would never evaluate correctly.

I didn't review all of your post, but I'm confident that you haven't explored your data fully as my original post was correct.

If {@BCWP} is zero, then you'll get zero, and if you haven't exposed enough decimal places, then the number may appear to be zero when it is just a small number, as totext may lose precision.

I've no idea why you're converting this to a string, just use:

If isnull({@BAC})
OR // NOT AND
{@BAC} = 0
then
0
else
({@BCWP}/{@BAC})*100

Right click the result and format the number to have greater decimal precision.

Also place the
{@BCWP}
{@BAC}

fields alongside so you can see what they contain.

-k

 

[synapsevampire]

I guess I forgot the braces, or they were stripped in the redo of PGTEKS formula:

If isnull({@BAC}) //I don't think that this is ever the case
OR // NOT AND
trim(totext({@BAC})) in [&quot;0&quot;,&quot;&quot;] // Check for blank
then
&quot;0.0&quot;
else
totext(({@BCWP}/{@BAC})*100,6,&quot;&quot //Parenthetical error and added precision

 

[synapsevampire]

Stewpud TGML got me:

If isnull({@BAC}) //I don't think that this is ever the case
OR // NOT AND
trim(totext({@BAC})) in [&quot;0&quot;,&quot;&quot;] // Check for blank
then
&quot;0.0&quot;
else
totext(({@BCWP}/{@BAC})*100,6,&quot;&quot //Parenthetical error and added precision

-k

 

[mattalin]

Now all I am getting is 0 or 100.

Here is my data (located in the grouped task footer):

@BCWP for task x = $10,592
@BAC for task x = $13,948
@test (formula trying to make) is showing &quot;100.000000&quot;

Answer should be 75.939203