Exceljet

Quick, clean, and to the point

Round price to end in .45 or .95

Excel formula: Round price to end in .45 or .95
Generic formula 
=CEILING(price,0.5)-0.05
Explanation 

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.

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:

=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

About CEILING

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

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.