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.

In some situations when you don't need an exact answer, rounding is a useful skill to use. In plain English, to round a number is to eliminate the least significant digits, making it simpler but keeping close to the original value. In other words, rounding lets you get an approximate number with the desired level of accuracy.

In everyday life, rounding is commonly used to make numbers easier to estimate, communicate or work with. For instance, you can use rounding to make long decimal numbers shorter to report the results of complex calculations or round off currency values.

Many different ways of rounding exist, such as rounding to integer, rounding to a specified increment, rounding to simple fractions, and so on. And Microsoft Excel provides a handful of functions to handle different rounding types. Below, you will find a quick overview of the major round functions and well as formula examples that demonstrate how to use those functions on the real-life data in your worksheets.

Excel rounding by changing the cell format

If you want to round numbers solely for presentations purposes, then you can just change the number of displayed decimal places without changing the underlying value. The fastest way is to use the Increase Decimal or Decrease Decimal command on the Home tab in the Number group:

Change the number of displayed decimal places.

Or you can change the cell's format by performing these steps:

  1. Select the cell with the number(s) you want to round.
  2. Open the Format Cells dialog by pressing Ctrl + 1 or right click the cell(s) and choose Format Cells... from the context menu.
  3. In the Format Cells window, switch to either Number or Currency tab, and type the number of decimal places you want to display in the Decimal places box. A preview of the rounded number will immediately show up under Sample.
  4. Click the OK button to save the changes and close the Format Cells dialog. Rounding a number in Excel by changing the cell format

Important note! This method changes the display format without changing the actual value stored in a cell. If you refer to that cell in any formulas, the original non-round value will be used in all calculations.

Excel functions to round numbers

Unlike formatting options that change only the display value, Excel round functions alter the actual value in a cell.

Below you will find a list of functions specially designed for performing different types of rounding in Excel.

  • ROUND - round the number to the specified number of digits.
  • ROUNDUP - round the number upward to the specified number of digits.
  • ROUNDDOWN - round the number downward to the specified number of digits.
  • MROUND - rounds the number upward or downward to the specified multiple.
  • FLOOR - round the number down to the specified multiple.
  • CEILING - round the number up to the specified multiple.
  • INT - round the number down to the nearest integer.
  • TRUNC - truncate the number to a specified number of decimal places.
  • EVEN - round the number up to the nearest even integer.
  • ODD - round the number up to the nearest odd integer.

Excel ROUND function

ROUND is the major rounding function in Excel that rounds a numeric value to a specified number of digits.

Syntax: ROUND(number, num_digits)

Number - any real number you want to round. This can be a number, reference to a cell containing the number or a formula-driven value.

Num_digits - the number of digits to round the number to. You can supply a positive or negative value in this argument:

  • If num_digits is greater than 0, the number is rounded to the specified number of decimal places.

    For example =ROUND(15.55,1) rounds 15.55 to 15.6.

  • If num_digits is less than 0, all decimal places are removed and the number is rounded to the left of the decimal point (to the nearest ten, hundred, thousand, etc.).

    For example =ROUND(15.55,-1) rounds 15.55 to the nearest 10 and returns 20 as the result.

  • If num_digits equals 0, the number is rounded to the nearest integer (no decimal places).

    For example =ROUND(15.55,0) rounds 15.55 to 16.

The Excel ROUND function follows the general math rules for rounding, where the number to the right of the rounding digit determines whether the number is rounded upwards or downwards.

Rounding digit is the last significant digit retained once the number is rounded, and it gets changed depending on whether the digit that follows it is greater or less than 5:

  • If the digit to the right of the rounding digit is 0, 1, 2, 3, or 4, the rounding digit is not changed, and the number is said to be rounded down.
  • If the rounding digit is followed by 5, 6, 7, 8, or 9, the rounding digit is increased by one, and the number is rounded up.

The following screenshot demonstrates a few ROUND formula examples: Excel ROUND function - formula examples

Excel ROUNDUP function

The ROUNDUP function rounds the number upward (away from 0) to a specified number of digits.

Syntax: ROUNDUP(number, num_digits)

Number - the number to be rounded up.

Num_digits - the number of digits you want to round the number to. You can supply both positive and negative numbers in this argument, and it works like num_digits of the ROUND function discussed above, except that a number is always rounded upward. Excel ROUNDUP function

Excel ROUNDDOWN function

The ROUNDDOWN function in Excel does the opposite of what ROUNDUP does, i.e. rounds a number down, toward zero.

Syntax: ROUNDDOWN(number, num_digits)

Number - the number to be rounded down.

Num_digits - the number of digits you want to round the number to. It works like the num_digits argument of the ROUND function, except that a number is always rounded downward.

The following screenshot demonstrates the ROUNDDOWN function in action. Excel ROUNDDOWN function

Excel MROUND function

The MROUND function in Excel rounds a given number up or down to the specified multiple.

Syntax: MROUND(number, multiple)

Number - the value you want to round.

Multiple - the multiple to which you want to round the number.

For example, the below formula rounds 7 to the nearest multiple of 2 and returns 8 as the result:

=MROUND(7, 2)

Whether the last remaining digit is rounded up (away from 0) or down (towards 0) depends on the remainder from dividing the number argument by the multiple argument:

  • If the remainder is equal to or greater than half the value of the multiple argument, Excel MROUND rounds the last digit up.
  • If the remainder is less than half the value of the multiple argument, the last digit is rounded down.

The MROUND function comes in handy, say, for rounding prices to the nearest nickel (5 cents) or a dime (10 cents) to avoid dealing with pennies as change. Using the MROUND function in Excel

And, it is really indispensable when it comes to rounding times to a desired interval. For example, to round time to the nearest 5 or 10 minutes, just supply "0:05" or "0:10" for the multiple, like this:

=MROUND(A2,"0:05") or =MROUND(A2,"0:10") Rounding times to the desired intervall

Note. The MROUND function returns the #NUM! error when its arguments have different signs. For example, both of the formulas =MROUND(3, -2) and =MROUND(-5, 2) result in the NUM error.

Excel FLOOR function

The FLOOR function in Excel is used to round a given number down, to the nearest multiple of a specified significance.

Syntax: FLOOR(number, significance)

Number - the number you want to round.

Significance - the multiple to which you wish to round the number.

For example, =FLOOR(2.5, 2) rounds 2.5 down to the nearest multiple of 2, which is 2.

The Excel FLOOR function performs rounding based on the following rules:

  • If the number and significance arguments are positive, the number is rounded down, toward zero, as in rows 2 and 10 in the screenshot below.
  • If number is positive and significance is negative, the FLOOR function returns the #NUM error, as in row 4.
  • If number is negative and significance is positive, the value is rounded down, away from zero, as in row 6.
  • If number and significance are negative, the number is rounded up, toward zero, as in row 8.
  • If number is an exact multiple of the significance argument, no rounding takes place.
Using the FLOOR function in Excel

Note. In Excel 2003 & 2007, the number and significance arguments must have the same sign, either positive or negative, otherwise an error is returned. In newer Excel versions, the FLOOR function has been improved, so in Excel 2010, 2013 and 2016 it can handle a negative number and positive significance.

Excel CEILING function

The CEILING function in Excel rounds a given number up, to the nearest multiple of significance. It has the same syntax as the FLOOR function.

Syntax: CEILING(number, significance)

Number - the number you want to round.

Significance - the multiple to which you want to round the number.

For instance, the formula =CEILING(2.5, 2) rounds 2.5 up to the nearest multiple of 2, which is 4.

The Excel CEILING function works based on the rounding rules similar to FLOOR's, except that it generally rounds up, away from 0.

  • If both the number and significance arguments are positive, the number is rounded up, as in rows 2 and 10 in the screenshot below.
  • If number is positive and significance is negative, the CEILING function returns the #NUM error, as in row 4.
  • If number is negative and significance is positive, the value is rounded up, towards zero, as in row 6.
  • If number and significance are negative, the value is rounded down, as in row 8.
Using the CEILING function in Excel

Excel INT function

The INT function rounds a number down to the nearest integer.

Of all Excel round functions, INT is probably the easiest one to use, because it requires only one argument.

Syntax: INT(number)

Number - the number you want to round down to the nearest integer.

Positive numbers are rounded toward 0 while negative numbers are rounded away from 0. For example, =INT(1.5) returns 1 and =INT(-1.5) returns -2. Excel INT formula examples

Excel TRUNC function

The TRUNC function truncates a given numeric value to a specified number of decimal places.

Syntax: TRUNC(number, [num_digits])

  • Number - any real number that you want to truncate.
  • Num_digits - an optional argument that defines the precision of the truncation, i.e. the number of decimal places to truncate the number to. If omitted, the number is truncated to an integer (zero decimal places).

The Excel TRUNC function adheres to the following rounding rules:

  • If num_digits is positive, the number is truncated to the specified number of digits to the right of the decimal point.
  • If num_digits is negative, the number is truncated to the specified number of digits to the left of the decimal point.
  • If num_digits is 0 or omitted, it rounds the number to an integer. In this case, the TRUNC function works similarly to INT in that both return integers. However, TRUNC simply removes the factional part, while INT rounds a number down to the nearest integer.For example, =TRUNC(-2.4) returns -2, while =INT(-2.4) returns -3 because it's the lower integer. For more info, please see Rounding to integer example.

The following screenshot demonstrates the TRUNC function in action: Using the TRUNC function in Excel

Excel ODD and EVEN functions

These are two more functions provided by Excel for rounding a specified number to an integer.

ODD(number) rounds up to the nearest odd integer.

EVEN(number) rounds up to the nearest even integer.

  • In both functions, number is any real number that you want to round.
  • If number is non-numeric, the functions return the #VALUE! error.
  • If number is negative, it is rounded away from zero.

The ODD and EVEN functions may prove useful when you are processing items that come in pairs.

For example:

=ODD(2.4) returns 3

=ODD(-2.4) returns -3

=EVEN(2.4) returns 4

=EVEN(-2.4) returns -4 Excel ODD and EVEN functions

Using rounding formulas in Excel

As you see, there exist a variety of functions to round off numbers in Excel depending on the particular purpose. The following examples will hopefully give you some clues on how to use Excel rounding formulas based on your criteria.

How to round decimals in Excel to a certain number of places

Depending on the situation, you may want to round decimals up, down or based on math rounding rules:

ROUNDUP function - always rounds the decimal upward.

ROUNDDOWN function - always rounds the decimal downward.

ROUND - rounds up if the rounding digit is followed by the digit equal to or greater than 5, otherwise rounds down.

As an example, lets round the decimal numbers in column A to 2 decimal places. In the first argument (number), you enter a reference to the cell containing the number, and in the second argument (num_digits) you specify the number of decimal places you want to keep.

=ROUNDUP(A2, 2) - rounds the number in A2 upward, to two decimal places.

=ROUNDDOWN(A2, 2) - rounds the number in A2 downward, to two decimal places.

=ROUND(A2, 2) - rounds the number in A2 to 2 decimal places, upward or downward, depending on whether the 3rd decimal digit is greater or less than 5. Rounding formulas to round decimals to a specified number of places

Rounding negative numbers (ROUND, ROUNDDOWN, ROUNDUP)

When it comes to rounding a negative number, the results returned by the Excel round functions, may seem to flout logic :)

When the ROUNDUP function applies to negative numbers, they are said to be rounded up, even though they actually decrease in value. For example, the result of =ROUNDUP(-0.5, 0) is -1, as in row 7 in the screenshot below.

The ROUNDDOWN function is known to round numbers downward, though negative numbers may increase in value. For example, the formula =ROUNDDOWN(-0.5, 0) returns 0, as in row 8 in the screenshot below.

In fact, the rounding logic with regard to negative numbers is very simple. Whenever you use the ROUND, ROUNDDOWN or ROUNDUP function in Excel on a negative number, that number is first converted to its absolute value (without the minus sign), then the rounding operation occurs, and then the negative sign is re-applied to the result. Rounding negative numbers in Excel

How to extract a decimal part of a number

If you want to extract a fractional part of a decimal number, you can use the TRUNC function to truncate the decimal places and then subtract that integer from the original decimal number.

=A2 - TRUNC(A2,0)

As demonstrated in the screenshot below, the formula in column B works perfectly both for positive and negative numbers. However, if you'd rather get an absolute value (decimal part without the minus sign), then wrap the formula in the ABS function:

=ABS(A2 - TRUNC(A2,0)) Extracting a fractional part of a decimal number in Excel

How to round a decimal to an integer in Excel

As is the case with rounding to a certain number of decimal places, there is a handful of functions for rounding a fractional number to an integer.

ROUNDUP

To round up to nearest integer, use an Excel ROUNDUP formula with num_digits set to 0. For example =ROUNDUP(5.5, 0) rounds decimal 5.5 to 6.

INT or ROUNDDOW

To round down to nearest whole number, use either INT or ROUNDDOW with num_digits set to 0. For example both of the following formulas round 5.5 to 5:

=ROUNDOWN(5.5, 0)

=INT(5.5)

For negative decimals, however, the INT and ROUNDDOWN functions yield different results - INT rounds negative decimals away from 0, while ROUNDDOWN toward 0:

=ROUNDOWN(-5.5, 0) returns -5.

=INT(-5.5) returns -6.

TRUNC

To remove the factional part without changing the integer part, use the TRUNC formula with the second argument (num_digits) omitted or set to 0. For example, =TRUNC(5.5) truncates the decimal part (.5) and returns the integer part (5).

ODD or EVEN

To round a decimal up to the nearest odd integer, use the ODD function:

=ODD(5.5) returns 7.

To round a decimal up to the nearest even integer, use the EVEN function:

=EVEN(5.5) returns 6.

Formulas that round a decimal to an integer in Excel

Round to nearest 0.5

Microsoft Excel provides 3 functions that let you round numbers to nearest half, more precisely to the nearest multiple of 0.5. Which one to use depends on your rounding criteria.

  • To round a number down to nearest 0.5, use the FLOOR function, for example =FLOOR(A2, 0.5).
  • To round a number up to nearest 0.5, use the CEILING function, for example =CEILING(A2, 0.5).
  • To round a number up or down to nearest 0.5, use the MROUND function, e.g. =MROUND(A2, 0.5). Whether MROUND rounds the number up or down depends on the remainder from dividing the number by multiple. If the remainder is equal to or greater than half the value of multiple, the number is rounded upward, otherwise downward.
Excel functions to round a number to nearest 0.5

As you see, the MROUND function can be used for rounding positive values only, when applied to negative numbers, it returns the #NUM error.

Round to nearest 5 / 10 / 100 / 1000

Rounding to nearest five, ten, hundred or thousand is done in the same manner as rounding to 0.5 discussed in the previous example.

Round to nearest 5

Supposing that the number you want to round to closest 5 resides in cell A2, you can use on of the following formulas:

  • To round a number down to nearest 5:
    =FLOOR(A2, 5)
  • To round a number up to nearest 5:
    =CEILING(A2, 5)
  • To round a number up or down to nearest 5:
    =MROUND(A2, 5)
Excel functions to round a number to closest 5

Round to nearest 10

To round your numbers to nearest ten, supply 10 in the second argument of the rounding functions:

  • To round a number down to nearest 10:
    =FLOOR(A2, 10)
  • To round a number up to nearest 10:
    =CEILING(A2, 10)
  • To round a number up or down to nearest 10:
    =MROUND(A2, 10)
Excel functions to round a number to nearest 10

Round to nearest 100

Rounding to a hundred is done in the same way, except that you enter 100 in the second argument:

  • To round a number down to nearest 100:
    =FLOOR(A2, 100)
  • To round a number up to nearest 100:
    =CEILING(A2, 100)
  • To round a number up or down to nearest 100
    =MROUND(A2, 100)
Excel functions to round a number to nearest 100

Round to nearest 1000

To round a value in cell A2 to the nearest thousand, use of the following formulas:

  • To round a number down to nearest 1000:
    =FLOOR(A2, 1000)
  • To round a number up to nearest 1000:
    =CEILING(A2, 1000)
  • To round a number up or down to nearest 1000
    =MROUND(A2, 1000)
Excel functions to round a number to nearest 1000

The same techniques can be used for rounding numbers to other multiples. For example, you can round the prices to the nearest nickel (multiple of 0.05), lengths to the nearest inch (multiple of 1/12), or minutes to the nearest second (multiple of 1/60). Speaking of time, and do you know how to convert it to nearest hour or closest 5 or 10 minutes? If you don't, you will find the answers in the next section :)

Rounding time in Excel

There may be many situations when you need to round time values. And again, you can use different rounding functions depending on your purpose.

Example 1. How to round time to nearest hour in Excel

With times located in column A, you can use one of the following functions to round time to nearest hour:

  • To round time to closest hour (up or down) - MROUND or ROUND.

    =MROUND(A1,"1:00")

    =MROUND(A1, TIME(1,0,0))

    =ROUND(A1*24,0)/24

  • To round up time to nearest hour - ROUNDUP or CEILING.

    =CEILING(A1, "1:00")

    =CEILING(A1, TIME(1,0,0))

    =ROUNDUP(A1*24,0)/24

  • To round down time to nearest hour - ROUNDDOWN or FLOOR.

    =FLOOR(A1, "1:00")

    =FLOOR(A1, TIME(1,0,0))

    =ROUNDDOWN(A1*24,0)/24

In the ROUND, ROUNDUP and ROUNDDOWN formulas, you multiply the time value by 24 (number of hours in a day) to convert a serial number representing the time to hours. Then you use one of the rounding functions to round the decimal value to an integer, and then divide it by 24 to change the returned value back to the time format. Rounding time to nearest hour in Excel

If your timestamps include date values, then use the INT or TRUNC function to extract dates (in the internal Excel system, dates and times are stored as serial numbers, the integer part representing a date and fractional part representing time). And then, use the formulas described above but subtract the date value. For example:

=MROUND(A1,"1:00") - INT(A1)

=MROUND(A1,"1:00") - TRUNC(A1)

The following screenshot demonstrates other formulas: More formulas to round time to the nearest hour

Note. For the results to display correctly, remember to apply the Time format to your cells.

Example 2. Rounding time to nearest 5, 10, 15, etc. minutes

In case you want to round times in your Excel sheet to five or ten minutes, or to the closest quarter-hour, you can use the same rounding techniques as demonstrated above, but replace "1 hour" with the desired number of minutes in the formulas.

For example, to round the time in A1 to the closest 10 minutes, use one of the following functions:

  • To round time to closest 10 minutes (up or down):=MROUND(A1,"0:10")

    =MROUND(A1, TIME(0,10,0))

  • To round up time to nearest 10 min:=CEILING(A1, "0:10")

    =CEILING(A1, TIME(0,10,0))

  • To round down time to nearest 10 min:=FLOOR(A1, "0:10")

    =FLOOR(A1, TIME(0,10,0))

Rounding time to nearest 10 minutes

If you know (or can calculate) what portion of a day is constituted by a certain number of minutes, then you won't have problems using the ROUND, ROUNDUP and ROUNDOWN functions as well.

For example, knowing that 15 minutes, is 1/96th of a day, you can use one of the following formulas to round the time in A1 to the nearest quarter-hour.

  • To round time to closest 15 minutes (up or down): =ROUND(A1*96,0)/96
  • To round up time to nearest 15 min: =ROUNDUP(A1*96,0)/96
  • To round down time to nearest 15 min: =ROUNDDOWN(A1*96,0)/96

This is how you perform rounding in Excel. Hopefully, now you know how, among all those round functions, chose the one best suited for your needs.

Table of contents

364 comments

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

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

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

    • 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!

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

        • 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?

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

      • 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!

          • 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! ♥♥

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

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

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

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

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

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

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

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

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

        • 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

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

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

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

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

  7. Hello,

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

    Please help

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

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

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

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

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

          • Perfect! Thank you so much!

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

          • Thanks, much appreciated!

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

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

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

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

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

      • many thanks , it works !

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

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

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

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

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

      • Thank you so much for the clarification and assistance.

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

  18. Works perfectly!

    Thanks so much.

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

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

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

    • With a minimum rounded amount of $30,000.00

      • Thanks you so much Alexander!... You saved me so much time, this worked perfectly

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

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

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

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

    • Dear Elias,

      Thank you so very much for your inspiring and motivating comments! I really appreciate it.

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

      • 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)?

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

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

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

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

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

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

  28. Hello! Is it possible to round past the decimal place? For example, turn 1,200,000 into 1.2?

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

  29. If i have a quantity of 3.06 in B11 of excel and i have a unit cost of 536 in d11 and if i rounded up the 3.06 to 4 is there an equation that would then change the unit cost to the quantity of 4 automatically?

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

    • 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

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

  32. Hey, I have got these conditions rounding with weights that we have,

    .1 - .5 (should be rounded to .5)
    .6 - 1.09 (Should be rounded to 1)
    1.1- 1.59 (Should be rounded to 1.5)
    1.6 - 2.09 (Should be rounded to 2)

    I am familiar with the nested if but what about rounding these numbers like mentioned above.

  33. Can you help - I need the following time provisions on a summary of time work in minutes up to 100
    i.e 11,070.92 hours worked

    Less than 15 minutes - round to zero hours

    15 minutes to 45 minutes (inclusive) - round to 1/2 hour

    Greater than 45 minutes - round to hours

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

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

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

    • 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

      • 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

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

          • 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

  37. Hello Everyone,
    If I wish to round off all decimal numbers upto 0.4999 to 0.5 and all decimal numbers greater than or equal to 0.501 to 0.9999 to 1. i.e. if a decimal number is 17.1 then it should be converted to 17.5 and if a decimal number is 29.65 then it should be converted to 30. These numbers should be rounded so that we arrive at the correct calculations.

    How do I do it? Please help

  38. I have a weight in one column and in the other column I am wanting to multiply it by 65% AND then round it up to the nearest increment of 5 using one formula. Example: My weight is 125 and I want to know 65% of that weight which is 81.25. I would like to round it up to 85. Can i do all of this in one cell. I have this so far =R3*0.65 , but I don't know how to use another function in the same cell to use that function AND round up to the nearest 5. I would like all my weights to end in 5 or 0 ie ( 85,95,100 etc..) Can you help me?

  39. Hi , I am trying to write a formula for total hours in excel .
    4(hrs).22(Min) + 4(hrs).46(Min) = 8(Hrs).68(Min) (Answer should be 9 hrs 8 minutes.)
    7(hrs).30(Min) + 5(hrs).30(Mins)= 12(Hrs).60(Min) (Answer should be 13 hrs)
    Please help to write formula.

    • Hello!
      If you write 04:42:00 in cell A1, and write 04:46:00 in cell B1, then the formula =A1 + B1 gives 09:08:00. Explain in detail how you got your result and I will try to help you

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

  41. I'm trying to round 161-169 to 165 and 160 still remain 160 in one cell.
    Thanks to much.

    • 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

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

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

  43. Hi,
    I'm trying to write an equation in Excel that rounds using the following rules:
    1. If the extra digit is less than 5, drop the digit.
    2. If the extra digit is greater than 5, drop it and increase the previous digit by one.
    3. If the extra digit is five, then increase the previous digit by one if it is odd; otherwise do not change the previous digit.

    I feel like I'm close to a solution by using the IF(AND(Find line of commands but can't get it to come together properly. Could you please help?
    Thanks so much,
    John

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

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

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

    • 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

  46. excel formula in above 6 roundup 10 or below 5 roundown 0 if condition separate cell
    mulitple purpose

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

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

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

  49. Hi All,

    I need you help on how to make this number 0.282186949 to become 28.21.

    Thanks a lot in advance..

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

  50. thank you!! it helps so much

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