Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel

ROUND, ROUNDUP, and ROUNDDOWN are all syntactically similar but produce different end results. Knowing which to use and when is extremely important for users. Being aware of the subtle differences between these 3 functions can help you get the results you want in Excel.

Why does Excel have 3 ROUND functions?

There are 3 distinct rounding functions in Microsoft Excel. Although all 3 have almost the same syntax, each function will produce slightly different results and Excel users should be aware of the differences between them.

The three rounding functions are ROUND, ROUNDUP, and ROUNDDOWN. All of them are very useful to help users format output from operations. Each function is used to limit the number of decimal places displayed in a number.

1. ROUND . function

The ROUND function is the most famous of the three. It has a relatively simple syntax:

=ROUND(number, number_digits)

The first argument is the number to be rounded off, and the second argument is the number of decimal places to which it must be limited. The function follows standard rounding rules and can round its input up or down.

The first argument in a function can be a static value, a reference to another cell, or another function. The second argument determines how many decimal places the function will round. It can be any integer.

By entering 0 in the place of the second argument, the function can round the first argument to an integer.

=ROUND(3.78, 0)

The above formula will return the number 4. The numbers in column B of the following spreadsheet are generated using the ROUND function on the numbers in column A, with 2 as the second argument.

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 1

2. ROUNDUP . function

The ROUNDUP function is the second of the ROUND functions. It is very similar to the standard rounding function. The syntax of ROUNDUP looks the same as that of ROUND:

=ROUNDUP(number, number_digits)

Functionally, the ROUNDUP function works almost exactly like ROUND. The main difference between these two functions is that ROUND can round the number in argument one up or down, while ROUNDUP only rounds up.

Just rounding up means that no matter what the value in the last decimal place of the number is rounded up, as long as it is not 0, it will be rounded up.

=ROUNDUP(0.0072, 3)

Even though the last decimal place is 2, the result will still round to 0.008. The only way for a number not to be rounded is if all the trailing digits after the decimal place are rounded to 0. So 0.0070 will still round to 0.007 in the example above.

In the above spreadsheet, the difference between ROUND and ROUNDUP can be clearly seen when comparing column B and column C.

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 2

3. ROUNDDOWN . function

Excel's ROUNDDOWN function is quite similar to the first two rounding functions. The ROUNDDOWN function uses the same syntax as both ROUND and ROUNDUP:

=ROUNDDOWN(number, number_digits)

Like the ROUNDUP function above, the main difference between ROUND and ROUNDDOWN is that ROUNDDOWN will always round its output down. Rounding down will occur regardless of the value of the remaining digits.

=ROUNDDOWN(0.0078, 3)

The output of the above function will be 0.007, even though the value of the next digit is 8. The result will always truncate the current number at the specified digit regardless of any trailing digits.

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 3

The difference between all 3 rounding functions can be easily seen in the above example. One important thing to note is the display in cell D2. If the last digit is 0, it will be hidden by default in the result. The last digit that is hidden can be changed by configuring Excel's custom formatting for cells to display a pre-specified number of digits.

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 4

Example of the difference between Excel's ROUND functions

There are a number of situations in which Excel's various rounding functions can be useful. Simple geometric calculations provide an interesting way to look at different rounding functions.

For example, consider the following spreadsheet used to calculate the volume of a spherical body of a given radius. To find the volume of the sphere, we will use the following formula:

volume = (4/3) π r^3

In this formula, r will be the radius of the sphere. For Pi, Excel provides a built-in PI function. Finally, the radius can be exponential 3 using Excel's POWER formula. The first argument will be the radius of the sphere. The second will be the exponent to which it is powered, 3.

You can enter this formula and a list of radii to calculate the volume of different spheres. The resulting formula will look like this:

=(4/3) * PI() * POWER(A2, 3)

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 5

Each result shows the total mass of the sphere in question, but due to the number of decimal places the output is difficult to read. The ROUND function can help achieve more understandable results.

The number of digits after the decimal can be limited by wrapping the above formula inside the ROUND function. The resulting formula will look something like this:

=ROUND((4/3) * PI() * POWER(A2, 3), 2)

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 6

Using the above formula in column C, the volume is rounded to 2 decimal places. Swapping ROUND for ROUNDUP and ROUNDDOWN will result in columns D and E:

=ROUNDUP((4/3) * PI() * POWER(A2, 3), 2)

=ROUNDDOWN((4/3) * PI() * POWER(A2, 3), 2)

Compare ROUND, ROUNDUP and ROUNDDOWN rounding functions in Excel Picture 7

The final result is truncated to 2 decimal places in each case and rounded up or down depending on the function used.

When using any of the ROUND functions to truncate the number of decimal places in a formula, the result will be rounded even when used in other calculations. Instead, when the format option is used to limit the number of decimal places, the actual result will be stored and any future calculations will use the full result.

The use of rounded numbers can reduce accuracy for complex calculations, so users should know which rounding method is most appropriate.

Jessica Tanner

Update 01 June 2023