## Summary

To round prices to end in .45 or .95, depending on whether the original price ends above or below .50, you can use the CEILING function. In the example shown, the formula in C6 is:

``````=CEILING(B5,0.5)-0.05
``````

which rounds prices as shown in the screenshot.

### Rounding rules

In the example shown, the goal is to round prices to end in either .45 or .95, following these rules:

1. If a price is already a whole dollar, the result should be the previous whole dollar + .95. For example, \$3.00 becomes \$2.95.
2. If a price ends in .50 or less, the result should be the current whole dollar + .45. For example, \$4.31 becomes \$4.45.
3. If a price ends in .51 or more, the result should be the current whole dollar + .95. For example, \$5.63 becomes \$5.95.

## Generic formula

``=CEILING(price,0.5)-0.05``

## Explanation

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:

``````=CEILING(B5,0.5) // round up to next half dollar
``````

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:

``````=CEILING(B5,0.5)-0.05
``````

When B5 contains \$17.01, the formula is solved like this:

``````=CEILING(B5,0.5)-0.05
=CEILING(17.01,0.5)-0.05
=17.50-0.05
=17.45
``````

CEILING is one of 8 rounding functions in Excel. You can use CEILING to do things like:

• Round numbers up to multiples of 25
• Round time up to 15 minute multiples
• Round materials up to the next whole unit

The MROUND function and FLOOR function can also round to a given multiple, but the behavior is different from CEILING:

• CEILING rounds up to the next multiple
• FLOOR rounds down to the previous multiple
• MROUND rounds to the nearest multiple Author ### Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.