Excel’s ROUND() function works great if you want to round of a number to the nearest whole number. But there may be situations where we want to Round off to some nearest fraction. Say, you want to Round off a number to the nearest quarter. How do you do that?

Let’s see with an example. Lets say we have a value $1.65 and we want it to be displayed as $1.75.(or if it was $1.60 it should be displayed as $1.50). We can still use Excel’s ROUND() function to achieve this but with a little twist. Assuming that the value is in cell A1, the following formula will do the trick:

=ROUND(A1/0.25,0)*.25

The formula divides the original value by 0.25, rounds it up and then multiplies the result by 0.25. Rounding off to other fractions is equally easy. For example, to round off to the nearest 5 cents, simply substitute 0.05 for each of the two occurrences of 0.25 in the above formula.

I would be interested to know if there are other ways to do this.