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

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

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

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

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

  5. good description,thanks

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

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

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

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

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

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

  12. hi,

    Anybody can tells me. how i can MROUND such below within a one Drag for both negative and positive values.
    eg:
    62 = count as 60
    65 = count as 70
    419 = count as 420
    601 = count as 600
    -24 = count as -20
    -37 = count as -40

    1. (ROUND(AZ9/10,0))*10

  13. i need formula for round minutes, spreadsheet have in cells 0:31 as 31 minutes
    5-18 minutes count as 15 min
    19--34 minutes as 30 minutes
    35-49 minutes as 45 min
    above 50min count as 1 hour

  14. I need a formula for average as per the following condition
    C4 should return B4 if both result 1 and 2 are non-numeric values(text)
    A B C
    1 Result 1 Result2 Average
    2 1 2 1.5
    3 <1 2 2
    4 < <1 #DIV/0!

    Can anybody help me out?

  15. 111.000 = 111
    11.100 = 11.1
    1.110 = 1.11
    .111 = 0.111

    please explain how to make a formula for the above

  16. Hi,
    I am using excel to calculate a sale price based on lengths of wood and have a calculation already in place that gives me an overall price for a frame.

    However, for example, I don't want the price to be less than £30 or more than say £100.

    How can I ask excel to round up to £30 but no more than £100 but still show me the exact figure from the original calculation if between 30-100?

    I've tried using IF's and ROUND but end up with 'FALSE' - can you help please?

    Thanks

  17. Hi mam
    Can u tell me , how can we remember all the formulas.

  18. Hi, Is there a function where I "dictate" to Excel to help:

    A) Round UP to the nearest $0.10 cent if it is > $0.08
    $15.78 -> $15.80

    B) Round DOWN to the nearest $0.10 if it is $15.70

    Do I need to combine two formulas together?

    Thanks in advance.

    1. Hi Autumn,

      There's not a short formula, but this may work:
      cell is the cell you're refering to (A2, B3, etc.)

      =IF((cell-ROUNDDOWN(cell,0))>0.08,CEILING(cell,0.1),FLOOR(cell,0.1))

      If you needed it to round up 0.80 as well and not only .80001 -> .9999, you would use:

      =IF((cell-ROUNDDOWN(cell,0))>=0.08,CEILING(cell,0.1),FLOOR(cell,0.1))

  19. Hi Mary
    I would like to round above an whole number to .49 or .99, ie 2.24 to become 2.49 and 2.52 to become 2.99
    please could you help

    1. =CEILING(cell,0.50)-0.01

  20. Hi!
    I need help!!
    trying to figure out but just cant get it right...
    one of the formula that i tried:-
    =TRUNC(21/12*A3,0.5)+IF(TRUNC(21/12*A3)=0.5,0,0.5)

    I need something like the result for 21/12*A3- if its anything = or above .5 will be 1.5/ 2.5/ 3.5 etc if anything below .5 will be whole number 1/2/3... etc...

    Anybody able to help pls?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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