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.
107 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/round-numbers-excel-formulas/
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 trade-off to get the 2 decimals + dollar sign.
=DOLLAR(ROUNDUP(A1,0)/1.15)
For nearest Monday:
=MROUND(A1-2,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+7-MOD(WEEKDAY(A1)+1,7)-4
Another argument against 1-based 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(A1-41176,7)+41176
where 41176 = last Monday, Sept. 24, 2012. Works for every day after or on last Monday.
=MROUND(ABS(A1-41176),7)*IF(A1-41176>=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((A1-2)/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.15-INT(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((A1-TRUNC(A1))<=0.25,MROUND(A1,0.25),IF((A1-TRUNC(A1))<=0.5,MROUND(A1,0.5),IF((A1-TRUNC(A1))<=0.62,A1-(A1-TRUNC(A1)),IF((A1-TRUNC(A1))<=0.75,(0.75-((A1-TRUNC(A1)))+A1),ROUNDUP(A1,0)))))
i think this is what you want Mr.
=IF((A1-TRUNC(A1))<0.5, A1-(A1-TRUNC(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 election-LOL) 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+8-WEEKDAY(A1,2),A1-WEEKDAY(A1,2)+1)
Assuming you have a date in A1, what formula gives nearest Monday?
=IF(WEEKDAY(A1,2)>4,A1+8-WEEKDAY(A1,2),A1-WEEKDAY(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 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.
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),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.
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,9-WEEKDAY(A1),2-WEEKDAY(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)
hello Sir,
I want to convert round value for
example:- 3.83X4X2=30.64/6=5.10, but i want to convert value for (6)
please help me
@Kadri
=ROUNDUP(30.64/6,0)
=6
HI
rounding based no.of digits. suppose there are numbers like 948.25, 12.45. if it is a two integer number it is to be rounded to up to one decimal, if the number is three integers, then the number is to be rounded nearest ten rupees, and so on. pls let me know the way of doing.
@Balu
Your words don't match your example
should
948.25 be rounded to 948.3 or 950
+
12.45 be rounded to 12.4 or 12
?
Would like to combine an excel formula that 1st if the number is less than 2000 then it returns 2000 IF(A1<1999, 2000) but if the number is greater then 2000 it rounds to the nearest 500 (Ceiling, A1, 500).
How can I do this?
=IF(A1<2000,2000,(CEILING(A1,500)))
The outer statement is an: If A1 is less than 2000, return 2000.
So: =IF(A1<2000,2000...
The nested statement now is that if A1<2000 is false, then return a rounded ceiling of 500, so use this as the false parameter.
Full statement: =IF(A1<2000,2000,(CEILING(A1,500)))
You had all the pieces of the puzzle:
if(A1<2000,2000,mround(A1,500))
Note: use <2000 instead of <1999 - they invented less than for a reason. It doesn't make a difference in this case (1999 will round up to 2000) but it's a pet peeve of mine. Ceiling truncates but MRound rounds to the nearest 500, as noted above. Enjoy!
[…] this work, I came across a nice table that shows various Excel rounding operations in Figure 7. Click here to go to the original […]
for salary increment I want conditional formula in Excel that, if value is =>510.99 up to 510.00 is to be round in 510, and if Value is =<511.00 is to be round in 520 ie. in next multiple of 10. Means value in multiple of 10 is greater up to 0.99 should be 0 and value come 511 or above upto 520 should be 520 after round. What conditional or other formula should insert.
Hi. I need help with rounding numbers. I am making a salary in which the formula i am using is "=(C1/240)*B1" in which C1 = 29875(salary) B1= 24(hours)
In excel i am getting 2987.50 but in calculator it is showing 2987.49999. So i am supposed to rounddown this amount to 2987.00 but since in excel it is showing .50 = it is rounding up to 2988.00
Suggest formula to round the Percentage. For E.g.
12.5% to be round to 13%.
You can use this formula.
=Round(value, 2). This rounds the percentage to nearest integer.
To roundup, use =ROUNDUP(value, 2).
Is there a way to format excel to round a 5 to make the digit in front of 5 an even number. ex 10.04445 rounds to 10.0444 or 10.04455 to 10.0446.
Excellent tips. It saves my time. Thanks a lot man. (y)
Thanks so much!!! Huge help. 🙂
help :
need a formula for round , roundoff or roundup , if
eg:
99.763
99.722
99.82
99.716
i need ans as
99.765
99.720
99.82
99.715
roundoff must be to the nearest of 3rd decimal
help plzzzz
@Mit
=ROUND(A1,3)
round logic is move around 0.5 up and dawn I want it move around 0.3 up and dawn >> may you help me ??
@Usama819
If you want to round by 0.3, which sounds quite odd, I would divide the numbers by 0.3, then round them then multiply by 0.3
eg: =0.3*ROUND(A1/0.3,0)
=ROUNDUP(A1*1.15,0)/1.15
I want to add Rounded Numbers, But every time I try it it takes the original numbers. In a Regular Formula (a1+b1+c1)
I was examining some of your content on this website and I think this web site is very informative ! Keep putting up.
18.1
=ROUND(CEILING(A1*(1+0.15),1)/(1.15),1)
18.2
=A1+(9-WEEKDAY(A1))
Thanks a lot !
It is amazing that this 4-year old post still gets comments - and not just spam comments.
I want to make a salary sheet with increment, old salary was $50, $80, $100, S1000, $2000, per month, I want to increase 10% salary for every one, but increment amount will be not less than $10 and not above than $100.
do you help me ?
I want to give increment for employees and increment value is 3% of pay + Grade pay . If 3% is 420.99 then the employee will get Increment of Rs 420 and if it is 421 then He will get Increment Rs 430. How to round a number (a+b)*3% = 420.99 to 420 and If it is 421 to 429.99 to 430
=ROUNDUP(A1*1.15,0)/1.15
Q1 = ROUNDUP(A1*(1+0.15),0)/(1+0.15)
Q2 = A1 - (WEEKDAY(A1,2)-1)
what rounding formula for
10.5 to 20.49 round 20
1 to 10.49 round 10
What rounding formula for
10.5 to 20.49 round 20
1 to 10.49 round 10
Kindly help me to sum (total) in MS WORD in table
Which formula to be instet for totaling amount in MS Word no in MS Excel
@Pradeep
Despite this being a Microsoft Excel blog
In a Word Table Select the cell where you want the formula
Goto the Table Tools, Layout tab
Select the Formula Icon
Select =Sum(Above)
You can apply Excel style Number Formats eg: #,##0.00
You can develop quite complex formulas if you want...
Enjoy
For 18.1
=RoundUp(Sum(Cell Address,Cell Address*15%),2)
For 18.2
=If(Weekday(Date,2)=OR("2","3","4"),Date-Weekday(Date,2),If(Weekday(Date,2)=OR("5","6",7"),Date+Weekday(Date,2),Date))
How do I round down 0.99 to 0.00 and round up 1.0 to the next 10 in a single formula in Excel? For ex. 100.99 should be rounded down to 100 and 101 should be rounded to 110.
What is the logic here?
You are rounding decimal points down but 1s to 10s?
So what happens if the number is 101.99? Should it be 101 or 110?