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

Try this: =IF(CEILING(A1,5)=A1,CEILING(A1+0.01,5)-0.01,CEILING(A1,5)-0.01)

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.

IF(CEILING(A1,5)=A1...

Since e.g. 90 and 95 are already at the CEILING when using a

significanceargument of 5, we need to bump those values up by .01 to force the CEILING to round up to the next significant 5.CEILING(A1+0.01,5)

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

CEILING(A1+0.01,5)-0.01

or

CEILING(A1,5)-0.01

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

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History