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

  1. Hey, what formula can I use for this:

    I want to round every number to the nearest "9". For example: 173 --> 169 & 176 --> 179

    How can I do this?

  2. Hi..!
    Hello! Is it possible to round off 3rd decimal place?
    (1,2,8,9 =0) (3,4,6,7=5)
    Last digit need 0 or 5

    Eg:1.224 = 1.225
    1.222=1.220
    1.228=1.230
    1.227=1.225

      1. Yes, it's working..!!!

        Thank you ?

  3. My percentages aren't adding up correctly.

    Cell and values used
    H1=1019
    H2=566
    H3=271
    H4=123

    this is what I used to get the % and the cells I used
    =H2/H1 I get 55.54% then I decrease the decimal place to get 56% in cell I2
    =H3/H1 I get 26.59% then I decrease the decimal place to get 27% in cell I3
    =H4/H1 I get 12.07% then I decrease the decimal place to get 12% in cell I4

    This is what I can't get correct
    in the cell, I10 I but this in =I2+I3+I4 and I should get 95% but I keep getting 94%

    It's adding up the 55.54+26.59+12.07 and I get 94.21 but I need 56+27+12 to get 95%

    Why is it reading the decimal instead of reading the rounded %

    Thanks

    1. Hello!
      If you change the format of a number, then that number does not change. Use rounding functions to round a number. Then the calculation result will correspond to what you see in the table.

  4. I need to find a calculation that will allow me to round exponents down at each step of multiplication.

    Example. We start with 180 and multiply by 1.2 = 216. It is already a whole number, so no problem.
    180*1.2*1.2 = 259.2 (Should round to 259, not too hard if using the round down feature)
    but if the person was to select that they want 5 { 180*1.2^5 }, this equals 447.8976 (round down would be 447) BUT if we were to take it in sequence AND round down at each step, it would only be 446.4 (446)

    Any ideas on where to start?

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

      =ROUNDDOWN(ROUNDDOWN(ROUNDDOWN(ROUNDDOWN(ROUNDDOWN(180*1.2,0)*1.2,0)*1.2,0)*1.2,0)*1.2,0)

      1. I really appreciate the above answer, worst comes to worst, I might just make a table.
        The complication is that the person might select 2 or 4 or 5 or 8 or any number in between really.
        The situation is having to increase the number by 20% incrementally, while rounding down at each step.

        1. Hi!
          Write down the calculations in the table and choose the desired value from it depending on the number.

  5. Hi, Is there anyway to round where 0.005 rounds down, and 0.006 rounds up?
    Usually 0.005 rounds up, but for my specific calculation i need it to go down.

    1. Hello!
      I believe the following formula will help you solve your task:

      =IF(AND(ISEVEN(A1*10^0),ROUND(MOD(A1*10^0,1),3)<=0.005),ROUNDDOWN(A1,2),ROUND(A1,2))

  6. 1.8 * 2.33 * 1.25 = 5.2425
    2.0 * 2.33 * 1.25 = 5.8250
    1.0 * 2.33 * 1.25 = 2.9125

    I need to round

    5.2425 ..... 5.00 (0.00 to 0.49)
    5.8250 ..... 5.50 (0.50 to 0.89)
    2.9125 ..... 3.00 (0.90 to 0.99)

    Please Help me

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

      =INT(A1)+IF(A1-INT(A1)<0.5,0,IF(A1-INT(A1)<0.9,0.5,1))

  7. I have a PRODUCT function that I want to result in an EVEN three decimal place number. I start with

    =PRODUCT(A3,2,0.001)*100

    which results in 0.741, which is correct, but I want it to automatically round up to the next even third decimal = 0.742.

    I've tried ROUND and EVEN, but not sure how to format in the function formula. Any help is appreciated. Thank you!

      1. Thank you for the help, Alexander. Unfortunately, that still returns the result 0.741. I also tried using ROUNDUP, and got the same result.

        1. Hi!
          What number does the function return? Write it in full. After 0.741 there are more numbers. Are you sure 0.742 is the correct result?

    2. I should also mention that I want to apply this to a column of results, and want to keep the numbers that are already even as such. Thanks again!

    1. Hi Mr. Alex,

      Thank you for the answer, but is there any way that I can still use the same formula both less than or higher than 100 result?

      Ex:
      (A1=Any numbers) x (B1=0.005) = result 100 & up (decimal numbers should be round up to peso)

      1. (A1=10,275) x (B1=0.005) = 100 (Result not falls below 100)
      2. (A1=75,420) x (B1=0.005) = 378

      Pls help me find out what's the only ONE FORMULA should be using.

      Thank you!

      1. Hi!
        The formula works for your examples 1 and 2. What is the problem? I do not understand your question.

        1. Hi Mr. Alex,

          Thank you for the response! The formula you gave only works for the value less than 100 only. What about my example 2 ,the result shows like this #VALUE!.

          To make things clear, let me refrain my examples.

          FORMULA:
          (Value) x (0.005) = RESULT

          (NOTE: minimum result is 100 & up and the decimal numbers should always automatically be round up to a whole number)

          Ex:

          (Value) x (0.005) = RESULT
          Actual result Result should be
          1. If I change the value to 10,275 x 0.005 = 51.38 100 (minimum result)
          2. If I change the value to 75,420 x 0.005 = 377.10 378 (decimals round up)

          My question, is there any way that we can use ONLY ONE FORMULA that works both examples 1 & 2? I use this formula =ROUNDUP(value*0.005,0) but it works example 2 only hahaha. Hope you can help me.

          Thank you in advance! ♥♥

          1. Hi!
            The formula returns an error if your cell contains text instead of a number. Check your details. For numbers, the formula works correctly. You wrote about rounding for the first time.

            =IF(A1*B1<100,100,ROUNDUP(A1*B1,0))

            I hope you fully described the problem and received an answer to it.

            1. Thank you so much for your help. God bless you! ♥♥♥

            2. Hi Alex!

              Thank you for the response. The formula you gave works correctly but is there any way that the problem 3 results will be Zero (0) using the same formula?

              =IF(A1*B1<100,100,ROUNDUP(A1*B1,0))

              Ex: ​(VALUE) x (0.005) = RESULT
              1. If I change the VALUE to 10,275 x 0.005 = 100
              2. If I change the VALUE to 75,420 x 0.005 = 378
              3. If I change the VALUE to 0 x 0.005 = 0

              Pls help. Thank you.

            3. OMG, im so sorry there is a problem again and I hope you still help me. Can please solve the problem 3 below using the same formula you gave.

              =IF(A1*B1<100,100,ROUNDUP(A1*B1,0))

              Ex: ​(VALUE) x (0.005) = RESULT
              1. If I change the VALUE to 10,275 x 0.005 = 100
              2. If I change the VALUE to 75,420 x 0.005 = 378
              3. If I change the VALUE to 0 x 0.005 = 0

              Thank you..

            4. OMG finally it wooorks! Thank you so much for your big help. ♥

  9. If the value is exactly ##.50 I need to round to the nearest even number.

    10.49=10
    10.50=10
    10.51=11

    11.49=11
    11.50=12
    11.51=12

    this is scientific rounding rules how can this be accomplished

      1. Nice, thanks Alexander. The thing I'm struggling with is having it round at the hundredth (0.01) position like this when I have the weight at a thousandth. Your formula clearly solves the question raised by R Knight, but how would I modify it for my scenario? I am struggling getting the decimal rounded.

        1. Hello!
          Try multiplying the original number by 100 and then dividing the result by 100. If this is not what you need, write examples of rounding so that I can understand you better.

          =IF(A1*100-INT(A1*100)=0.5,INT(A1*100)+MOD(INT(A1*100),2),ROUND(A1*100,0))/100

          1. I actually played around a bit and found a simple formula to use that works perfectly:

            =IF(ISEVEN(A1*100),ROUNDDOWN(A1,2),ROUNDUP(A1,2))

            1. Sorry, I truncated the formula and left off the beginning... Silly Me :) It should read...

              =IF(B2*100-INT(B2*100)=0.5,IF(ISEVEN(B2*100),ROUNDDOWN(B2,2),ROUNDUP(B2,2)),ROUND(B2,2))

  10. So I have come to the conclusion i need to use the standard ROUND programming here. I am pulling a dollar amount from a different page in the program and can not seem to figure out where ROUND would go. Current text ='Estimate Sheet'!G47. I have tried =ROUND('Estimate Sheet'!G47) and it will not allow it.

    1. Hello!
      If I understand correctly, in the ROUND function you use the text in which the cell address is written.
      You can use this formula:

      =ROUND(INDIRECT("Sheet1!C1"),0)

      or

      =ROUND(INDIRECT(A1),0)
      A1 contains "Sheet1!C1".
      If this is not what you wanted, please describe the problem in more detail.

  11. Hello,

    Im trying to round firgures but im not able to remove the currency symbol.
    e.g:£4,560.06.

    Please help

  12. Hi, Thank you for a very helpful tutorial! I did look but don't seem to see an already answered question to my problem.
    I found the right round up formula for me which is to round up to a closest 5 or 10 you provided =ROUND(C1,-1).

    But the cell I need to apply it to is pulling a value from another sheet :
    ='UK £ Trade '!B2*1.5

    so I can't seem to make it work the two together.

    Would really appreciate your help on this.

    Thank you.

      1. Thank you! Much appreciated. Working perfectly now :)

  13. Hi,
    I'll make a scale that has max 30 kgs with resolution 5 grams. So, the grams units will only display 0 or 5. For example, the reading and displaying would be as follows:

    5.003 --> 5.000
    11.004 --> 11.000
    8.005 --> 8.005
    2.006 --> 2.010
    -0.002 --> 0.000
    -0.007 --> -0.010

    Is it possible provided with built in function or I've to make it with "if".

    Thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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