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

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

Explanation

MROUND rounds to nearest values based on a supplied multiple. When you supply “0:15” as the multiple, Excel internal converts 0:15 into 0.0104166666666667, which is the decimal value that represents 15 minutes, and rounds using that value.

You can also express 15 minutes in a formula with this formula:

=15/(60*24)

The formula above divides 15 by 1440, which is the number of minutes in one day. So, to Excel, these formulas are identical:

=MROUND(B6,"0:15")
=MROUND(B6,15/(60*24))

Round to other time intervals

As you would expect, you can use the same formula to round to different time intervals. To round to the nearest 30 minutes, or nearest 1 hour, use these formulas

=MROUND(time,"0:30") //nearest 30 minutes
=MROUND(time,"1:00") //nearest 1 hour

Always round up

To always round up to the nearest 15 minutes, you can use the CEILING function :

=CEILING(B6,"0:15")

Like MROUND, the CEILING function rounds to a nearest multiple. The difference is that CEILING always rounds up. The FLOOR function can be used to always round down