how do I mimic ceiling function? 1

[fancyface]

hi there. I want to mimic the Excel ceiling function by always rounding UP the results of my formula to the nearest $5. I'm not able to use ceiling or int within the SQL expression. Also to let you know I am using Crystal 7.

 

[dgillz]

Crystal 7 is 10+ years old! Upgrade to Crystal XI which includes the ceiling() function, as well as several others.

Alternatively you can mimic this function as follows:

Round({database.field}/5,0)*5


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[fancyface]

I know we're using something old. I absolutely agree with you! I don't think this round function you're suggesting consistently rounds up. It also will round down to the nearest $5 which I don't want. Also, I'm using the round on a running total not on a straight field.

 

[lbass]

Is the truncate() function available to you in CR 7?

-LB

 

[dgillz]

Sorry, I missed the roundup part. Try this:

Round(({database.field+2.4999}/5,0)*5

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[goranm]

Hi!

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

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

/Goran

 

[lbass]

If truncate() is available, you could use:

if {table.field} >= 0 then
(truncate({table.field}/5)*5)+5 else
(truncate({table.field}/5)*5)

-LB

 

[fancyface]

this is just arbitrarily adding $5. If I have $100 I don't want $5. If I have $97 then I want $100. Or if I have $101 then I want $105.

 

[lbass]

Who are you responding to? I tested my truncate formula and it worked the way you wanted it to.

-LB

 

[dgillz]

LB-

If {Table.field} is in integer perfectly divisible by 5, they don't want to add 5 to it. Your formula does not take that into account. I think the formula should be:

if remainder({table.field},5)=0 then
(truncate({table.field}/5)*5)+5 else
{table.field}

However this does not address how to treat negative numbers. Fancyface, any comment on negatives? Should -6 be represented as -5 or -10?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Good catch, Don. I tested the formula with positive and negative numbers, but not with whole numbers exactly divisible by 5. The following works for positive or negative numbers and accounts for this situation:

if {table.field} >= 0 and
remainder({table.field},5) <> 0 then
(truncate({table.field}/5)*5)+5 else
(truncate({table.field}/5)*5)

-LB

 

[fancyface]

Don your Round(({database.field+2.4999}/5,0)*5 seems to be - pardon the pun - right on the money! I tested it over the weekend and compared it to the manual ceiling calculation in Excel and it mimics it exactly! I don't think negative numbers will be an issue in this case because the base will never be less than 0 in my case. I want to thank you so much for this formula. Bumping it up first the "half mark" is exactly the right logic. Thank you everyone for all your posts. It's a huge help. I know I'm so limited by version 7 but I have no choice on that one. So thank you!!