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:
Formula  What it does? 
ROUND  Rounds a number to specified decimal points (or multiples of 10) 
ROUNDUP  Rounds up a number 
ROUNDDOWN  Rounds down a number 
MROUND  Rounds to nearest multiple of specified number 
INT  Rounds down to nearest integer 
TRUNC  Gives you only integer portion 
CEILING  Rounds up a number to nearest multiple of 1,10,100… 
FLOOR  Rounds down a number to nearest multiple of 1,10,100… 
EVEN  Gives next even number 
ODD  Gives next odd number 
FIXED  Rounds and converts to text format (with commas if you want) 
18 Rounding Formula Tips
1. Round to 2 decimal points
Example: 1.2649 to 1.26
2. Round up to 2 decimal points
Example: 1.2649 to 1.27
3. Round to nearest integer
Example: 1.2649 to 1
4. Round to nearest multiple of 10
Example: 544.234 to 540
5. Round up to nearest multiple of 10
Example: 544.234 to 550
6. Round to nearest thousand
Example: 312789123 to 312789000
7. Round to nearest million with one decimal point
Example: 312789123 to 312.8
8. Round to nearest multiple of 2
Example: 43 to 44
9. Round to nearest multiple of 5
Example: 93 to 95
10. Round down to hundred
Example: 301 to 300
11. Get only the integer portion of a number
Example: 23.34 to 23
12. Round a number to 2 decimals and convert to text
Example: 312789.26921 to 312,789.27
13. Get next even number
Example: 42.1 to 44
14. Get next odd number
Example: 44.93 to 45
15. Round to nearest quarter ($0.25)
Example: 19.14 to 19.25
16. Round to next 9 (ie 19,29,39 etc.)
Example: 23 to 29
17. Round up to next 1000
Example: 124567 to 125000
18. Get only decimal portion of a number
Example: 23.345 to 0.345
Download Rounding formula example workbook
Click here to download example workbook & understand these formulas better.
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?
Go ahead and figure them out. Post your answers using comments.
 
 

Leave a Reply
Introducing Excel School + Excel Hero Academy Bundle  Using pivot tables to find out non performing customers 
71 Responses to “18.2 Tips on Rounding numbers using Excel Formulas”
18.1 Waiter friendly pricing
=ROUND(A1*15/100+A1,2)
I’m trying to create a formula and then round the answer to the nearest 5. Can someone explain how to do that?
If your data is in A1, the formula you need is
=MROUND(A1,5)
@J
Also have a read of:
http://chandoo.org/wp/2012/09/28/roundnumbersexcelformulas/
For 18.1 This can be also applied
=ROUNDUP(A1*15/100+A1,1)
These don’t seem to work…first formula gives $2.88, second formula gives $2.90
Is 18.1 the same as Rounding to n significant digits? If so, http://peltiertech.com/Excel/NumberFormats.html:
ROUND(A1, NumberOfDigits – INT(LOG10(A1))),
better if combined with * #0.00″ “_;* #0.00″ “_ or similar as Number Format.
http://spreadsheetpage.com/index.php/tip/rounding_to_n_significant_digits/:
ROUND(A1, NumberOfDigits – 1 – INT(LOG10(ABS(A1))))
Here’s 1 way to get the new price. Output is in text format, but it’s a tradeoff to get the 2 decimals + dollar sign.
=DOLLAR(ROUNDUP(A1,0)/1.15)
For nearest Monday:
=MROUND(A12,7)+2
Assuming the tip amount is in $B$1 and the old price is in A4
=ROUNDUP(A5*(1+$B$1),0)/(1+$B$1)
If you need to round this to money in the formula rather than in the format, then wrap dollar(2) around this. Luke’s formula didn’t work for me for anything other than $2.50.
For nearest Monday, I used mod rather than rounding:
=A1+7MOD(WEEKDAY(A1)+1,7)4
Another argument against 1based indices.
Oops, old price is in A5 not A4!
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.
18.1 Waiter friendly pricing:
=ROUND(ROUNDUP(A1*1.15,0)/1.15,2)
I got a price of $2.61 for your example. $2.60 x 1.15 = $2.99, but $2.61 x 1.15 = $3.00
18.1 Rounding to nearest Monday:
=MROUND(A141176,7)+41176
where 41176 = last Monday, Sept. 24, 2012. Works for every day after or on last Monday.
=MROUND(ABS(A141176),7)*IF(A141176>=0,1,1)+41176
Works for any day.
You are right. The price should be $2.61. I used 2.6 to make it easy for anyone calculating the 15% with a calculator.
Note on 18.1 – Waiter friendly price:
Keep in mind that sometimes, we may have to reduce the price. For example a price like $2.01 can be reduced to $1.74 rather than $2.61.
Oh, if we can go down then it’s a simple change from roundup to round:
=ROUND(A4*(1+$B$1),0)/(1+$B$1)
I figured retailers only increased prices…
Since Saturdays are evenly divisible by 7, we could also do it this way:
=7*ROUND((A12)/7,0)+2
The ceiling (and floor) functions can also round up (and down) to the nearest multiple of 5.
Hi!!
Is there any shortcut to use the above functions?
Your formula for getting only the Integer portion of a number is wrong. The integer portion 23.34 is not 23.3, it’s 23. Your formula should be “=TRUNC(C15,0)”
Thanks TKS for pointing out the typo. I have fixed it now.
CEILING
Rounds up a number to nearest multiple of 1,10,100…
FLOOR
Rounds down a number to nearest multiple of 1,10,100…
These functions can handle decimals as well!
e.g.
=CEILING(1.01,0.1) will give 1.1
I got it!
I don’t have to share it because it’s already been published, but I was excited to get it!
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):
=IF(A1*1.15INT(A1*1.15)<=0.002,MROUND(A1*1.15,1),ROUND(A1*1.15,0))
[...] round, roundup, trunc. Home: Chandoo.org Main Page ? Doubt: … … Read more: 18.2 Tips on Rounding numbers using Excel Formulas – Chandoo ← BBO ALMANACH • Thema Anzeigen – Buy Microsoft Office Home [...]
First Post Ever
B2 is the modified price
B1 is the original price
B2=ROUNDUP((ROUND(ROUNDUP(1.15*B1,1),0))/1.15,2)
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.
=ROUND(A1,0)
TRY THIS ONE OUT!!!
=IF((A1TRUNC(A1))<=0.25,MROUND(A1,0.25),IF((A1TRUNC(A1))<=0.5,MROUND(A1,0.5),IF((A1TRUNC(A1))<=0.62,A1(A1TRUNC(A1)),IF((A1TRUNC(A1))<=0.75,(0.75((A1TRUNC(A1)))+A1),ROUNDUP(A1,0)))))
i think this is what you want Mr.
=IF((A1TRUNC(A1))<0.5, A1(A1TRUNC(A1)),ROUNDUP(A1,0))
Ravi – something like this would work, so long as you have more digits of precision in the .4999999 than you do in the input.
=FLOOR(A1+0.4999999999,1)
It’s working just fine.. Gary…!
Thank you very much.
Thanks a lot for sending me the mails with problems and solutions. great work
I’ve always used round up and round down instead of the ceiling and floor approach.
for example for number 17 this will yield the same result as your solution.
=ROUNDUP(A1,3)
Are there other things that can be done with ceiling that can’t be done with roundup?
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.
Hi
Please help me to make it
I need to round off 0.8 to 1 and below 0.8 to 0
For example
70.6 to 70 and 70.8 to 71
@Sandeep
=ROUNDDOWN(A1+0.2, 0)
or
=Int(A1+0.2)
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.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.
——
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 electionLOL) it will not affect any formula I have going.
This is what i would like to see.
Any takers?
I use INT instead of TRUNC to extract the integer portion. Simpler and requires fewer arguments.
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.
=IF(WEEKDAY(A1,2)>4,A1+8WEEKDAY(A1,2),A1WEEKDAY(A1,2)+1)
Assuming you have a date in A1, what formula gives nearest Monday?
=IF(WEEKDAY(A1,2)>4,A1+8WEEKDAY(A1,2),A1WEEKDAY(A1,2)+1)
Don’t know if it has already been mentioned but sometimes I need to strip out the time from a date, leaving just the date figure. =TRUNC(date&time) = just date
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 userdefined 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.
Roy
This should do it for you. code goes in a new module
Function UNROUND(cell As Range) As Variant
Dim ltext As String
Dim rtext As String
Dim lfind As Integer
Dim rfind As Integer
rfind = Application.WorksheetFunction.Find(“(“, cell.Formula) – 2
rtext = Right(cell.Formula, rfind)
lfind = Application.WorksheetFunction.Find(“,”, rtext) – 1
ltext = Left(rtext, lfind)
UNROUND = Range(ltext).Value
End Function
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.)
How to round average IF the number is N.50 or less than 0.50 then round down
and IF the number is N.501 or biger then round up.
@Vicktor
When you say round you need to specify round to How Many Decimal places?
So:
Should 0.501 be rounded to 0.51 or 1.0?
Should 0.499 be rounded to 0.5, 0.4 or 0.0?
we write the formula for .2 tip as =ROUND((ROUND(A1*1.15,0))/1.15,2).This formula will give the new price rounded off to two decimals.
For 18.1 I have the following:
=ROUND((ROUND(A1*1.15,FLOOR(LOG10(A1),1))/1.15),1FLOOR(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.
Dear Guys i have a question to ask, i have 1 carton = 24 pieces i want to get exact as Cartons 2.18 + 0.21 = 3.15 i want to done this job thru excel how can i do it kindly help.
@Mr. Ron
Can you explain what 1 Carton = 24 pieces and 2.18 + 0.21 = 3.15 have to do with each other?
Does 2.18 mean 2 cartons with 18 pieces or 1 carton with 24 and 1 with 18 etc ?
I can’t get it to add up in any combination ?
Dear Bro,
I want to get result in same cell of Round which cell showing result of sumif formula
For Example: A B C
5.2 3.27 =a1+b1=8.47
I want to Round in C1 Cell, pls guide
Hi,
I am using excel 2010 and when i try to add any number using decimal points for eg 2.1+3.2 it gives me result as Zero “0″.
but if i add 2+3=5 it gives me correct result but not on decimal points at all..
@Nadeem
Can you post a sample of your file or a full formula ?
=ROUND(M62,0)/1.15
Cell A1 is referred as date:
=A1+IF(WEEKDAY(A1)>=6,9WEEKDAY(A1),2WEEKDAY(A1))
=ROUND(A1*1.15,0)/1.15
Hello,
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.
@Rakesh
=Round(A4,0)A4
Dear Chandoo,
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.
Thanks and Regards
Arup Chakraborty
Dear Chandoo,
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.
Thanks and Regards
Arup Chakraborty
Looking to have a number round up if it is 20.2 and above to next whole number, if below 20.2 down to whole number.
So if the number is 20.1 then it is 20 and if it is 20.2 then it is 21..
Help?
Assuming the number is in A1, write this formula
=INT(A1 + (MOD(A1,1)>=0.2))
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.
Would appreciate any help …
Is there vba code that could a column with 20,000 rows of decimals
That is to say round the decimal to the integer.
@Michelle
I’ll assume your data is in Column A
You could do that without VBA by adding a column, Column B
in B1 =Int(A1)
Copy down
Copy Column B
Paste as Values on top of itself
If you really must use VBA
Sub Strip_Decimals()
Dim c As Range
For Each c In Range(“A1″, Range(“A65536″).End(xlUp))
c.Value = Int(c.Value)
Next
End Sub
Answers to Question Monday.
It is assumed that a date is input to cell A1.
Solution 1.
=IF(MOD(A1,7)=3,1,IF(MOD(A1,7)=4,2,IF(MOD(A1,7)=5,3,IF(MOD(A1,7)=6,3,IF(MOD(A1,7)=0,2,IF(MOD(A1,7)=1,1,0))))))+A1
Solution 2 (A Short Formula).
=LOOKUP(MOD(A1,7),{0,1,2,3,4,5,6},{2,1,0,1,2,3,3})+A1
how to convert…EX: 23.5 or 23.2 always in 1peso? Meaning: 23.5 = 24, 23.2 = 24
@Jastin
=ROUNDUP(A1,0)