- Gašper Kamenšek
- July 15, 2014
- 11 Comments

Excel can be very tricky about rounding numbers. Almost half of Excel users still believe that if they format a cell to round to two decimals, that what they see is what they are calculating with. This of course is not the case. For the purpose of rounding numbers in Excel three functions are available, ROUND, ROUNDUP and ROUNDDOWN. This are well known functions and I’m sure most of the readers know how to use them. But what if you want to round the values to a nearest multiple of 50? So 123 becomes 100, 12 becomes 0, 175 becomes 200 and so on… For this, we need a new set of functions.

MROUND in an equivalent of ROUND but it rounds a number to a multiple.

=MROUND(Number,Multiple)

=MROUND(25,50) gives you 50

=MROUND(24,50) gives you 0

Pretty straight forward, but what if you were set on rounding up to the next multiple of 50.

CEILING in the equivalent of ROUNDUP but it rounds a number to a multiple.

=CEILING(25,50) gives you 50

=CEILING(1,50) also gives you 50

Great so far, but what if you were set on rounding down to the previous multiple of 50.

FLOOR in the equivalent of ROUNDDOWN but it rounds a number to a multiple.

=FLOOR(49,50) gives you 0

=FLOOR(1,50) also gives you 0

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Reblogged this on SutoCom Solutions.

I just KNEW there had to be a way to do this!!! I used to have time to explore the help features or take a class, but I’m so busy now that I just find ‘work-around’ solutions using what I already know. I have Microsoft’s Excel Expert certification for an older version, I’m not a novice user and I really enjoy learning new ways to expand my MS-Office horizons, especially in Excel and PowerPoint.

I love the short, direct format of your tips and you seem to have a knack for knowing the most desirable Excel secrets 😉 This is the third of your helpful hints I’ve saved and you are my new Excel guru.

Thanks so much for sharing with all of us!

Hi,

Your comment really means a lot to me and I thank you for the praise! And it’s worth saying that it’s people like you, that make it all worth while!

Every post gets us all one step closer to eternal happiness 🙂

Great note. FLOOR and CEILING are standard mathematical operators, but MROUND was new to me. Very useful to know.

nice articles

nice articles

I think the admin of this website is really working hard in support of his site, as here every stuff is quality

based material.

Excellent post. I was checking constantly this blog and I am

impressed! Extremely useful info particularly the last part 🙂 I care for such information a lot.

I was looking for this particular information for a

very long time. Thank you and best of luck.

I am genuinely grateful to the owner of this website

who has shared this wonderful post at at this place.

Hi! I’ve been following your web site for a while now and finally got the bravery to go

ahead and give you a shout out from New Caney Texas!

Just wanted to tell you keep up the good job!

This note really useful. Thanks for enlighten me with this new function.