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 →

Table of contents

Comments page 2. Total comments: 373

  1. Hello can any one help ??

    Basically if X is equal to or less than 10 I would like it rounded to 0.1 if it is greater than 10 would like it rounded to the nearest 0.25

    I have tried MROUND i thin i am getting lost in the logic part

  2. I would like a formula which rounds off to the nearest hundred UP and DOWN, but AT LEAST x points away. For eg., given 43657 and x is 40, I want it to return 43600 and 43800 ( because though 43700 is the nearest 100, it is less than x=40 away ). I need this as part of an algo for excel for trading

  3. I want to use the actual truncated integer in a separate equation. For example, I want to use the truncated result of 74/A8 (14 is the A8 value) So I type =TRUNC(74/A8,0) and it gives me 5. But then I want to use the number 5 in another equation but it actually uses the untruncated value 5.285...
    How can I get it to use the 5 only?

    1. Hi! Use the ROUND or INT function to round the number to an integer. All the explanations are in the article above.
      Or tfy this formula:
      =TRUNC(74/A8)

  4. Can I have multiple equations in a cell that will round each equation before adding them all together?

    I would like to see all the math in 1 cell

    Formula that I am currently using:
    =SUM(375.48*15%)+(393.5*15%)+(287.24*15%)+(287.24*15%)

    However, I would also like each () to round to the nearest $5 in the same cell

    For instance (based on the above formula), it'd be 55 + 60 + 45 + 45 = 205 (205 is the # I would like displayed)

    Currently I have the rounding in a separate cell, but that only calculates correctly when you have 1 set of ()
    For instance, 56.322 + 59.025 + 43.086 + 43.086 rounded = 200

    1. Hi!
      Read carefully the paragraph in the article above: Round to nearest 5 / 10 / 100 / 1000
      Use rounding for each equation.

      CEILING(375.48*15%, 5) + CEILING(393.5*15%, 5) + CEILING(287.24*15%, 5) + CEILING(287.24*15%, 5)

  5. Hi! hope you can give insight on this:
    Excel formula to round up time in before 8:00 am up to 8:15 am to 8:00 am. thanks.

    1. Hello!
      Use the MINUTE function to extract the minutes from time and round them using the FLOOR function

      =TIME(HOUR(A1),FLOOR(MINUTE(A1),15),0)

  6. Hi

    If i have a value i.e 2300 and i want to divide that by 73 I get 27.397260. how can i use excel to round up the result so that anything below 27.300 rounds down to the full number 27 and anything above 27.301 rounds up to the closest full number so 28.

    many thanks

      1. Hi

        thanks for your reply.
        So currently i'm using this formula in the cell.

        =ROUNDUP(SUM(B2/73),0)

        for example if i enter 2500 it returns a value of 35. however the 2500/73 is 34.24657. i want the cell to rounddown also to display 34 because the real amount is lower than 34.3. so ideally i want the cell to perform 2 actions. first to multiply by 73 them if the figure is higher then .3 to roundup if lower than .3 to round down to the nearest full number. hope i have explained it better now

  7. I can quite easily round time to nearest 5 minutes, but if already exactly 5 minutes, it rounds to 10, 10 minutes to 15, etc. How do I stop this happening so that if already divisible by 5 is doesn't do this?

    1. Hi!
      If I understand your task correctly, try the following formula:

      =CEILING(A2, 5)

      Pay attention to the following paragraph of the article above - Round to nearest 5 / 10 / 100 / 1000. It covers your case completely.

  8. Hi All,

    I need to combine the Ceiling/Floor function with my manual percentage formula, i.e

    =(F7*15%)+F7

    how to combine it with ceiling or floor ?

  9. Hi All,

    I need you help on this 25.58 i want this number to conditionally round up to 26.18

    if the value of decimal is greater than .40 then round up the value and remaining decimal value show

    Thanks a lot in advance..

  10. Many thanks for the articles on excel help..

  11. If I need to round depending on the numbers how should I do this?

    I need a formula for this

    if 50 nearest 10

  12. Respected sir please me on the conversion on rounding values.
    For example :
    (value:10.70 is rounded to 11.
    If the value: 10.60 is rounded to 10)

    Mainly objective is if the value is greater than 0.7 should be rounded to 1 and if the value is less than 0.7 should be rounded to 0.

    1. Hello!
      To comply with normal rounding rules, decrease the number to be rounded by 0.2
      Please use the formula below:

      =ROUND(A1-0.2,0)

      1. Rate Round
        -1.69 -2.00
        -0.95 -1.00
        -1.50 -2.00
        0.02 0.00
        -3.79 -4.00
        -0.74 -1.00
        -0.66 -1.00
        -0.17 0.00

        I need to apply the same rule - if the decimal value is less than 0.7, rounddown or else round up. When applied formula =ROUND(A1-0.2,0), it seems not correct. Could you please look at -1.50, why is it round up to -2.00, but not round down to -1.0.

      2. Thank you very much sir for helping me 🙏🙏🙏💐💐💐

  13. Hello!

    I am trying to figure out a formula that rounds a number to the nearest 9. For example $1.52 should be $1.49 or $1.56 should be $1.59; $2.08 should be $2.09 or $2.02 should be $1.99

    1. Hi!
      Round to 1 decimal place and subtract 0.01
      If I got you right, the formula below will help you with your task:

      =ROUND(A1,1)-0.01

  14. Hi Alexander,
    I need your help to calculate this. When is more than 2-3 rules I can`t manage.

    Our work is starting from 7:00 - 16:00.
    I have 3 departments. Every department is going to break at different times.
    1 department: 1. 10:00 - 10:30 = 0:30 minutes
    2. 12:45 - 13:15 = 0.30 minutes
    2. department: 1. 10:30 - 11:00 = 0:30 minutes
    2. 13:15 - 13:45 = 0:30 minutes
    3. department: 1. 11:00 - 11:30 = 0:30 minutes
    2. 13:45 - 14:15 = 0:30 minutes

    Sometimes workers are leaving early. For example: 1.department = 12:15 (after first break). Or = 14:15 (after second break)
    I have to calculate how many hours he worked and convert result (only minutes) to decimal. First example: 7:00 - 12:15 = 5.15 - 0.30 = 4.45 = 4.75 (in decimals).
    Second example: 7:00 - 14:15 = 7:15 - 1 = 6:15 = 6.25 (in decimals).
    Is it possible with one formula to calculate or I have to make separate calculation and formula for every department.

    1. Hi Alexander, I am an intern and an assessment is given to me but days of research didn't give any result. So if you could help me I would be really greateful.

      So I have ''date and time values vs number'' table. These table is like 1 months of measurement in a day each hour. so table is like:

      cell 1 cell 2

      1.01.22 01:00 24
      1.01.22 02:00 23
      .
      .
      1.01.22 04:00 23
      1.01.22 05:00 25
      .
      .
      2.01.22 01:00 24
      2.01.22 02:00 25
      .
      .
      .
      .
      2.01.22 04:00 23
      2.01.22 05:00 25

      And it goes like this for 5 months. Hope that is clear.

      So what I have to do is: there will be a second table, for each day at a certain time like 04.37, and it should correspond a measurement value from the first table. the second table will be like:

      cell 1 cell 2
      01.01.22 04.37 ***
      02.01.22 05.31 ***
      03.01.22 04.01 ***

      I have to pick the *** values using the first table. for example for the first one, I want to round the 4.37 to 4.50 and take the 40 minutes before's measurement which is 04.10 and put it to cell2.

      With the same logic 05.31 should be round 5.30 and take the 40 minutes before's mesaurement from the first table that accordings to 04.50.

      But I couldn't figured out how to take the rounding and taking 40 minutes before's measurement.

      I used vlook up. it matches for example for 01.01.22 4.32 date, 01.01.22 04.00's measurement value. But I dont want that. I want 03.50's mesaurement at 01.01.22.

      So I hope that its clear.

      I would be really greateful if you could help. Sorry for my grammer mistakes I tried my best.

      1. Hi!
        Pay attention to the following paragraph of the article above - Rounding time in Excel. However, I don't think it's possible to use the same formula to round 4:37 to 4:50 and 5:31 to 5:30.

      1. Hi Alexander,
        Thank you for your answer and helping with formulas.
        This formula works HALF. It means calculate everything and decimals also. BUT, this break times 10:00 - 10:30 and 12:45 - 13:15 I don`t know how to put in formula. If I put just 0.30 minutes it calculates. I would like formula to obey to this specific time based break intervals.

          1. This time works like I want. Great and many thanks.

  15. Hello Alexander,
    I have this rules : 0 / 0,25 / 0,50 / 0,75 / 1
    This should be in one formula. Any number which I am going to enter has to obey to this rules. I try to use IF and INT, but I was able only to round down and up "till 0,25 :)

    For example :
    5,10 -- > 5,00
    5,15 -- > 5,25
    Until 0,12 should be round down to 5,00. After 0,13 round up 0,25 = 5,25

    5,30 -- > 5,25
    5,40 -- > 5,50
    Until 0,37 round down 5,25. After 0,38 round up 5,50

    5,60 -- > 5,50
    5,70 -- > 5,75
    Until 0,57 round down to 5,50. After 0,58 round up 5,75

    5,80 -- > 5,75
    5,90 -- > 6
    Until 0,87 round down to 5,75. After 0,88 round up 6.

    Best Regards

    1. Hello!
      You are using different rounding intervals, so the rounding functions cannot be applied. Use the INT function to get the fractional part of a number. Round it up using an IF function with multiple conditions.

      =INT(A1)+IF(A1-INT(A1) > 0.87,1,IF(A1-INT(A1) > 0.57,0.75,IF(A1-INT(A1) > 0.37,0.5,IF(A1-INT(A1) > 0.12,0.25,0))))

      I hope I answered your question.

      1. Great, it works like a charm. Thank you very much.

  16. Hi sir
    I need your help for round function, I don't want to round less than 0 to 0.60, and more than 0.60 and above data's to round

    thank you

    1. Hi!
      If you did not find the answer in the article above, then please explain in more detail, as your question is not clear.

      1. For example 0.30 should be converted to 0.30 and 0.45 = 0.45, 0.67=0.67, 1.23=1, 1.56=2, 2.30=2, 2.68=3, that is, as there should be decimal numbers less than 1 decimal, numbers with values ​​greater than 1 should work under the rounding formula.

  17. Hello,

    I am having a difficult time figuring out a solution for how my organization tracks time. The six minute rule is used, for instance if the time is 8:06am- the time is rounded down to 8:00am, and if the time is 8:07am it is rounded up to the next quarter. I have used mround for the nearest quarter hour. I have not found a method to create a rule that if the time is 8:06 rounddown to 8am and roundup from 8:07am to the next quarter hour. I would really appreciate some help or guidance on this.

  18. I need to create a system that choose between two parameters to round up or down the number depending on a condition. This is for scoring students with a decimal system, so I need to round the final score.

    What I exactly need is this: if the score is between x,01 and x,24 round down to x,0 / if the score is between x,25 and x,49 round up to x,5 / if the score is between x,51 and x,74 round down to x,5 / if the score is between x,75 and x,99 round up to x,0

    For example: if the score is between 7,01 and 7,24 round down to 7,0 / if the score is between 8,25 and 8,49 round up to 8,5, and so on.

    1. So ... You're looking for a function to round to nearest .5? You should be able to use
      =MROUND(A1,0.5)

      Remember to correct separator and decimal point to your own locale. Given your notation I'd assume you should be using
      =MROUND(A1;0,5)

    2. Hello!
      Use a nested IF function with the rounding functions described in this article above.

      =IF((A1-INT(A1)) < 0.25,ROUNDDOWN(A1,0),IF((A1-INT(A1)) < 0.5,CEILING(A1,0.5),IF((A1-INT(A1)) < 0.75,FLOOR(A1,0.5),ROUNDUP(A1,0))))

  19. How can we round to nearest number automatically +/- MROUND(A23, 0.0) leads to zero total value. I want that formula that i feed once and it calculate round up/down automatically whatever right.

    1. Hi!
      I don't really understand what result you want to get. Try using the ROUND function. See the article above for a detailed description.

  20. Hi, is there a way to round 1,521,387.99 to 152.14 (unit is 10k)? Thanks in advance

  21. hi
    2.01 to 2.49 to be 2.5
    2.51 to 2.99 to be 3

      1. No, she (he?)'s trying to round UP to nearest .5.

        srinivas, you're looking for
        =CEILING(A1,0.5)

  22. Can we use ABS & ROUND functions together in one cell ?

    I have few negative numbers with decimals in my data, I want to change it to positive number with no decimals. Could you please help with it

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

      =ROUND(ABS(A1),0)

      Hope this is what you need.

      1. Hi

        How do i set up a formula to round to the nearest nth multiple depending on the value thats in the cell?
        I work a lot of changing cashflows and I need them to round depending on the value in the cell. e.g how to round 526 449.80 to the nearest thousand multiple, or how to round 6 143 651.73 to the nearest whole number, if the significance of the ceiling and floor formulas is variable, how do I automate that?

        Regards

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

  24. Hi how to roundup 7.4 to 8 and round down to 7. Instead of default .5 Thanks

    1. Hi!
      Have you tried the ways described in this blog post? If ROUNDUP and ROUNDDOWN functions don’t work for you, then please describe your task in detail.

  25. I have something like this: ="Avg $: "&(SUM(B7:G7))/(SUM(B9:G9))
    How would I round this to the nearest penny?

    1. Hi!
      Have you tried the ways described in this blog post? Use ROUND function

      =”Avg $: “&ROUND((SUM(B7:G7))/(SUM(B9:G9)),2)

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

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

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

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

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

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

  32. Hi,

    Can somebody help me regarding this excel problem. If adding or multiplying numbers, I still want the result not to fall the minimum result number. The minimum result number should be 100.

    Example:
    10,275 x .005 = 100 (I know it should be 51.38 but I want the result to fill automatically 100)

    Please help me, I need it ASAP.

    Thank you!

      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.

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

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

  33. Hi

    I need in excel after decimal 0.01 to 0.09 it round off "0"
    & 0.1 to 0.5 = 0.5
    & 0.6 to 0.9=1

    Ex:
    CM FT I NEED
    92 3.018 3
    93 3.051 3
    94 3.083 3

    EX:2
    CM FT I NEED
    95 3.11 3.5
    96 3.14 3.5
    97 3.18 3.5

    CM FT I NEED
    107 3.510 3.5
    108 3.543 3.5
    109 3.576 3.5

    EX:3
    CM FT I NEED
    110 3.61 4
    111 3.64 4
    112 3.67 4

    Please help on this one.

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

      1. This is known as the odd/even rounding rule. It is standard weight rounding laid out by the US Department of Commerce in NIST Handbook 44 section 10.2

        "(d) When the figure next beyond the last figure to be retained is 5 and there are no figures, or only zeros, beyond
        this 5, the figure in the last place to be retained is to be left unchanged if it is even (0, 2, 4, 6, or 8) and is to
        be increased by 1 if it is odd (1, 3, 5, 7, or 9). This is the odd and even rule, and may be stated as follows:
        “If odd, then add.” Thus, rounding off to the first decimal place, 47.25 would become 47.2 and 47.15 would
        become 47.2. Also, rounded to the nearest thousand, 4500 would become 4000 and 1500 would
        become 2000."

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

  36. Hello,

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

    Please help

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

  38. Excellent article!

    Please could you help with adding an IF statement to an MROUND formula? I wish to subtract £0.01 if it rounds to a whole number for retail prices. I have this to round to the nearest £0.10

    =(MROUND(BQ4,0.1))

    Thanks in advance!

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

      1. Thanks for the reply. I have various prices generated by a formula - for instance:
        £2.23, £2.76, £3.09 and £3.97

        =(MROUND(BQ4,0.1)) rounds my prices up to the nearest 10 pence so I now have:
        £2.20, £2.80, £3.10 and £4

        I wish to end up with:
        £2.20, £2.80, £3.10 and £3.99

        If the price = a multiple of one whole pound, deduct one pence. In this case changing the £4 to £3.99 but leaving the other values unchanged.

        1. Hello!
          If I got you right, the formula below will help you with your task:

          =IF(INT(MROUND(B1,0.1))<>MROUND(B1,0.1),MROUND(B1,0.1),MROUND(B1,0.1)-0.01)

          I hope this will help, otherwise please do not hesitate to contact me anytime.

          1. Thanks, much appreciated!

        2. I would use this for all of your rounded prices:

          =IF(MROUND(BQ4,0.1)=MROUND(BQ4,1),MROUND(BQ4,0.1)-0.01, MROUND(BQ4,0.1)

          In words:

          - Logical statement: Checks if the price rounded to the nearest 10 pence is the same as the price rounded to the nearest pound.

          - If TRUE: Outputs the rounded number (Can be to the nearest 0.1 or 1. I chose 10p but both are the same for TRUE cases) and takes away 0.01 (or 1p).

          - If FALSE: It leaves the number rounded to the nearest 10p with no further alterations.

          1. Perfect! Thank you so much!

  39. hello, can you help me how to round off grades into a whole value
    e.g. 89.5

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

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

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

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

  44. I want to set a limit whereas 0.60 is the threshold if the decimal reaches 0.60 it will automatically count as 1 and reset into decimal. The decimal represent the minutes and the whole number represent the hours. I'm presenting my data as numbers instead of time value. Hope anyone can help

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice.
      I’ll try to guess and offer you the following formula:

      =IF((C1-INT(C1))>=0.6,1+INT(C1),C1)

  45. Hi, I'm trying to reduce a number by a % then MROUND it to the nearest 5.
    I'm having trouble figuring out the formula, would you have any pointers?

    =B5*(1-E5)&MROUND(F5,5)

      1. Thank you so much for the clarification and assistance.

  46. Hi!

    I need my values to round to the nearest 5, but I need 2.5 to round DOWN. So any values 2.5 and below would round down and any values 2.6 and above would round up. Problem is that the number before the decimal may change. For instance it may be 17.5 which would need to round down to 15 and 17.6 would need to round up to 20. 23 would round up to 25 but 22.5 would round down to 20.

    Thanks!

  47. Works perfectly!

    Thanks so much.

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

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

  50. I am working as an engineer
    I need to validate certain complex rules after implementing certain values or data. That shouldn't violate the rules. If it is violated the we will get a error text.
    Please 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 :)