result of formula rounded to nearest $5

[fancyface]

Hi there. I'm using Crystal 7 on an Oracle database. What I want is comparable to the ceiling function in Excel, rounding the amount up to the nearest $5. I see there is a ceiling function available as a SQL expression however that won't work for me as I'm wanting to round the results of a formula involving 2 shared variables.

 

[bdreed35]

This formula is based off of one I got from Ken Hamady's site.
Try this, but substitute your shared variable names:

whileprintingrecords;
shared numbervar value1;
shared numbervar value2;
Round ( (value1 + value2) / 5 , 0 ) * 5

~Brian

 

[fancyface]

thank you. I think we're stuck though when the amount is less than $5.... the results will go to 0. Any other ideas?

 

[fancyface]

and another example of when it's 5.78. I want it to round UP to the next $5 not down...

 

[lbass]

Try:

whileprintingrecords;
shared numbervar value1;
shared numbervar value2;

-int(-(value1+value2)/5)*5

-LB

 

[synapsevampire]

Why not just add 5 to it?

if var <> 0 then
round((var+5)/5,0)*5
else
0

-k

 

[fancyface]

Hi there. I don't know what this "int" formula is? I don't seem to have that as an option?

 

[fancyface]

Hi there Synapse. If I try yours, based on $62.50, I get $70.00 when really I should get $65.

 

[goranm]

Hi!

If you have the function remainder you can try this one:

if remainder({your quantity},5) > 0 then
{your quantity} - remainder({your quantity},5) +5
else
{your quantity};

/Goran