Round price to end in .45 or .95

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