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 3. Total comments: 225

  1. Hello,

    I get time downloaded from a stopwatch in a hours:minutes:seconds:decimal seconds format. How do I round the seconds+decimal to just seconds e.g. 00:35:27.35782 to display as 00:35:27 ?

    1. Dave:
      If you want to display the time as 00:35:27 from 00:35:27.35782 select the cell holding the time and click on Format Cells then Custom the select the [h]:mm:ss option from the list and add another h in the square bracket. Click OK and that should display it the way you want.

  2. Hello---
    I am trying to round all of my numbers to three decimal places, and keep the zero at the end. For example, I want the number -0.0799488 to round to -0.080. When I use ROUND(cell,3), it rounds it to -0.08 (two decimals instead of three). Thank you.

    1. Lindsey:
      Try formatting the cells using this custom format: #,##0.000.
      Go to Format Cells, choose Custom option and enter this formatting in the field, save it and you're out.

      1. Thank you for your quick reply, Doug. I should have been more thorough in my question. I am trying to use the ROUND function inside the IF function to add notation about statistical significance to large tables. My formula is this:

        =IF(G15<=0.001,ROUND(E15,3)&"***",IF(G15<=0.01,ROUND(E15,3)&"**",IF(G15<=0.05,ROUND(E15,3)&"*",IF(G15<=0.1,ROUND(E15,3)&"+",ROUND(E15,3)))))

        When I apply this formula, the cells that have added asterisks/plus signs do not keep the trailing zeroes. The cells that do not have added notation (the null values) DO keep the trailing zeroes. Custom formatting after the fact does not change the trailing zeroes issue for the ones that have had asterisks added.

        Do you have any other thoughts?

        1. Working solution to this issue is to use the TEXT function with the ROUND function. This keeps the trailing zeroes and asterisks intact.

          =IF(D5<=0.001,TEXT(ROUND(B5,3),"0.000")&"***",IF(D5<=0.01,TEXT(ROUND(B5,3),"0.000")&"**",IF(D5<=0.05,TEXT(ROUND(B5,3),"0.000")&"*",IF(D5<=0.1,TEXT(ROUND(B5,3),"0.000")&"+",TEXT(ROUND(B5,3),"0.000")))))

  3. IF this Result should be

    350.1 350
    350.5 350
    350.9 350
    351 360
    351.5 360
    359.9 360
    360 360

    basically if my ones is Zero i want to rounddown and if the ones is 1to9 then tens will be rounded up...

    suggest the formula

    1. Please try the formula below:

      =(INT(A1)-RIGHT(INT(A1),1))+IF(RIGHT(INT(A1),1)="0",0,10)

      Hope this will help.

  4. hi there!
    im having a problem on how to put "if the tenths digit number is ODD;roundup and if the tenths digit number is EVEN;rounddown" in excel formula. thanks in advance!

    example:
    24.45= 24.4
    24.15= 24.2

    1. Hello,

      Please try the following formula:

      =IF(ISODD(MID(A1,FIND(".",A1)+1,1)),ROUNDUP(A1,1),ROUNDDOWN(A1,1))

      Hope it will help you.

      1. Hello,
        I need a formula for below issue
        12.45 = 12.45
        12.23 = 12.23
        12.51 = 13
        12.79= 13

  5. Hello,

    Thank you for the helpful information. I just wanted to point out that under the "TRUNC Function", in the screenshot example, the third row's formula should be "=TRUNC(A3,1)" in order to get the number in B3. Just don't want others to get confused.

    Thanks,
    Steven

    1. Hello Steven,

      Thank you for pointing that out! Fixed.

  6. Hey, does anyone know how I can round up or round down an equation?
    (A1-10)/2

    1. =ROUND((A1-10)/2,0)
      The 0 is for no decimal places. replace it with 2 for 2 decimal places or maybe with -1 to round to the nearest 10.

  7. Good afternoon,

    I'm needing to add 2 different percentages to a formula and they both need to round. I will need to round the first percentage before i can add the second percentage to that number. Right now excel will not allow me to round each percentage but will allow me to round both percentages at the end.

    example:

    this is what i need to happen in my formula
    218*-15%= -32.7 (round up)

    218-33=185

    185*8%=14.8 (round up)

    185+15=200

    200+205=405

    405 is the number i need to reach
    and this is my formula in excel now:

    A3=218
    B3=-15%
    C3=8%
    D3=205

    =ROUND(A3*B3+A3*C3,0)+A3+D3 and that comes up to 408, which is 3 over from 405

    Before, i needed to only add one percentage and the round formula works great but i cannot find a way to get it close. I have tried with a larger number, replace A3(218) with 1304 and the gap was larger and will cause problems.

    example:

    it should figure out to 1402

    1304*-15%=195.6 (round up)

    1304-196=1108

    1108*8%=88.64 (round up)

    1108+89=1197

    1197+205=1402

    And in excel, with the same formula as above, i get 1418. I know with percentages you will have numbers be maybe 1 or 2 off due to rounding but this is too great and will not work.

    Please let me know if there is another way to write this formula to make it work properly.

    Thanks.

    1. well i found a formula that worked

      (ROUND((8%+1)*(ROUND(218*-15%,0)+218),0))+205=405

      just fyi...

  8. =ROUND(B1/10^TRUNC(LOG(B1)),A1-IF(B1>1,1,0))*10^TRUNC(LOG(B1))

    1. I prematurely posted a significant digit rounding I've been using. Sediment is what I deal with and it is the size of clay in mm to cobble in mm, where significant digits count. I don't know of a function to do this so I wrote this formula; =ROUND(B1/10^TRUNC(LOG(B1)),A1-IF(B1>1,1,0))*10^TRUNC(LOG(B1)) with cell A1 the significant digits and B1 the input number to be rounded.

  9. Is there any way to round a column of currency to an even number (reducing a dollar amount by $.01)
    $887.65 to $887.64 leaving the even numbers as is.

    I can easily round up. I'm not finding a way to round down.

    1. Hi Julie,

      To round down to the even hundredth, you can use the FLOOR function with multiple 0.02, e.g.:

      =FLOOR(A1, 0.02)

  10. maam, how to round off even a minute to the next(higher hour) ex.01:05 to 02:00 hrs please help

    0

    1. Hi Kandan,

      You can use any of the following formulas:

      =CEILING(A1, TIME(1,0,0))
      =CEILING(A1, "1:00")
      =ROUNDUP(A1*24,0)/24

  11. Hi, I want round off this time,1:16 to the nearest 5 minutes,anybody can help me

    1. Hi Masooda,

      To round time to the nearest 5 minutes, you can use the MROUND function with multiple equal to "0:05", e.g.:
      =MROUND(A1,"0:05")

  12. Hey - I wondered if you might help. I'm trying to work on membership rates and wanting to round down to $1,500 if the formula we use surpasses that mark. Any ideas people? Many thanks in advance.

    1. Hi Richard,

      You can embed your formula into the IF function, like this:
      =IF(formula()>1500, 1500, formula())

  13. hi,

    Anybody can tells me. how i can count such below within a one Drag.

    1 to 5 = count as 5
    6 to 10 = count as 10
    11 to 20 = count as 20

    Thanks

    Kuldeep Singh
    Mob:- 9540790777

    1. Hello Kuldeep,

      Please try this formula:
      =IF(AND(1 <=C18,C18<=5), 5,0)+IF(AND(6 <=C18,C18<=10), 10,0)+IF(AND(11 <=C18,C18<=20), 20,0)

      1. HI THIS NOT WORKING PLEASE SEND ME AGAIN THIS IS VERY IMPORTAND TO ME

      2. Hi, Fedor, Can you help with my formula, which I think is similar to the problem above. I need to round my pricing so that the numbers end in either a 5 or a 9. The prices are whole numbers without decimals e.g $893.

        The rules are as follows

        Number ending:
        1 - round down to 9
        2 - round up to 5
        3 - round up to 5
        4 - round up to 5
        5 - no change
        6 - round down to 5
        7 - round up to 9
        8 - round up to 9
        9 - no change
        0 - round down to 9

  14. =ROUND(SUM(B5*(113/113)+64.5-71),0)
    If anyone can help me with the above formula.
    With the above the positive numbers are moved away from zero to the next whole number and negative numbers are also moved away from zero to the next whole number.
    Examples (Positive) :
    1.6=2
    1.5=2
    1.4=1
    Examples (Negative)
    -1.6=-2
    -1.5=-2
    -1.4=-1
    The positive numbers are fine the fix I am looking for is when the negative numbers finish in -0.5 they get moved away from zero to the next whole number but I need them to go towards zero as shown in the examples below.
    Examples (Negative)
    -0.5=0
    -1.5=-1
    -2.5=-2
    So the function I am looking for is to move positive numbers away from zero, and negative numbers towards zero when they finish either 0.5 or -0.5 to the next whole number
    Hope you can help
    Regards Keith

    1. Hello Keith,

      Please try this formula: =TRUNC(L5, 0)+IF(L5-TRUNC(L5) >= 0.5, 1, 0)
      You should get the following result:

      1.6 2
      1.5 2
      1.4 1

      -0.5 0
      -1.5 -1
      -2.5 -2

      1. Thank u for your guidance
        Regards

      2. CAN YOU HELP ME TO GET THE FOLLOWING RESULT? ANY FORMULA FOR THIS?
        0 TO 0.3= 0
        0.31 TO .80=.5
        0.81 TO 1= 1

  15. I want to make grade of a exam result sheet. Grades will be if marks between 50 and 54, "C", Marks bet. 54 and 60, "B", bet. 60 and 100, "A". Please show How do I have to write the formula?

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)