Division by Zero Workaround? 1

[loveyoursite]

CRV10 I have this formula:

If {#Term} = 0 Then
0
Else
Truncate (({#Term} / {#Active}) * 100,2)

The formula works fine but when you are paging through the report, when you reach a certain page, the "Division by Zero" caution message pops up. I believe it is because on this particular page, in one department, the number of active is 0 and the number of term is 1 and you can't divide 1 by 0. Does anyone know a way around this?

 

[rosemaryl]

Dear Loveyoursite,

You need to test both sides of a formula or field that will be used in a division formula:

If {#Term} = 0
or
{#Active} = 0
Then
0
Else
Truncate (({#Term} / {#Active}) * 100,2)

By the way, in CR 8.5 and higher you can use the % sign as the divide side and it will automatically multiply by 100 to give you percent.

Else
Truncate (({#Term} % {#Active}),2)

regards,

ro




Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[Turkbear]

Hi,
Test for both:

If ({#Term} = 0 or {#Active} = 0 ) then
0
Else
Truncate (({#Term} / {#Active}) * 100,2)




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

You only need to test for {#active}:

If {#Active} = 0 ) then
0
Else
Truncate (({#Term} / {#Active}) * 100,2)

It will return 0 if {#active} is 0.

I assumed that this was a Running Total, if it is a database field with a # in front of it, then also check for null:

If isnull({#Active})
or
{#Active} = 0 ) then
0
Else
Truncate (({#Term} / {#Active}) * 100,2)

-k

 

[rosemaryl]

Dear SV,

While technically correct <smile> as 0 / 1 is 0 but ... I always test both sides.

I force the 0 and because I may change my mind later (read that as made a mistake on which one was supposed to be the divisor) I don't have to modify the formula as much.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

Hello again, Ro.

Seems like no more work to change which is the divisor as it is to copy and paste which is on the rigth side of the divided sign (or % as you intelligently pointed out).

Less processing is always my objective.

-k

 

[rosemaryl]

Dear SV,

Of course you are right ... I guess I picked up a bad habit

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[loveyoursite]

Thanks so much to you all. I so much appreciate this forum. I ended up using the formula provided by synapsevampire. One problem though,

I just realized that on my report, if the active number of employees in a dept is 0, the term is 1, then turnover is 100%, however, only the term 1 shows up on the report. How can I get the "0" for active and the "100%" for turnover to show on the report as well? Example:

Active Term Turnover
73 2 2.73
1