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.

Zoeloo says

A BIG BIG THANK YOU FOR YOUR TIPS!! GOD BLESS YOU!!!!!!

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.

Prahlad Sharma says

verry verry useful me your formul number change in words in excel sheet indian format

yelbaut says

=ceiling(cell,significant)

Narendra Patel (Himatnagar) says

Verry usefull me .

Thanks

HARVINDER MUDGAL says

i wnat a value to next higher rupee

if the value is 36.01 i want it to be 37 please send formula

Sunil says

can anyone tell me how to copy text and figure automaticaly in another cell in excel

AmanV says

thanks a lot…this is exactly what i wanted…

keep up with the good work!

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.

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

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

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.

Marshal says

This formula really helper. Thanks dude

s.goswami says

Thanks for the addin.. it exactly met my requirements.. thanks once again. 10/07/2007

Mikeee says

Nice job, that really helped me.