In the example shown, the goal is to round prices to end in either .45 or .95, following these rules:
If a price is already a whole dollar, the result should be the previous whole dollar + .95. For example, $3.00 becomes $2.95.
If a price ends in .50 or less, the result should be the current whole dollar + .45. For example, $4.31 becomes $4.45.
If a price ends in .51 or more, the result should be the current whole dollar + .95. For example, $5.63 becomes $5.95.
How this formula works
The key to solving this problem is to realize that the solution requires a specific kind of rounding. We can't just round to the "nearest" .45 or .95 value. In fact, the first step is to round up to the nearest half dollar (.50). The second step is to subtract 5 cents ($0.05).
To round up to the nearest half dollar, we use the CEILING function, with the significance argument set to .5:
This will round the original price up to the next half dollar. For example, $4.31 will become $4.50, and $5.72 will become $6.00. Importantly, if a price already ends in .00 or .50, it will remain unchanged (i.e. a price of $4.00 or $4.50 is not affected).
Once rounded, the formula simply subtracts 0.05 to get a .45 or .95 result. The formula in C5, copied down, is:
To round prices to end in the nearest, .99 value, you can use the ROUND function then subtract .01. In the example shown, the formula in C6 is: = ROUND ( B6 , 0 ) - 0.01 which rounds the value in B6 to the nearest whole dollar, then subtracts .01...
If you need to round a number to the nearest specified multiple (i.e. round a number to the nearest dollar, nearest $.25, nearest multiple of 5 or 10, etc) you can use the MROUND function. In the example, the formula in cell D6 is = MROUND ( B6 , C6...
To round a time to the nearest 15 minute interval, you can use the MROUND function, which rounds based on a supplied multiple. In the example shown, the formula in C6 is: = MROUND ( B6 , "0:15" ) How this formula works MROUND rounds to...
The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function, but CEILING always rounds up.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.