Comments on: How to round numbers in Excel: ROUND, ROUNDUP, ROUNDDOWN functions

The tutorial explains the uses of ROUND, ROUNDUP, ROUNDDOWN, FLOOR, CEILING, MROUND and other Excel rounding functions and provides formula examples to round decimal numbers to integers or to a certain number of decimal places, extract a fractional part, round to nearest 5, 10 or 100, and more. Continue reading →

Comments page 4. Total comments: 373

  1. Excellent article, which I have bookmarked for future use!

    When using the ROUND or MROUND functions to round a column of data with occasional non-numerical values (e.g. X instead of a number), how can I get the function to ignore those non-numerical values instead of it screaming #VALUE! at me please?

  2. I’m trying to round figures for my retail prices in one excel column
    For example,

    Anything with a price between 170 and 175 rounds to 175
    Anything with a price between 176 and 179 I want to round to 185

    Any help I can get with this is much appreciated
    Regards,

    1. Hello!
      The formula below will do the trick for you:

      =IF(MOD(A1,10)>5,FLOOR(A1,5)+10, IF(MOD(A1,10)=5,A1,FLOOR(A1,5)+5))

      This should solve your task.

      1. many thanks , it works !

  3. Hello I would like to round to nearest .09 that way every price ends with 9 cents I don't think its possible. Let's say a price is 6.71 I would like it to round down to 6.69. Say a price is 6.76 it rounds up to 6.79. Is this possible? I would like this instead because the way I raise my prices it is done by a certain percentage and if I round everything up or down I will stray away from that said percentage.

    Thank you!

    1. Hello!
      Please try the following formula:

      =A1+(--(CHOOSE(MATCH(MIN(ROUNDUP(ABS(--RIGHT(A1,1)-{-0.001,9}),0)), ROUNDUP(ABS(--RIGHT(A1,1)-{-0.001,9}),0),0),"-","")&"0.0"& MIN(ROUNDUP(ABS(--RIGHT(A1,1)-{-0.001,9}),0))))

      Hope this is what you need.

  4. I want to set a limit whereas 0.60 is the threshold if the decimal reaches 0.60 it will automatically count as 1 and reset into decimal. The decimal represent the minutes and the whole number represent the hours. I'm presenting my data as numbers instead of time value. Hope anyone can help

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice.
      I’ll try to guess and offer you the following formula:

      =IF((C1-INT(C1))>=0.6,1+INT(C1),C1)

  5. Hi, I'm trying to reduce a number by a % then MROUND it to the nearest 5.
    I'm having trouble figuring out the formula, would you have any pointers?

    =B5*(1-E5)&MROUND(F5,5)

      1. Thank you so much for the clarification and assistance.

  6. Hi!

    I need my values to round to the nearest 5, but I need 2.5 to round DOWN. So any values 2.5 and below would round down and any values 2.6 and above would round up. Problem is that the number before the decimal may change. For instance it may be 17.5 which would need to round down to 15 and 17.6 would need to round up to 20. 23 would round up to 25 but 22.5 would round down to 20.

    Thanks!

  7. Works perfectly!

    Thanks so much.

  8. Hi!

    I need to do a multi-step process.

    First, I need to truncate any numbers with over 2 digits after to decimal point down to two digits after the decimal.

    Second, the second digit after the decimal needs to be rounded up to either a 5 or a 9. The range could to round is this: 0-5 rounds up to 5, and 6-9 rounds up to 9.

    Third, the result must be formatted as currency.

    Examples with steps illustrated:

    - 3.755556 > 3.75 > 3.75 > $3.75
    - 4.488889 > 4.48 > 4.49 > $4.49
    - 14.11111 > 14.11 > 14.15 > $14.15
    - 3.2 > 3.2 or 3.20 > 3.25 > $3.25

    Is there a formula that can process all these steps together? I.e. input 3.755556 and output $3.75?

    Happy for any help.

    1. Hello!
      Number rounding functions cannot solve your problem. You need to use digit substitution.

      =--(LEFT(TEXT(TRUNC(A1,2),"#.00"), LEN(TEXT(TRUNC(A1,2),"#.00"))-1) & (IF(--(RIGHT(TEXT(TRUNC(A1,2),"#.00"),1))<6,5,9)))

      Hope this is what you need.

  9. Hi, I'm doing a billing report and i need a formula that will round to the nearest $5000, and if the salary is $100,001.00, round down to $100,000.00 and if $100,001.01 or above, round to $105,000.00

      1. Thanks you so much Alexander!... You saved me so much time, this worked perfectly

    2. With a minimum rounded amount of $30,000.00

  10. I am working as an engineer
    I need to validate certain complex rules after implementing certain values or data. That shouldn't violate the rules. If it is violated the we will get a error text.
    Please help

  11. I am working with a downloaded csv file. The actual reference is this, 291827871268478976 but when i download excel turns it to this 2.91828E+17. I tried formatting and have used several methods but excel always seems to round up the last three digits to something like this, 291827871268478000. Please how can this can be resolved. Previously, I usually worked with python to convert the file from csv to excel and the reference column always changed to text and remained the same but somehow python doesn't seem to be working this time around. Thank you

    1. Hello!
      In Excel, the maximum precision for representing a number is 15 digits. A cell displays up to 11 characters. If you need to store long numbers, I recommend saving them as text.

  12. Dear Svetlana:

    As with all your online works, I cannot but express my sincere thanks to you for your easy-to-follow, excellent work. I am always happy to look at your webpages; indeed, I am most appreciative of the food for thought they give each and every one of us!

    Thank you and keep the excellent work up!

    Elias

    1. Dear Elias,

      Thank you so very much for your inspiring and motivating comments! I really appreciate it.

  13. Hello,

    I'd like to round up time difference (formula) between 2 times. Eg.:

    A1 -> 08:02 (start time in format hh:mm)
    B1 -> 09:58 (end time)
    C1 -> 01:56 (calculated difference between B1-A1)

    Question is how to round up the result of formula in C1.

    Thank you!

      1. Hi,

        - in the cell A1 is the beginning of an event @ 08:02 (hh:mm)
        - in the cell B1 is the end event of an event @ 09:58 (hh:mm)
        - in the cell C1 is the formula (=B1-A1) for the duration of this event. The calculated number is 01:56 (hh:mm).

        How to round up the value of C1 01:56 (hh:mm) to 02:00 (hh:mm)?

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.
          You can learn more about rounding time in Excel in this article on our blog.

          =MROUND(B1-A1,"1:00")

          There are different ways of rounding the time in the article.

  14. How can convert greater than.5 as 1 & less than or equal to.5 is accurate no. For example 2.51 = 3, 2.50 =2

  15. Dear Sir,

    Pls. solve this problem

    Total 30.00% 25.00% 25.00% 7.50% 7.50% 5.00% 100.00%
    505.00 151.50 126.50 126.50 38.00 38.00 25.50 506.00
    510.00 153.00 127.50 127.50 38.50 38.50 25.50 510.50

    I need to total should come to 505 and 510 without deviation in any percentage

    1. Hello!
      The actual values in your cells contain more than 2 decimal places. Therefore, a rounding error appears. I recommend rounding the values in each cell to 2 decimal places using the ROUND function:
      ROUND(A2,2)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  16. Hello!
    Looking to calculate a sales price that is rounded to the nearest xx,x99
    Example 17899 / .845 = 21,182.25. Need that to have end result show as 21,199.

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use MROUND function

      =MROUND(A1,100)-1

      Hope you’ll find this information helpful.

  17. Hello! Is it possible to round past the decimal place? For example, turn 1,200,000 into 1.2?

    1. Hello!
      This is not rounding as the number does not change. Just divide by 1000000. Or use a custom number format

      #,##0.0,,

      or

      #,##0.0,," M"

      I hope I answered your question. If something is still unclear, please feel free to ask.

  18. If i have a quantity of 3.06 in B11 of excel and i have a unit cost of 536 in d11 and if i rounded up the 3.06 to 4 is there an equation that would then change the unit cost to the quantity of 4 automatically?

  19. Hello,
    I have a question noone asked. How to round numbers to specific numbers that I have in a list?

    My list is:
    ...
    0.09
    0.1
    0.12
    0.16
    0.2
    0.25

    Then I need to round like this:
    0.23 => 0.25
    0.094 => 0.9

    1. I finally found answer. Here it is:

      =INDEX(A2:A20;MATCH(MIN(ABS(A2:A20-A1));ABS(A2:A20-A1);0))

      Where:
      A1 is number that needs to be rounded
      A2:A20 is a range of numbers that I can round to

  20. Hello,
    I have a question noone asked. How to round numbers to specific numbers that I have in a list?

    My list is:
    ...
    0.09
    0.1
    0.12
    0.16
    0.2
    0.25

    Then I need to round like this:
    0.23 => 0.25
    0.094 => 0.9

  21. Hey, I have got these conditions rounding with weights that we have,

    .1 - .5 (should be rounded to .5)
    .6 - 1.09 (Should be rounded to 1)
    1.1- 1.59 (Should be rounded to 1.5)
    1.6 - 2.09 (Should be rounded to 2)

    I am familiar with the nested if but what about rounding these numbers like mentioned above.

  22. Can you help - I need the following time provisions on a summary of time work in minutes up to 100
    i.e 11,070.92 hours worked

    Less than 15 minutes - round to zero hours

    15 minutes to 45 minutes (inclusive) - round to 1/2 hour

    Greater than 45 minutes - round to hours

  23. Hello all,

    I'm using a worksheet with retail prices where they all end in .99 (i.e. $26.99). I want to take 10% off each price but have it round down to .99. I was using (=ROUNDDOWN([@[List Price]]*0.9,0)-0.01) but the problem is if the list price has a 9 in the ones column it rounds down an extra dollar. For example if you take 10% of 29.99 it would be $26.99 which already ends in .99 so thats what i want. But the function im using rounds that down to $25.99.

  24. Hello all,
    Just wanted to know if anyone have solution to the currency rounding requirement below:

    Cent of 0.01 to 0.04 = Round down to 0 (zero)
    Cent of 0.05 = Remain as 0.05
    Cent of 0.06 to 0.09 = round up as 0.10

    By law in our country the currency need to be rounded in such way due to 1 cent coin is no longer a legal tender.

    Please share if anyone have solution. Thanks in advance

  25. Hi,

    Is there a way to round up to the nearest specific time?
    ie. round 12/09/2020 20:30 to the nearest 6:00am time

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =TRUNC(CEILING(B1,"06:00"))+0.25

      I hope this will help

      1. Hi,

        Thanks for your reply. It does mostly do what i was asking. Is there a way for it to round upwards only? Some of the cells i am using it on is rounding some numbers backwards to "6:00" ie. 14/9/2020 11:40 is getting rounded to 14/9/2020 6:00 as opposed to 15/9/2020 6:00

        1. Hello!
          I ask you to immediately specify all the conditions.
          Please try the following formula:

          =IF((B11-INT(B11))>0.25,INT(B11+1)+0.25,INT(B11)+0.25)

          Hope this is what you need.

          1. Hi,

            Thanks for your reply. Essentially what is needed is column A is a variety of different days and different times for each different day. The cell that will have the answer will need to round column A in the same row forward to 6:00am regardless of what the date is.
            ie
            Column A Column B
            15/09/2020 17:30 16/09/2020 6:00
            15/09/2020 21:43 16/09/2020 6:00
            16/09/2020 3:26 16/09/2020 6:00
            16/09/2020 8:15 17/09/2020 6:00
            16/09/2020 13:26 17/09/2020 6:00
            17/09/2020 5:00 17/09/2020 6:00
            19/09/2020 20:45 20/09/2020 6:00

  26. Hello Everyone,
    If I wish to round off all decimal numbers upto 0.4999 to 0.5 and all decimal numbers greater than or equal to 0.501 to 0.9999 to 1. i.e. if a decimal number is 17.1 then it should be converted to 17.5 and if a decimal number is 29.65 then it should be converted to 30. These numbers should be rounded so that we arrive at the correct calculations.

    How do I do it? Please help

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Use the CEILING function

      =CEILING(A1,0.5)

      Read more here

  27. I have a weight in one column and in the other column I am wanting to multiply it by 65% AND then round it up to the nearest increment of 5 using one formula. Example: My weight is 125 and I want to know 65% of that weight which is 81.25. I would like to round it up to 85. Can i do all of this in one cell. I have this so far =R3*0.65 , but I don't know how to use another function in the same cell to use that function AND round up to the nearest 5. I would like all my weights to end in 5 or 0 ie ( 85,95,100 etc..) Can you help me?

  28. Hi , I am trying to write a formula for total hours in excel .
    4(hrs).22(Min) + 4(hrs).46(Min) = 8(Hrs).68(Min) (Answer should be 9 hrs 8 minutes.)
    7(hrs).30(Min) + 5(hrs).30(Mins)= 12(Hrs).60(Min) (Answer should be 13 hrs)
    Please help to write formula.

    1. Hello!
      If you write 04:42:00 in cell A1, and write 04:46:00 in cell B1, then the formula =A1 + B1 gives 09:08:00. Explain in detail how you got your result and I will try to help you

  29. What formula would I need to use to round up to the nearest $5? Example $63 round up to $65.
    THANKS!!

  30. I'm trying to round 161-169 to 165 and 160 still remain 160 in one cell.
    Thanks to much.

    1. Hello Lynn!
      If I got you right, the formula below will help you with your task:

      =IF(A4-ROUNDDOWN(A4,-1)>0,ROUNDDOWN(A4,-1)+5,A4)

      I hope this will help

  31. Hi
    I’m trying to round figures for my retail prices in one excel column
    For example, simplest way for me to explain
    Anything with a price between 17.01 and 17.50 rounds to 17.45
    Anything with a price between 17.51 and 17.99 I want to round to 17.95
    Any help I can get with this is much appreciated
    Regards,
    Declan

    1. Hello Declan!
      If I understand your task correctly, the following formula should work for you:

      =IF((K1-TRUNC(K1))<=0.5,TRUNC(K1)+0.45,TRUNC(K1)+0.95)

      I hope it’ll be helpful.

  32. Hi,
    I'm trying to write an equation in Excel that rounds using the following rules:
    1. If the extra digit is less than 5, drop the digit.
    2. If the extra digit is greater than 5, drop it and increase the previous digit by one.
    3. If the extra digit is five, then increase the previous digit by one if it is odd; otherwise do not change the previous digit.

    I feel like I'm close to a solution by using the IF(AND(Find line of commands but can't get it to come together properly. Could you please help?
    Thanks so much,
    John

  33. What will be the output of following1) =sum (5,6). 2)= power(5,3). 3)=round(14.67) - round off by one digit after decimal. 4) =max(78,89,48). 5) =LOWER ("CORONA")

    1. Hello!
      What does sum (5,6) or LOWER (“CORONA”) have to do with the ROUND function? For me to be able to help you better, please specify which formula you mean and describe the problem in more detail. Thank you.

  34. Hello, I would like to bypass the normal rounding function. I would like a number between 1.01 - 1.4 to round down and 1.41-1.99 to round up.

    Is this possible?

    1. Hello Paul-Simon!
      If I understand your task correctly, the following formula should work for you:

      =IF(A20-TRUNC(A20) <= 0.4,ROUND(A20,0), ROUND(A20+0.1,0))

      I hope this will help

  35. excel formula in above 6 roundup 10 or below 5 roundown 0 if condition separate cell
    mulitple purpose

  36. Hi, great article but I still can't solve my problem, I have a set number to round up to e.g. Power Supplies come in 2,3,5,8,10,15 Amps, so if my load is 6.1 I need to choose 8A unit, or load is 10.8 chose the 15A unit

  37. Hello, I want to round a value either 1.5 or 1.4 to the nearest whole number 2.0. But if that same cell is 2.0 then the return cell would show 2.0.

    1. Hello Todd!
      If I understand your task correctly, maybe the following formula should work for you:

      =ROUNDUP(A1,0)

      If there is anything else I can help you with, please let me know.

  38. Hi All,

    I need you help on how to make this number 0.282186949 to become 28.21.

    Thanks a lot in advance..

    1. Hello Jeric!
      If I understand your task correctly, the following formula should work for you:

      =ROUNDDOWN(A1*100,2)
      or
      =TRUNC(A1*100,2)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  39. thank you!! it helps so much

  40. Hello,
    I was wondering if there is a way to round a cell up to the nearest dollar amount (for a budget) and then have the added changed moved to another cell where it is added up to a grand total at the end of the month. If this is not possible yet is it something that can be looked into for the future?

  41. I am looking to round up a catalog of figures, rounding DOWN anything below eg 125.30, and rounding up anything greater than 125.30. I am only a casual user, but would appreciate some help!

  42. Sir/ Mam
    i am sivakumar. M From trichy
    my small doubt anybody excel export pls to clarification and help me
    system calculated Rs.11.00 goes to next roundup Rs.20 (increment) This is correct
    system calculated Rs.11.90 goes to next roundup Rs.20 (increment) This is correct
    system calculated Rs.15.90 goes to next roundup Rs.20 (increment) This is correct
    system calculated Rs.19.90 goes to next roundup Rs.20 (increment) This is correct
    system calculated Rs.10.90 goes to next roundup Rs.20 (increment) This is not correct
    system calculated Rs.10.51 goes to next roundup Rs.20 (increment) This is not correct
    system calculated Rs.10.99 goes to next roundup Rs.20 (increment) This is not correct
    The Correct amount is Rs.10/- Only
    The Correct amount is Rs.10.01 to Rs.10.99 Correct amount Rs.10/-

    The Correct amount is Rs.11.00 to Rs.19.99 Correct amount Rs.20/-
    how to get the above result
    anybody help me
    i am waiting for your favorable reply.
    cell : 9843760656

    1. If Value is in col. A and Formula in B
      =IF(A110.99,CEILING(A1,10)))

      Hope this would help.

  43. If i enter the value in decimal value i need it in subtraction.
    Eg : 2.75 = 2-75
    1.001 = 1-001 (or) 1 - 0
    9.138 = 9 - 138
    1 - 70 = 1 - 7

  44. good description,thanks

  45. Can you only use decimals for rounding operations? I am trying to display cells that have formulas in them that were entered as decimals to round to the nearest 16th, but also automatically reduce the resulting fraction. So I would like 12.49 to display as 12 1/2 instead of 12 8/16.

  46. Hello,
    I need a formula for below issue -
    12.34 = 12.34
    5645.89 = 5646
    3456.23 = 3456.23
    4569.51 = 4570
    Please help.
    Thanks

  47. hello,
    how could i put round figure value in excel.
    some examples here !!!
    "1234" if the last digit is under "5", than i want result "1230"
    "5678" if the last digit is up to "5", than i want result "5680"
    "9120" the last digit is "0", than okay.

  48. Hi, I have a problem when using the "Floor" function.
    Suppose I'm preparing a "Over Time " sheet. SO there, the minimum OT will be counted from starting from 1:00 hrs to upwards adding in 00:15 mints time intervals. (Which means, to add OT, I have to work at least minimum 1:00 hr of extra time, other wise lesser time will not be added. Further , if my off time is at 4:15 pm, I have to work until 05:15 pm to gain 1:00 hr OT. Suppose if I off at 05:00 Pm, then that 00:45 mints will not be counted as the minimum requirement to OT get counted is 1:00 working hr, which means 05:15 pm. After 05:15 pm, OT will be added in 00:15 mints basis.
    SO my question is that, when I put all the formulas of, IF, Floor,...etc, all the functions work correctly, but except for a one incident. When my off time is 05:30 pm, it counts as 01:00 hr OT when I put it to "Floor" function. But actually, it should be floored as 01:15 hrs, right?
    So except for that particular time, the function gives exact answer for all other off times, including 05:15 pm. Why this happens? Is it because I have have limited my OT to counting from 05:15 pm? Because it is the first 00:15 minutes after first 01:00 hr OT?
    Please explain, if any one knows? WHat kind of change I should do in my "Floor " function?

  49. Contract Worked Start End
    3.75 5 08/05/2019 08:38 08/05/2019 13:43
    I need a formula that will return Not clocked in if start is missing, Not clocked out if end is missing, not clocked in or out if start and end are both missing, start - end - contract as a decimal ie 3, 3.25, 3.5, 3.75.
    this information comes to me as a list of 500+individuals and I want to drag the formula to all rows.
    Worked may not be the same as end-start.
    Thankyou

    1. 2 3 07/05/2019 10:20 07/05/2019 13:28
      10:20 should round to 10:15 (nearest 15 mins) and 13:28 should round to 13:30 making the difference 3.25 I can take the worked hours (3) away and see they have worked .25 hours more than stated in the worked column.

  50. please help me i m very confuse in formula lets see
    (any value) * 0.2% = result
    i want that the result upto 0.50 value convert to lower value and up 0.50 count as next decimel like as 11250 * 0.2% = 22.50 this count as 22 other hand 12999 * 0.2% = 25.99 this count as 26.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)