18.2 Tips on Rounding numbers using Excel Formulas

Posted on September 28th, 2012 in Excel Howtos , Learn Excel - 71 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

=ROUNDUP(A1,-1)

6. Round to nearest thousand

Example: 312789123 to 312789000

=ROUND(A1,-3)

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

=MROUND(A1,5)

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

=ODD(A1)

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.

Your email address is safe with us. Our policies

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

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

  1. Rahim Zulfiqar Ali says:

    18.1 Waiter friendly pricing
    =ROUND(A1*15/100+A1,2)

  2. Rahim Zulfiqar Ali says:

    For 18.1 This can be also applied
    =ROUNDUP(A1*15/100+A1,1)

  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.
    http://spreadsheetpage.com/index.php/tip/rounding_to_n_significant_digits/:
       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.
    =DOLLAR(ROUNDUP(A1,0)/1.15) 

  5. Luke M says:

    For nearest Monday:
    =MROUND(A1-2,7)+2

  6. Gary says:

    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.

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

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

  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:

    =ROUND(A4*(1+$B$1),0)/(1+$B$1)

    I figured retailers only increased prices…

  10. Gary says:

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

    =7*ROUND((A1-2)/7,0)+2

  11. Robert says:

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

  12. Ramki says:

    Hi!!
    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:

    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
     

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

    =IF(A1*1.15-INT(A1*1.15)<=0.002,MROUND(A1*1.15,1),ROUND(A1*1.15,0))
      

  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

    B2=ROUNDUP((ROUND(ROUNDUP(1.15*B1,1),0))/1.15,2)

  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.

    =FLOOR(A1+0.4999999999,1)  

  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.
    =ROUNDUP(A1,-3) 

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

  23. Sandeep says:

    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
     
     

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

  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?

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

  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:

      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 

  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:

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

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

  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:

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

  37. Jigar Gohil says:

    =ROUND(M62,0)/1.15

  38. Jigar Gohil says:

    Cell A1 is referred as date:-
    =A1+IF(WEEKDAY(A1)>=6,9-WEEKDAY(A1),2-WEEKDAY(A1))

  39. Jigar Gohil says:

    =ROUND(A1*1.15,0)/1.15

  40. Rakesh says:

    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.

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

    Help?

  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:

      @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

  46. Mehmet Gunal OLCER says:

    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

  47. jastin says:

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

Leave a Reply