Chris has a retail store and wants to know how to round prices up to the nearest $.09. Whenever I need to round up, the CEILING function usually does the trick. In this case, there’s one extra step. Assume the unadjusted price is in A1.
Round up to the nearest .09
=CEILING(A1+0.01,0.1)-0.01
Round up to .49 or .99
=CEILING(A3+0.01,0.5)-0.01
Basically, these formulas round to the number just above the number I want, then I subtract a penny. In the first example, it rounds up to nearest dime and the second example rounds up to the neares $.50. The one catch is if you have an even numbered unadjusted price, say $12.00. CEILING won’t round that at all because it’s already there, so I add a penny, then round, then subtract a penny.
Here’s a data table that shows some results of those formulas:
Migrating from a unix based software package, to a Windows Server 2003 platform that leaves such price manipulation to the user via Excel. Both formula’s are a bonus!
Should add:
=CEILING(A3+0.01,1)-0.01
for rounding all to $.99 which I figured out thanks to your first two functions!
Thank you!