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 9. Total comments: 375

  1. Hi,
    is there anybody who knows how I can round the calculation result to end in -9

    I have a forumula (D2 + $H$2 +$H$4 + $H$5)the result of which i want to end in number -9
    bgrds
    Dodo

  2. Hello, how do i formulate a cell for the figures to end in a certain number (ex. 195, 295) and not in multiples of 95?

  3. Hello, I want to roundup cell 2564 =2560 and I want to roundup the 2564 =2570 in same cell and How can I do this task.

  4. I need to Round the following lines to the nearest 1/16. But Excel will not allow this. for example,

    IF(D9=2,D5/2,

    I may get 83.3333 but I need 83.125.

    =IF(OR(D3=0,D3=""),"",
    IF(D9=1,D5,
    IF(D9=2,D5/2,
    IF(D9=3,D5/3,
    IF(D9=4,D5/4,
    IF(D9=5,D5/5,
    IF(D9=6,D5/6,
    IF(D9=7,D5/7,""))))))))

  5. I am needing to found any thing above 100 back down to 100. How do I write this formula?

  6. Hi,

    Which formula used for the round in the multiple of 10 for both figure( Negative as well as positive).

    Mround is used only for the positive figure and rounddown is for negative figure but i want 1 formula for both the figure.

    Is there any???

    Please tell me............

  7. Hullo, I want to roundup cell I3 which carries an amount 2100 and I want to roundup the same as 3,000 in another cell and How can I do this task.

  8. whole numbers such as 201
    202
    203
    204
    205
    206
    207
    208
    290
    210

    1-2 round down ie 201 to 199 202 to 199
    3-6 rounded to 5
    202 203 204 205 206 become 205
    207 208 209 210 become 209

    anyone help?

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

  10. Hi, Can hi you help me to get this?
    I can't really came up with a solution for this problem
    say I want 1mins-4mins rounded into 0 and 5mins-19mins rounded into 15mins then 20-34mins rounded into 30mins and 35mins-1hr4mins rounded into 1hr/60mins
    i hope yo ucan help me with this thanks :)

  11. Please help me get the rounddown or roundup for the following:
    a) 1.01 - 1.85 round down to 1
    b) more than 1.86 round up to 2

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

  13. Hi Team,

    I want a simple formula for round-off, like -
    a) 0 Years, 9 Months, 15 Days - Should be rounded off to 9 Months
    b) 0 Years, 9 Months, 16 Days - Should be rounded off to 10 Months
    means, anyone, below 15 or equivalent to 15 days would remain 09 Months and
    anyone, greater than or equivalent to 16 days would be 10 months.

    Thanks & regards
    Arnab Sarkar

  14. In cell A1 have 10 and cell B1 is 8.50681, when I multiply A1*B1 result appear is "85.0681", But I show in Cell B1 is 8.50 till (2 decimal point). And now I want to multiply A1*B1, and I want that excel count till 2 decimal point like 8.50 in B1. Is it possible? remember B1 actual value is 8.50681.

  15. what i have to do if bulk amount need to round off, if the amount will >5 so it will grow upside,if the amount <5 so it will get down side

  16. How to convert upper than 15 digit value in excel

  17. I am having a problem using ceiling function.
    Now my Problem is : I want to ceil few values upto 0.5 and then rest upto 0.25 in the SAME CELL !! like if i want to ceil values between 2 and 3 upto 0.5 i.e if i write 2.3 it gives me 2.5 or if i write 2.74 it gives me 3. But in the same cell if i write values between 0.5 to 2, it should ceil me upto 0.25 i.e. if i write 1.2 it should give me 1.25 or if i write 1.67 it should give me 1.75. Hoping for a reply. Thanks for the concern.

  18. Can you explain why this happens?
    For the same input of 0.01, the spreadsheet should not be giving .02, .02 is incorrect. But why? Thanks.

    =ceiling(0.01,0.01) = .01
    =ceiling(16.00-15.99,0.01) = .01
    =ceiling(16.01-16.00,0.01) = .02

    =roundup(0.01,2) = .01
    =roundup(16.00-15.99,2) = .01
    =roundup(16.01-16.00,2) = .02

  19. can I round up years? for example 1 years, 3 months, 4 days, rounded up to 2 years.

    Could you give me the formulation? thanks.

  20. Is there a way to force excel to round the following decimal down, instead of up? $2.225. I want it to round any number where the third decimal place is a "5" (and only a 5, not any other number) down, instead of up.

    For instance: $2.225 to $2.22
    $2.226 auto rounds to $2.23, this is fine
    $2.224 auto rounds to $2.22, this is fine

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

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

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

  24. Can you explain why this happens?

    =ceiling(0.01,0.01) = .01
    =ceiling(16.00-15.99,0.01) = .01
    =ceiling(16.01-16.00,0.01) = .02

    =roundup(0.01,2) = .01
    =roundup(16.00-15.99,2) = .01
    =roundup(16.01-16.00,2) = .02

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

  26. if i have a function that if c1=8 and c1 cells contains value 8 and then i create this that if the value is true then i will forward that if it is true then it take the value of cell d1 which value is 2x1.8 then how i will solve this that it gives value 3.6 automatically not the value same 2x1.8 in the cell where i put this formula

  27. if i have a function that if c1=8 and c1 cells contains value 2x1.8 then how i will solve this that it gives value 3.6 automatically not the value same 2x1.8 in the cell where i put this formula

  28. Very helpful it was required while working and finally I got the solution and the result...

    Thank you...

  29. Hi

    Firstly great article very useful info on rounding.

    Now to my query is there a formula that will convert negative figures into positive figures with out affecting the value.

    The reason for this is I'm a bookkeeper and when i am downloading reports to excel to match to data downloaded from a CV file from bank. The debit side will sometimes show in a negative dependant on program its downloaded from, its makes it a nuisance when it in a negative as I have to add the figures together as one report will have positive figures and other report will have negative figure. When I would rather just do the usual (this-this=this), the outcome its to have the amount it equals to is 0. Then we know our reports match, & if it doesn't it then helps us find the out of balance amount.

    If we add further information to get a percentage or divided value, if the amount is in negative it gives an incorrect figure. Going through every figure and removing the - from the each amount takes up a lot of time, especially when the report is very long. This is time wasted when i could be making use of my time more efficiently.

    Im hoping yo will say yep here is an easy fix and not say no sorry no solution. My fingers are crossed

    Regards Vivien Anderson
    C/- VLA Bookkeeping Services

  30. =(G54-F54)/(K54/L54)*2 = 11013.1765 round

  31. =(G54-F54)/(K54/L54)*2 PLS ROUND UP THIS FORRMULATION

  32. Good day. How can i round up number such as:
    0.00 to 0.00
    0.01 to 0.00
    0.02 to 0.00
    0.03 to 0.00
    0.04 to 0.05
    0.05 to 0.05
    0.06 to 0.05
    0.07 to 0.10
    0.08 to 0.10
    0.09 to 0.10
    0.10 to 0.10

  33. Hi, please can someone help. 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

  34. can any one solve this issue
    any numeric number convert in to number like

    <3 = 0
    3=3
    4=3
    5=3
    6=6
    7=6
    8=6
    9=9
    10=9

  35. Is there any way to set a rule in terms of when to round?
    For instance, at my job when we're billing by hand, we round anything 0.456 or above to 1 and anything 0.455 or below to 0. In Excel, however, I've tried several different ways to round according to the rules of my job with no luck. 0.456, even 0.5 most of the time, gets rounded down to 0. We create these number charts and most numbers round correctly but some numbers do fall victim to this discrepancy and I need to make sure we don't have errors because of this issue. What do you suggest?

  36. Hello,

    I am trying to sum multiple decimals. If sum equals .05 or higher, I need to round up to the nearest whole number. Here is an example:

    0.47
    0.19
    0.39
    -------
    1.05

    I need to round up to 2.00

    If sum equals 2.07 round up to 3.00 etc..

    Thanks for your time!

    Jason

  37. Hello,

    I am trying to sum multiple decimals. If sum equals .05 or higher, I need to round up to the nearest whole number. Here is an example:

    0.47
    0.19
    0.39
    -------
    1.05

    I need to round up to 2.

    If sum equals 2.07 round up to 3 etc..

    Thanks for your time!

    Jason

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

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

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



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