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

  1. How do I make a fraction round up to the nearest fraction that i set?

  2. Hi, can anyone help please? I'm trying to round exam scores with 4 parts, so results will come in as, say, 5,8,5 and 7 out of ten (25/40) but I need to round the average of between 0.25 and 0.74 to a .5 score, and .75 to .24 score to a whole number.
    To clarify, a formula that makes:
    5+5+5+6 display 5.5
    6+6+6+5 display 6.0
    6+6+6+7 display 6.5
    2+9+7+5 display 6.0

    Many thanks

  3. I want to always round up to the nearest $100.
    example: $400.01 should round up to $500, NOT down to $400

  4. i want time times to be converted.If the time is at 12 minutes or
    less then round down to the nearest quarter hour. If the time is at 13 minutes or
    more, then round up to the nearest quarter hour.

    Example
    13:12 to 13:00
    13:13 to 13:15
    13:27 to 13:15
    13:28 to 13:30
    13:57 to 13:45
    13:58 to 14:00

  5. I want to write a formula that rounds anything from .1 up to the nearest integer, but anything below .1 (i.e. .07) round down to the nearest integer.

    So if I get 2.07, it becomes 2.
    But if it's 2.15 it becomes 3.

    How can I do this please?

  6. Hi
    I want to use MROUND to round numbers in many cells within a column provided that the sum of all cell not exceed or be less than a specified value...Sometimes I get more than the value and some other arrangments I get less..(To make it clear the values in the cells that I want to ROUND are actually results of multiplying different presentage with the common specified value)
    Thank you

  7. A COLUMN B COLUMN
    row 1 row1
    BASIC SALARY 17500/- NET SALARY 13417/-
    17500/30*23=13417/-
    IT IS FIXED TO NOT ABOVE 15000/-
    MY FORMULA IS IF(B1>15000,"15000"*13%,IF(B1<=15000,B1*13%))
    THE RESULT IS 13417 x 13% =1744.21, I WANT TO ROUND THE AMOUNT TO 1744.00
    PLEASE SUGGEST ME THE CORRECT FORMULA

    1. Hello, Shravan,

      If we understand your task correctly, you need to round the result you get. If so, please try the formula below:

      =ROUND(IF(B1 > 15000, "15000"* 13%, IF(B1 <= 15000, B1 *13%)), 0)

      Hope this is what you need.

  8. Hello,

    I have searched and cannot find an answer specific to my needs. Can anyone help, please?

    I need to round down negative numbers to the nearest 0.25

    Examples:
    -1.94 to -1.75
    -2.14 to -2
    -2.64 to -2.5

  9. Hello ,
    Need help, in excel I want the 3rd decimals to be either 0 or 5
    Eg
    1.3423 want to change to 1.340
    1.3472 change to 1.345

    3rd deci condition
    =.005 -.009 will be .005

    Any recommendations

    Thanks

  10. for example
    if my number 0.5 result will be 0.5
    if my number less than 0.5 result will be 0
    if my number greater than 0.5 result will be 1

    please give formula i will apply for this automatic

    1. Hello, Pragnesh,

      Please try the formula below:

      =IF(A1>0.5, 1, IF(A1=0.5, 0.5, 0.5))

      Hope this is what you need.

  11. I realized that I have made typo error in the following sentence.

    Assuming that 172:24 lies in cell A!, I use the function MROUND(A1,"00:30").

    Please read it as corrected below:

    Assuming that 172:24 lies in cell A1, I use the function MROUND(A1,"00:30").

    I regret the inconvenience.

  12. Hi!

    I am facing a problem while trying to use MROUND function for rounding off time.

    When I want to round up some amount of time say for example 172:24 to 172:30. Assuming that 172:24 lies in cell A!, I use the function MROUND(A1,"00:30"). I know that this function is perfectly alright. But when I input the said time amount in cell A1, it converts automatically to 04:24 and in the formula bar, it displays 07-01-1900 04:24:00. Obviously the I get wrong result of 04:30.

    After spending much time and efforts, I came to know that it happens for all the times which exceed 23:59. (For your information, I have tried both the number formats - time (hh:mm) and Custom (hh:mm))

    Can you help me? I need getting many such sums of time - that is over time hours of our employees converted to next 30 minutes.

  13. I would like to seek advice on creating a forumula rounding to the nearest quarter hour with the following requirements:

    11 minutes or above:round upto 15
    26 minutes or above:round upto 30
    41 minutes or above:round upto 45
    56 minutes or above:round upto 60

  14. Any negative number in excel AS -45326 how possible fro number as 45326.00

  15. Hi, I am needing to write a formula that rounds to the nearest 0.5 however wont round to numbers that end in 2, 4.5, 7 & 9.5.

    For example 12.05 would round to 12.5 and 11.95 to 11.5
    -but 13.05 would round to 13.0 and 12.95 to 12.5.

    Is this possible?

    Thanks and regards, Jason

    1. Hi Jason,

      It looks like you need to use the FLOOR function to solve your task. Please try the formula below:

      =FLOOR(A1,0.5)

      Hope this is what you need.

  16. Hi, I need help to fix the formula for billable time. It's not exactly where I want it to be...
    =IF(A3-B3<=TIME(0,2,0),0.1,ROUND((A3-B3)*24,1))

    A3-B3 = the time difference
    I am trying to fix the error when 1-8 minutes to show 0.1 of hours. In other words,
    1-8 = 0.1
    9-14= 0.2
    15-20= 0.3
    21-26= 0.4
    27-32= 0.5
    33-38= 0.6
    39-44= 0.7
    45-50= 0.8
    51-56= 0.9
    57 and up= 1.0

    I also have this part of the formula which basically is that above, not sure how to use it

    =IF(AND(1 <=X72-W72,X72-W72<=8),0.1,0)+IF(AND(9 <=X72-W72,X72-W72<=14),0.2,0)+IF(AND(15 <=X72-W72,X72-W72<=20), 0.3,0)+IF(AND(21 <=X72-W72,X72-W72<=26),0.4,0)+IF(AND(27 <=X72-W72,X72-W72<=32), 0.5,0)+IF(AND(33 <=X72-W72,X72-W72<=38),0.6,0)+IF(AND(39 <=X72-W72,X72-W72<=44), 0.7,0)+IF(AND(45 <=X72-W72,X72-W72<=50),0.8,0)+IF(AND(51 <=X72-W72,X72-W72<=56), 0.9,0)+IF(AND(57 <=X72-W72,X72-W72<=59), 1,0)

    Thank you so much.

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

  18. I want to know when I type 5. Cell automatically insert 5.5.

  19. Good evening!I'm needing to add IF formula in TRUNC calculated numerical digit. Everything is OK but when i input in G16 double numeric digit (example: 25.20) small bag condition its not work properly. please solve the problem.

    G16 (input double digit numerical value 25.20 or 7.20 already is working)

    =G16-TRUNC(G16)
    =IF(H16=0.5,"large bag",IF(H16=0.43,"medium bag",IF(H16=0.2,"small bag","-")))

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

  21. formula that give me results as:

    4.45 = 4.4
    4.55 = 4.6
    4.05 = 4.0

    x.yz
    if y = 0, 2, 4, 6, 8 ( even ) disregard z
    if y = 1,3,5,7,9 (odd) add 1 on y.
    if z 5 add to y

  22. Hi Svetlana Cheusheva!

    It was very useful to solve my requirement. Excel is an ocean we agree.
    Knowledge is all and it is worthy too.
    Thanks again for Svetlana Cheusheva and also other friends sharing your knowledge here.

    With Great Thanks.
    Alexos.

  23. Hi There , i need to round ex:453 , 458 to nearest 5 or nearest 10 . any help thanks

  24. I need to change dollars and cents to whole dollars and multiply by certain percentages and the resulting answer should not be in whole dollars; eg:
    $33350.97 becomes $33,350*35% = $11672.50 (rounded to 2 decimal places)

  25. how to round off time only if minute is greater then or equals to 30 to the next(higher hour)
    ex.01:35 to 02:00 hrs
    01:45 to 02:00 hrs
    but 01:29 then as it is 01:29 hrs

    please help

  26. Hi,

    Roundup Value method (at the time of multiplying)
    62056*1.5%=930.84 apply formula as =ROUNDUP(62056*1.5%,0)
    or =ROUNDUP(930.84,0)

    62056*1.6=99,289.60 apply formula as =ROUNDUP(62056*1.6,0)
    or =ROUNDUP(99289.60,0)
    (Put = & TYPE roundup ( Open bracket type/link the value and * with
    percentage or number and add ,0 then close the ) bracket and press

    enter.

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

  28. how to create formula for

    82.01 & 82.02= 82.00 or 0.01-0.02=0.00
    82.03 & 82.04= 82.05 or 0.03-0.04=0.05
    82.06 & 82.07= 82.05 or 0.06-0.07=0.05
    82.08 & 82.09= 82.10 or 0.08-0.09=0.10

    please help me please...

  29. Hi There,
    Anyone can help me? for " positive number", it would be round up and for " negative number", it would be round down with "no".
    example 307/30 roundup 11
    -15/9 rounddowwn -2 but i wanna see "no"

  30. Hi, I have problem here. How do i round number like below

    0.0 to 0.0
    0.1 to 0.0
    0.2 to 0.0
    0.3 to 0.0
    0.4 to 0.0

    0.5 to 0.5

    0.6 to 1.0
    0.7 to 1.0
    0.8 to 1.0
    0.9 to 1.0
    1.0 to 1.0

    Can anyone help?

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

  32. How can i round up number such as:
    0.00 to 0.00
    Below 0.01 to 0.00
    above 0.01 to 1.00

  33. how to 4165*10% round formula

  34. Hi. I am trying to round the difference between 2 times to the nearest 15 min increment. I know I can use MROUND to round the times and then do the difference but trying to see if I can just do it on the difference. Thanks

    Jay

  35. After a few moments of play, I believe this accomplishes what I want.
    {=SUM(ROUND(IF(ISNUMBER(A1:A5),A1:A5,0),0))}

    Thank you for this site, which got me thinking about a solution.

  36. When summing rounded values, as in:
    {=SUM(ROUND(A1:A5,0))}
    some cells may not contain a number value (common case as in "N/A").
    Any thoughts whether it is possible to add validation as number prior to the round function, so that the formula does not return #VALUE!? Essentially, eliminate non-numeric values, before rounding each, and then summing.

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

  38. Hi,

    Is there a way to round up an input in the same cell without adding a new column for the return result? eg, data validation?

  39. I want to know the formula which decimals not to calculate while sum total

    if
    1.5 1.5 1.5 1.5 1.5 1.5 --> 6 to calculate with out decimal caculateing

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

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

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

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

  44. 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,""))))))))

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

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

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

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

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

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

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