I am looking to automatically round up cost to either $_4.99 or $_9.99.
The idea for this is if I have a cost of $83.58, I would like it rounded up to $84.99. If I have $97.99 I would like that rounded to $99.99. I would like it rounded to the nearest 4.99 or 9.99.
Is there a way to do this?
[Re-Titled by Moderator]
iMac 24″, macOS 15.4
Actually, both models have a problem -
The FLOOR() function, as written, always returns a x9.99 price (as you can see in B2 which should be 84.99, not 89.99. The correct formula would be:
=FLOOR(A2,10)+IF(MOD(FLOOR(A2,5),10)=0,4.99,9.99)
You have to drop the price to the previous $10, then add either 4.99 or 9.99.
Also, my CEILING() version has a problem with prices that are exact multiples of $5. That can be accounted for with a simple penny addition:
=CEILING(B2+0.01,5)−0.01
Actually, both models have a problem -
The FLOOR() function, as written, always returns a x9.99 price (as you can see in B2 which should be 84.99, not 89.99. The correct formula would be:
=FLOOR(A2,10)+IF(MOD(FLOOR(A2,5),10)=0,4.99,9.99)
You have to drop the price to the previous $10, then add either 4.99 or 9.99.
Also, my CEILING() version has a problem with prices that are exact multiples of $5. That can be accounted for with a simple penny addition:
=CEILING(B2+0.01,5)−0.01
I think this does what you want:
With the value to be rounded up in A2:
=FLOOR(A2,5)+IF(MOD(FLOOR(A2,5),10)=0,9.99,4.99)
FLOOR rounds down to the nearest multiple of 5.
If the result is a multiple of 10 add 9.99, if not add 5.99.
SG
Ha - I took the opposite approach - while you used FLOOR() to round down and then add either 4.99 or 9.99, I went the other way and rounded UP to the next 5 and subtracted 0.01:
=CEILING(A2,5)−0.01
CEILING(A2,5) rounds up to the next $5, then I subtract one cent.
Thank you so much everyone, that is fantastic! Worked a treat. I really appreciate everyone's assistance.
Thank you so much everyone, that is fantastic! Worked a treat. I really appreciate everyone's assistance.
How to implement automatic rounding to $4.99 or $9.99?
