18.2 Tips on Rounding numbers using Excel Formulas

We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more. To know how to use all these formulas and how to round numbers based on any criteria, just read on.

Rounding Formulas in Excel

Before learning the tips, first lets understand various rounding formulas & what they do. Look at this:

Download Rounding formula example workbook

What about .2 tips?

Well, those are for you to fill down. Go ahead and write formulas for both these situations & you have the .2 tips!

18.1 Waiter friendly pricing

Lets say you run a hotel where customers usually tip 15% of bill amount. Now, to make it easy, you want to price your items such that when 15% is added, the total amount becomes a round number like $1.00, $2.00 etc.

For example: If a dish’s current price is $2.50, then 15% tip on it would be $0.37. This makes the total $2.87.If you modify the price to $2.60, with tip the total would be $3.00.

Assuming current price of a dish is in A1, what formula will give you new price?

18.2 Rounding to nearest Monday

Lets say you have some dates in a list and you want them to round to nearest Monday. Assuming you have a date in A1, what formula gives nearest Monday?

Sign-up for our FREE Excel tips newsletter:

Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

Hi Gary!
Not sure why it wasn’t working w/ any other number since the first function that happens is to round up the old price to nearest dollar. I understood the original problem as the owner wanting to set his price so that when customer gives a 15% tip, the total (price + tip) is an even dollar amount. Thus:
price + (15% * price) = even dollar
115% * price = even dollar
price = even dollar / 115%

Your formula works when the nearest dollar up from the original price is the same as the nearest dollar up from the price + tip (as it is for 2.50), but for larger amounts you need to round up from the price + tip not just the original price.

For 18.1, here’s another take: the below formula tests to see if the result falls within a certain tolerance (i.e., difference between the normal answer and rounded answer is less than .002 so that you’re not paying an extra $1 for a very small difference; of course, the tolerance, in this case 0.002, could be changed to a larger tolerance, say 0.005):

Thank you very much for your work, But I am searching for something which looks like the following:
If the decimal value is equal to or less than 0.50 i.e. 21.50 or 21.10 or 21.40, it should round down to 21.00, if it is more than 0.50 i.e. 21.60 or 21.70 or 21.90, it should round up to 22.00. Possible by any means?!?!?!?! People need this in many scenarios. We can achieve this by if condition but checking for any other method.. Good luck.

Roundup and Rounddown use a multiple of ten for the base, where the 2nd argument tells you the precision you want to use. FLOOR and CEILING let you set the multiple. E.g.,
=FLOOR(17,4)
says to go down to the nearest multiple of 4, and thus will result in an answer of 16.

Well, I’ve tested many of these solutions but according to what the problem is actually saying, I don’t see one that is accurate using different amounts.
The problem is that
“You run a hotel where customers usually tip 15% of bill amount. Now, to make it easy, you want to price your items such that when 15% is added, the total amount becomes a round number like $1.00, $2.00etc.For example: If a dish’s current price is $2.50, then 15% tip on it would be $0.37. This makes the total $2.87.
If you modify the price to $2.60, with tip the total would be $3.00.——
Now we already know that it should be $2.61 (to be accurate)
I see that some of the formulas above work for this example but when trying different values (cause the problem did say ‘your items’) I get incorrect values.
The problem ALSO has rounded numbers of $1.00, $2.00 etc. So are we supposed to be rounding up to the nearest dollard? If so, some of the other examples round up (or down) to the nearest 50 cent.
So, in theory, I really haven’t found a correct solution yet. (unless I missed one)
So to keep things consistent,

If I added the current price of a dish in A1 (as the example states) and I add ‘ .15 ‘ or (15%) in A2, I would want to get my result in B2. And I should be able to change the value of A1 to any value I want (My other hotel items) and A2 should show me what i REALLY should be selling my items for in order to ROUND UP to the NEAREST dollard.
And should I decide to change the tip amount to 18% (after the election-LOL) it will not affect any formula I have going.

They also behave differently when dealing with negative numbers
=TRUNC(-2.1)
gives -2
=INT(-2.1)
gives -3, as INT rounds a number DOWN to nearest integer. Small difference, but do be aware that it is there.

That’s all well and good, including all the bells & whistles in the comments. Thanks to all.
As a challenge, can anyone write a VBA procedure that creates a user-defined function to UNround. For example, if cell B1 contains =ROUND(1234.56,0), which produces 1235 in B1, then write a VBA procedure named UNROUND that could be placed in, say, C1 such as =UNROUND(B1), which would produce in C1 the original, unrounded 1234.56.
Similarly, if A1 contains 1234.56 and B1 contains =ROUND(A1,0), again producing 1235 in B1, the UNROUND formula in C1 would produce 1234.56.
It might be impossible to write a VBA procedure that would catch every type of situation (for instance =ROUND(A1*2) in B1). I’m mainly interested in unrounding whole dollars back to their original dollars.cents values. That’s a finite situation and hopefully shouldn’t be too difficult.

Thanks guitarthrower, but unfortunately I get a VBA compile error relating to both of the Application.WorksheetFunction.Find statements. I don’t see why; they seem OK to me.

Assuming we can get that fixed, I still don’t think this function, which peels off both sides of the formula string, would work in my second scenario, where I said, “Similarly, if A1 contains 1234.56 and B1 contains =ROUND(A1,0), again producing 1235 in B1, the UNROUND formula in C1 would produce 1234.56.” (Sorry if my final comment about unrounding whole dollars back to their original dollars.cents values threw you off on this aspect.)

For 18.1 I have the following:
=ROUND((ROUND(A1*1.15,-FLOOR(LOG10(A1),1))/1.15),1-FLOOR(LOG10(A1),1))
Which gives 2.50 -> 2.60; 25 -> 26.00; 250 -> 260.00 etc. and 748 -> 780.00. It tries to scale the number using Logarithms so rounding is at an appropriate order of magnitude.
For 18.2 I have this (which looks a bit messy):
=A1+(7*(SIGN(MROUND(WEEKDAY(A1,3),7)))-WEEKDAY(A1,3))
I wanted to achieve the equivalent of if Fri/Sat/Sun then round up else round down without using an IF Function, Although I’m sue that there are better ways of doing that. It’s a great blog many thanks.

Can you figure out, how to show the rounding off amount in a separate column with the help of formula, Like in A4 the amount value is 342.85 then how can we show the round off amount 0.15 in other cell.

I have a query about rounding numbers. I have 8 columns of numbers where the numbers have 4 to 8 decimal places in random order. Now all I want to know is that is there any trick in Excel 2007 to round it to 2 decimal places. I tried to use the decrease decimal button but it is just hiding the decimal places. Please give me a solution.

I have a query about rounding numbers. I have 8 columns of numbers where the numbers have 4 to 8 decimal places in random order. Now all I want to know is that is there any trick in Excel 2007 to round it to 2 decimal places without using any Formula or Function. I tried to use the decrease decimal button but it is just hiding the decimal places. Please give me a solution.

Hello – what do you do if you want to KEEP both decimal digits (as they are showing a rounded down price) – the formula I am using on an invoice template works the calculation but shows 2587.5 rather than 2587.50.