18.2 Tips on Rounding numbers using Excel Formulas

Posted on September 28th, 2012 in Excel Howtos , Learn Excel - 99 comments

School Cartoon #6408 by Andertoons

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

=ROUND(A1,2) Rounds value in A1 by 2 decimal points

2. Round up to 2 decimal points

Example: 1.2649 to 1.27

=ROUNDUP(A1,2) Roundsup value in A1 by 2 decimal points (ie away from zero)

3. Round to nearest integer

Example: 1.2649 to 1

=ROUND(A1,0) By using 0, we can round the value to nearest integer

4. Round to nearest multiple of 10

Example: 544.234 to 540

=ROUND(A1,-1) By using negative numbers, we can round the value to nearest multiple of 10, 100…

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

=ROUND(A1/1000000,1) First we divide the number by million (1,000,000) and then round this to 1 decimal point.

8. Round to nearest multiple of 2

Example: 43 to 44

=MROUND(A1,2) Just like round formula, but for any multiple. So MROUND(A1,2) takes value in A1 and rounds it to nearest multiple of 2

9. Round to nearest multiple of 5

Example: 93 to 95


10. Round down to hundred

Example: 301 to 300

=FLOOR(A1,100) To round down, we can use FLOOR formula.

11. Get only the integer portion of a number

Example: -23.34 to -23

=TRUNC(A1,0) To extract only the integer portion of number, use TRUNC formula. Note: INT formula gives same result for positive numbers.

12. Round a number to 2 decimals and convert to text

Example: 312789.26921 to 312,789.27

=FIXED(A1,2,FALSE) In one shot, round and convert the number to text. Useful when you want text output.

13. Get next even number

Example: 42.1 to 44

=EVEN(A1) Gets you next EVEN number (away from zero)

14. Get next odd number

Example: 44.93 to 45


15. Round to nearest quarter ($0.25)

Example: 19.14 to 19.25

=MROUND(A1,0.25) MROUND can be used with fractions too.

16. Round to next 9 (ie 19,29,39 etc.)

Example: 23 to 29

=ROUNDUP(A1,-1)-1 To do this, we just roundup the number to next 10 and then subtract 1 from it.

17. Round up to next 1000

Example: 124567 to 125000

=CEILING(A1,1000) Just like FLOOR, but takes you to next value.

18. Get only decimal portion of a number

Example: 23.345 to 0.345

=A1-TRUNC(A1) To get only decimal portion, subtract TRUNC value from original

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.

Click here to comment.

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

99 Responses to “18.2 Tips on Rounding numbers using Excel Formulas”

  1. Rahim Zulfiqar Ali says:

    18.1 Waiter friendly pricing

  2. Rahim Zulfiqar Ali says:

    For 18.1 This can be also applied

  3. Unai says:

    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.
       ROUND(A1, NumberOfDigits - 1 - INT(LOG10(ABS(A1))))    


  4. Luke M says:

    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.

  5. Luke M says:

    For nearest Monday:

  6. Gary says:

    Assuming the tip amount is in $B$1 and the old price is in A4


    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:


     Another argument against 1-based indices.

    • Gary says:

      Oops, old price is in A5 not A4!

    • Luke M says:

      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% 

      • Gary says:

        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. 

  7. Travis says:

    18.1 Waiter friendly pricing:


     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:
    where 41176 = last Monday, Sept. 24, 2012.  Works for every day after or on last Monday.


    Works for any day. 

  8. Chandoo says:

    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.

  9. Gary says:

    Oh, if we can go down then it's a simple change from roundup to round:


    I figured retailers only increased prices...

  10. Gary says:

    Since Saturdays are evenly divisible by 7, we could also do it this way:


  11. Robert says:

    The ceiling (and floor) functions can also round up (and down) to the nearest multiple of 5.

  12. Ramki says:

    Is there any shortcut to use the above functions?

  13. TKS says:

    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)"

  14. shrivallabha says:

    Rounds up a number to nearest multiple of 1,10,100…

    Rounds down a number to nearest multiple of 1,10,100…

    These functions can handle decimals as well!
    =CEILING(1.01,0.1) will give 1.1

  15. Mel says:

    I got it! 
    I don't have to share it because it's already been published, but I was excited to get it! 

  16. Russ says:

    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):


  17. [...] 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 [...]

  18. suresh says:

    First Post Ever

    B2 is the modified price
    B1 is the original price


  19. Ravi says:

    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.

  20. Gary says:

    Ravi - something like this would work, so long as you have more digits of precision in the .4999999 than you do in the input.


  21. venkat says:

    Thanks a lot for sending me the mails with problems and solutions. great work

  22. guitarthrower says:

    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.

    Are there other things that can be done with ceiling that can't be done with roundup?

    • Luke M says:

      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., 
      says to go down to the nearest multiple of 4, and thus will result in an answer of 16. 

  23. Sandeep says:

    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

  24. Mike says:

    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?

  25. Erin says:

    I use INT instead of TRUNC to extract the integer portion. Simpler and requires fewer arguments.

    • Luke M says:

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

  26. =IF(WEEKDAY(A1,2)>4,A1+8-WEEKDAY(A1,2),A1-WEEKDAY(A1,2)+1)

  27. Assuming you have a date in A1, what formula gives nearest Monday?


  28. 5antiago says:

    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

  29. Roy says:

    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.

    • guitarthrower says:


      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 

  30. Roy says:

    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.)

  31. 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.

    • Hui says:

      When you say round you need to specify round to How Many Decimal places?
      Should 0.501 be rounded to 0.51 or 1.0?
      Should 0.499 be rounded to 0.5, 0.4 or 0.0?

  32. raj says:

    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.

  33. richard says:

    For 18.1 I have the following:
    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):
    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.

  34. Mr. Ron says:

    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.

    • Hui... says:

      @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 ?

  35. Saleem says:

    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

  36. Nadeem says:


    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..

  37. Jigar Gohil says:


  38. Jigar Gohil says:

    Cell A1 is referred as date:-

  39. Jigar Gohil says:


  40. Rakesh says:


    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.

  41. Arup Chakraborty says:

    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

  42. Arup Chakraborty says:

    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

  43. scott b says:

    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..


  44. Virginia says:

    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 ...

  45. Michelle says:

    Is there vba code that could a column with 20,000 rows of decimals
    That is to say round the decimal to the integer.

    • Hui... says:


      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)
      End Sub

  46. Mehmet Gunal OLCER says:

    Answers to Question Monday.
    It is assumed that a date is input to cell A1.

    Solution 1.

    Solution 2 (A Short Formula).

  47. jastin says:

    how to convert...EX: 23.5 or 23.2 always in 1peso? Meaning: 23.5 = 24, 23.2 = 24

  48. KADRI MUNTAZIR says:

    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

  49. Balu says:


    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.

  50. Anita says:

    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?

    • Ay says:


      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)))

  51. gary says:

    You had all the pieces of the puzzle:


    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!

  52. […] this work, I came across a nice table that shows various Excel rounding operations in Figure 7.  Click here to go to the original […]

  53. Mohan Lal Singhal says:

    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.

  54. Goldy says:

    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

  55. Govind Pandey says:

    Suggest formula to round the Percentage. For E.g.

    12.5% to be round to 13%.

  56. nmgolfer2009 says:

    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.

  57. John Kenedy says:

    Excellent tips. It saves my time. Thanks a lot man. (y)

  58. Karla says:

    Thanks so much!!! Huge help. 🙂

  59. Mit says:

    help :

    need a formula for round , roundoff or roundup , if



    i need ans as


    roundoff must be to the nearest of 3rd decimal

    help plzzzz

  60. usama819 says:

    round logic is move around 0.5 up and dawn I want it move around 0.3 up and dawn >> may you help me ??

    • Hui... says:

      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)

  61. kaushik says:


  62. Tito says:

    I want to add Rounded Numbers, But every time I try it it takes the original numbers. In a Regular Formula (a1+b1+c1)

  63. Daron Haymon says:

    I was examining some of your content on this website and I think this web site is very informative ! Keep putting up.

  64. Nalamo says:




    Thanks a lot !

  66. Gary says:

    It is amazing that this 4-year old post still gets comments - and not just spam comments.

  67. filafisa33 says:

    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 ?

  68. Devanand Borate says:

    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

  69. Darrell says:


Leave a Reply