Rounding Numerical Calculation Results In Microsoft Excel

So here we are, with calculations that give long decimal results.The value of PI is 3.14159265...god knows where it ends. But most of our work is done by just 3.14. This is called rounding. Let’s see how we can round the numbers in excel.

Let's say in cell A2 we have 92.45477919. To limit these decimal points you can go to Home Tab? Decrease Decimal and click it until you reach to your desired number of decimals.

1

Wouldn’t it be easy and time-saving if you can do it with a formula?

Excel provides three formulas for Rounding Numeric numbers:

ROUND (value, num of decimal digits) ROUNDUP(value, num of decimal digits) ROUNDDOWN (value, num of decimal digits)

Examples will make it easy.

ROUND Function in Excel

The round function simply limits decimal value. It takes two arguments. First the value (the number that needs to be rounded) and second the number of decimals.

For example here in Cell A2, I have a random decimal number generated by RAND() function and then multiplied by 100. Now in Cell B2 I want to have only 1 decimal digit, so I write:

=ROUND(A2,1)

You can see the result in A2 in the below image.

2

It shows only one digit.

Pro Note: if the second decimal digit was greater than or equal to 5 than ROUND function would increase the first decimal by 1. You can see it in Cell B4 in the above image.

ROUNDUP Function in Excel

Same as the ROUND function of Excel, the ROUNDUP function limits the decimal points shown but it always increases the last decimal digit by one.

This, too, takes two arguments. First the value (the number that needs to be rounded) and second the number of decimals.

Now in Cell C2, I want to have only 1 decimal digit but I want to increase it by one to compensate the leftover digits. So I write this formula in C2:

=ROUNDUP(A2,1)

3

You can see that the value is rounded (92.5) to one decimal and it is increased by one.

ROUNDDOWN Function in Excel

The ROUNDDOWN function just shows the last decimal digit as it is and makes all following decimal digits 0. In short, it prevents the rounding up of a number.

In Cell D2 I have written this formula:

=ROUNDOWN(A2,1)

You can see that 92.454779 is now 92.4. Similarly, all numbers in column D are shown as they depend on defined decimal digits.

4

Pro Note: This does not decrease the last digit.

Now you know how to harness control of these decimal digits to your desired number digits. I tried to explain ROUND functions in Excel in the most precise way. These Rounding functions are available in Excel 2016, 2013, 2010 and in older Excel versions.
Let me know if that would be helpful. If your problem is not solved then post it in the comments section.

Users are saying about us...

  1. I want to take my results for example, 213 and 212 and round up to 215 and round down to 210. I am trying to round up to "5" and down to "0". Does anyone know how to do this?

  2. "The way I normally do this, is by dividing by 5, rounding to the nearest whole number, and then multiplying back by 5:

    If the number you want to round in this way is in A1, then:

    =ROUND(A1/5,0)*5

    Does that solve your problem? "

  3. Hi all, I'm trying to round (to the nearest hundreth) the result of the sum of a range of numbers divided by another number.

    Here's what I've attempted so far but it's not working (currently results in: #VALUE):

    =ROUND(SUM(H7:V7/5280),2)

    Any help would be greatly appreciated.

  4. How do I use the roundup formula to a cell which already has a formula eg
    cell A1 value = 60
    cell A2 value = 8
    cell A3 has a value =A1/A2. This leaves me a value of 7.5
    I want to round off that value in cell A4. how would you write out the formula to achieve an 8 value

      • I have the same question, but I want to nest the Round function inside the resulting cell. not create a new "Rounding" cell that references the formula cell.
        In other words, I want the ROunding to be a part of the formula. So, in the result above, I want to round Cell A3 within itself.
        As a caveat, the formula is an IF statement. Is this even possible?

        • Yes Keith. It is possible. Just write the formula as =ROUNDUP(A1/A2). Basically you just need to encapsulate the formula that will return a fractional result with the ROUNDUP function. The result will automatcally rounded up to specified decimal points.

          • my question is similar, but i'm using multiplication: =(B3*.1250). when i try to use ROUNDUP such as =ROUNDUP(B3*.1250) the error "you've entered too few arguments for this function" is returned. what needs to change?

          • You need to tell the number of decimal points you need. the roundup function takes two arguments. roundup(number, decimal_numbers). So B3*.1250 will return a number. Now if you want a whole number, you want zero decimals. Hence use this formula =ROUNDUP(B3*.1250,0). If you want 1 decimal point after whole number use =ROUNDUP(B3*.1250,1) and so on.

  5. Hi
    I have a formula that is date related as per the below, this calculates monthly budget lines but if the month is short by a day - ie: Nov it changes the budget line as it is calculating a 'short month' 0.9666667 instead of 1
    do you know how I can round up the end formula whilst keeping the below

    =(DATEDIF([@[Activity Start Date2]],[@[Activity Finish Date3]],"d"))/30

  6. Hi,
    Excel gives different results when I decrease decimel and when I use round function.
    i.e
    5,055 --> decrease decimel --> 5,05
    5,055 --> round (or roundup) --> 5,06 (the correct one).

    is this due to some Setting issue? how can i fix it?

    thanks in advance

    • Hi Firat,
      I thing the actual number would be something 5.0546... something and the default visible number would be 5.055 (default rounding). And when you round this number it will return 5.055

  7. Hello,

    How would I get excel to continue to round up the highest fractions in a column until it hits a specific number then round down the remain.

    Example:

    C1: 9.43, C2: 9.78, C3: 8.6

    I want excel to round the highest fractions first until it equals 28. Then round the remain down.

    So since C3 is the highest it rounds to 9, c2 is the second it rounds to 10, c1 is not needed to round up as we have hit our total so it rounds down.

    Then I want to capture the fractions that it rounded up or down so they offset next month.

    Example carryover:

    C1=-.43, c2=-.22, c3=-.4

    Thank you,

    Craig

  8. Hello,

    How would I get excel to continue to round up the highest fractions in a column until it hits a specific number then round down the remain.

    Example:

    C1: 9.43, C2: 9.78, C3: 8.6

    I want excel to round the highest fractions first until it equals 28. Then round the remain down.

    So since C3 is the highest it rounds to 9, c2 is the second it rounds to 10, c1 is not needed to round up as we have hit our total so it rounds down.

    Then I want to capture the fractions that it rounded up or down so they offset next month.

    Example carryover:

    C1= .43, c2= -.22, c3= -.4

    Thank you,

    Craig

  9. Hi, after rounded 3 CELLS 20.8 as 21 I got sum for these 3 CELLS is 62 not 63 similarly 20.2 ronded to 20 and sum of three is 61 not 60 how can I fix the problem

    • Hi, If you use ROUND or ROUNDUP Functions to round up values, the sum of these values will 63. But if you are using increasing or decreasing decimal point method for rounding up values, the sum will be shown of original values instead of sum of round up values.

    • Hi, Anne
      if you want to round up the result to zero number of decimal points, use this formula.
      Use ROUNDUP((($H2/50)*0.15)*100,0).

Leave a Reply to Gary Cancel reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube