Rounding values to the nearest fraction in MS Excel

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.

Comments

  1. vikas says

    hi.. can u advise if this is possible. for eg.12.10/12.09/12.25 it should be rounded of to the next number i.e. 13. plz advise

    i understand that if its lesser than .49 it remains to the same number and above .50 it jumps to the next number.

  2. Abhilash says

    Hi.

    Can anyone tell me how to round off values in A1 through C86 cells in one shot? That’s 256 cells at one go. Excel 2007 doesn’t seem to have that feature.

  3. kuber says

    Lokesh bhai…. try his…. for any round for 12…. =round(12,0) … for me it automatically came 12 in case of 12.3 and 13 incase of 12.7… hope this helps

    Regards
    Kuber

  4. Lokesh says

    Sir, Give me the IF condition to round off like: 12.49 or less should be rounded to 12 and 12.5 or more should be rounded to 13.
    Please reply as soon as possible.
    Thanks
    Regards
    Lokesh Joshi

  5. Lokesh says

    Sir, I wanted to round off a number. if number is 12.4 then it should be rounded to 12 and if it is 12.5 or more then 13. Give me the If condition so that I can apply it over a range of cells.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>