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.
You May Also Be Interested In...
- An Excel AddIn to show Currency in Indian format and Words
- Excel 2007 – The 65535 & 65536 problem
- Few Excel Tips to make you more productive
- How to install and Addin in Excel 2007 and Excel 2010
- How to change the default file type for Word 2007 and Excel 2007
- How we put Zoho Sheet to good use at Gconnect.in
- Get your documents converted to PDF for free
- How to Unprotect a Protected Excel Worksheet
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
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 }
Nice job, that really helped me.
Thanks for the addin.. it exactly met my requirements.. thanks once again. 10/07/2007
This formula really helper. Thanks dude
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.
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 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
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.
thanks a lot…this is exactly what i wanted…
keep up with the good work!
can anyone tell me how to copy text and figure automaticaly in another cell in excel
i wnat a value to next higher rupee
if the value is 36.01 i want it to be 37 please send formula
Verry usefull me .
Thanks
=ceiling(cell,significant)