Rounding values to the nearest fraction in MS Excel

Posted on 14 March, 2007
This item is filed under [MS Office Tricks, Productivity Tips]

March 14, 2007 · 12 comments

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.

Enjoyed this post? Share it with others.
  • email
  • Print
  • Digg
  • StumbleUpon
  • Technorati
  • Reddit
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • FriendFeed
  • BlinkList
  • Diigo
  • LinkedIn
  • Propeller
  • IndianPad
  • MySpace
  • Slashdot

Subscribe Now

If you enjoyed this post, you will definitely enjoy our others. Subscribe to the feed to get future posts delivered right to your mailbox or feedreader.

Powered by Thesis

Thesis Theme

An amazing WordPress Theme, nothing beats the versatility and SEO friendliness of the Thesis framework.

From beginners, to the most advanced WordPress developers, Thesis makes it easy for anyone to customize it.


{ 12 comments… read them below or add one }

Mikeee May 31, 2007 at 1:09 am

Nice job, that really helped me.

Reply

s.goswami July 11, 2007 at 5:57 pm

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

Reply

Marshal July 9, 2008 at 12:51 pm

This formula really helper. Thanks dude

Reply

Lokesh October 22, 2008 at 4:00 pm

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.

Reply

Lokesh October 22, 2008 at 4:02 pm

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

Reply

kuber March 27, 2009 at 5:56 am

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

Reply

Abhilash June 12, 2009 at 7:54 am

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.

Reply

AmanV August 17, 2009 at 9:54 am

thanks a lot…this is exactly what i wanted…
keep up with the good work!

Reply

Sunil November 15, 2009 at 2:04 am

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

Reply

HARVINDER MUDGAL January 28, 2010 at 4:35 am

i wnat a value to next higher rupee

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

Reply

Narendra Patel (Himatnagar) May 31, 2010 at 12:25 am

Verry usefull me .

Thanks

Reply

yelbaut June 1, 2010 at 8:56 pm

=ceiling(cell,significant)

Reply

Leave a Comment

Previous post:

Next post: