Round to nearest 100?
How do I add a formula to excel to round a number to the nearest 100?
Eg 1445 would need to be rounded down to 1400 or 1175 would become 1200?
The numbers are already integers. Floor and ceiling formula seem almost to work, but I need to to automatically round up if 50 or over and round down if 49 or under.
Thanks
look at mround, you will need analasys tool pack installed
or =round(a1/100,0)*100
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
Good afternoon twixthecat
You couold use floor if you add 49 to your sum, eg :
=FLOOR(1445+49,100) will return 1400
=FLOOR(1175+49,100) will return 1200
You don't say what your original formula is, so you would have to work that into the solution.
(Or you could use CEILING and -49 to the formula)
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
I feel mround formula is working properly no need to give any extra condition just
`=MROUND(D24,100)
this will round the nearest 100.
floor formula is also works same but if value is 150 then , =floor(a1+49,100) = 100 while mround result will be 200.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
You can use a regular round function, to round A1 to the nearest 100
=ROUND(A1,-2)
Audere est facere
For me it needed to be ROUNDUP rather than just ROUND. This:
=ROUNDUP(A2,-2)
I'm using Excel 2007.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks