I'm trying to figure out how to get my results to round to the closest integer ending in 5.
Example: 550.33 would round to 555, 367.2 would round to 365.
I've tried all sorts of round and mround equations and they all round to the nearest multiple of 5. I'm stumped on this one.
I would use this:
=MROUND(A1 + 5, 10) - 5
NOTE: Go to Tools > Addins and make sure the Analysis Toolpak is enabled.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Try:
=((INT(A1/10))*10)+5
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks so much. The MROUND was easier to type out so I used that one.
=5+INT(A1/10)*10
Any easier? And you don't need the Analysis Pack ... which might be a factor if others have to use the workbook.
Regards
An advantage of TM's INT formula is that it works for both positive and negative numbers without modification.
Entia non sunt multiplicanda sine necessitate
You could also use ROUND
=ROUND(A1+5,-1)-5
Audere est facere
Good catch, SHG. If negative numbers are a possibility, use TMShucks. Mine would have to change to this to work for pos/neg numbers:
=(MROUND(ABS(C17)+5,10)-5)*SIGN(C17)
EDIT: DLL wins again! Nicely simple and I learned something, I never knew you could -1 the second ROUND() param.
Last edited by JBeaucaire; 03-17-2011 at 07:26 PM.
Yup. And that has the additional advantage of being symmetric about 0:Originally Posted by daddylonglegs
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks