MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Number Rounding

Posted by Marc on August 14, 2001 8:58 PM

A simple question I hope:
I would like to be able to round a number in the following way:

When the number is say 9.181 then round it up to 10.000 and when the number is 9.180 round this down to 9.000. It would be good also to be able to round a number to say the nearest .200 (or whatever you want)and not just a whole number as in above. I'm understand when its rounding a number as per normal but how do you only round the number up or down when it reachs a specified trigger point.

I hope you can help and thankyou in advance for your time.


Posted by Robb on August 14, 2001 9:26 PM


This may not be the only way to do this, but using floor/ceiling within IF as a formula should work:


I have used 1 in the example, but you may set the multiples to any value you choose.

Any help?