Solved Formulas CEILING Round up or Round down

June 7, 2013 at 13:53:11
Specs: Macintosh
Using Excel I want to take a series of numbers and round up to the nearest 4.99 if the total amount ends with a number <5.00 or 9.99 if the total amount is >5.00 For Example 94.20 = 94.99 95.00 = 99.99

See More: Formulas CEILING Round up or Round down

Report •

June 8, 2013 at 08:45:37
✔ Best Answer
Try this:


I assume you want to round values that end with 0.00 to 4.99 and values that end in 5.00 to 9.99. If that's correct, we have to check what the CEILING function returns first.


Since e.g. 90 and 95 are already at the CEILING when using a significance argument of 5, we need to bump those values up by .01 to force the CEILING to round up to the next significant 5.


Once that's done, we simply subtract .01 to get back down to .99.




Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question