Formula Error: calculating percentage missing one value

[lbizzell]

Hello,

Crystal 9 - SQL

I've got a set of formulas:
Extended Price: {co_ship.qty_shipped}*{coitem.price}
Var Cost: {item.cur_matl_cost}+{item.cur_vovhd_cost}
Total Var Cost: {co_ship.qty_shipped}*{@Variable Cost}
Contribution Margin{@Extended Price}-{@Total Var Cost}
Percentage: {@Contribution Margin}/{@Extended Price}*100

I'm getting an error on the Percentage formula if the data for item.cur_matl_cost or coitem.price is zero. Is there a formula I can add to eliminate the error?

Thanks-
Laurie

 

[lbizzell]

I've tried checking both Convert Database NULL values to Default and Convert Other NULL values to Default. I still get the error.

Thanks-
Laurie

 

[Madawc]

Is it a 'divide by zero' error? If so, try

IF item.cur_matl_cost <> 0
AND coitem.price <> 0
THEN {@Contribution Margin}/{@Extended Price}*100
ELSE 0

If it is some other error, please specify.

Note that you can also use IsNull to test for null, rather than removing them all using database settings.

Madawc Williams (East Anglia, UK)

 

[lbizzell]

Perfect!
Thanks so much-
Laurie