In this post, you will learn how to round numbers in Excel. We will briefly talk about ROUND, ROUNDUP, ROUNDDOWN, CEILING and FLOOR functions in Excel.

### Rounding numbers using formulas –

Excel’s **ROUND** function is used to round a given number to a specified number of digits. The

ROUND function takes two arguments: the original value and number of digits to round to.

Passing 0 as the second argument tells Excel to remove all decimal places and round the

integer portion of the number based on the first decimal place. For instance, this formula

rounds to 94:

`=ROUND(94.45,0)`

Passing a 1 as the second argument tells Excel to round to one decimal based on the value

of the second decimal place. For example, this formula rounds to 94.5:

`=ROUND(94.45,1)`

You can also pass a negative number to the second argument, telling Excel to round based

on values to the left of the decimal point. The following formula, for example, returns 90:

`=ROUND(94.45,-1)`

You can force rounding in a particular direction using the** ROUNDUP** or **ROUNDDOWN**

function.

This ROUNDDOWN formula rounds 94.45 down to 94:

`=ROUNDDOWN(94.45,0)`

This ROUNDUP formula rounds 94.45 up to 95:

`=ROUNDUP(94.45,0`

### Rounding to the nearest penny –

In some industries, it is common practice to round a dollar amount to the nearest penny.

Figure below demonstrates how rounding a dollar amount up or down to the nearest penny

can affect the resulting number.

You can round to the nearest penny by using the CEILING or FLOOR function.

The CEILING function will round a number up to the nearest multiple of significance that

you pass to it. This comes in handy when you need to override the standard rounding pro-

tocol with your own business rules. For instance, you can force Excel to round 123.222 to

124 by using the CEILING function with a significance of 1.

`=CEILING(123.222,1)`

So, passing a .01 as the significance tells the CEILING function to round up to the nearest

penny.

If you wanted to round up to the nearest nickel, you can use .05 as the significance. For

instance, the following formula returns 123.15.

`=CEILING(123.11,.05)`

The FLOOR function works the same way except it forces a rounding down to the nearest

significance. The following example function rounds 123.19 down to the nearest nickel,

giving you 123.15 as the result:

`=FLOOR(123.19,.05`

### Rounding to significant digits –

In some financial reports, figures are presented in significant digits. The idea is that when

you’re dealing with numbers in the millions, there is no need to inundate a report with

superfluous numbers for the sake of showing precision down to the tens, hundreds, and

thousands place.

For instance, instead of showing the number 883,788, you could choose to round the num-

ber to one significant digit. This would mean displaying the same number as 900,000.

Rounding 883,788 to two significant digits would show the number as 880,000.

In essence, you’re deeming that a particular number’s place is significant enough to show.

The rest of the number can be replaced with zeros. This may feel like it could introduce

problems, but when dealing with large enough numbers, any number below a certain sig-

nificance would be inconsequential.

Figure below demonstrates how you can implement a formula that rounds numbers to a

given number of significant digits.

Let’s take a moment to see how this works.

Excel’s ROUND function is used to round a given number to a specified number of digits. The

ROUND function takes two arguments: the original value and number of digits to round to.

Passing a negative number to the second argument tells Excel to round based on significant

digits to the left of the decimal point. The following formula, for example, returns 9500:

`=ROUND(9489,-2)`

Changing the significant digits argument to –3 will return a value of 9000.

`=ROUND(9489,-3)`

This works great, except what if we have numbers on differing scales? That is to say, what

if some of our numbers are millions while others are hundreds of thousands? If we wanted

to display all of our numbers in one significant digit, we would need to build a different

ROUND function for each number to account for the differing significant digits argument

that we would need for each type of number.

To help solve this, we can replace our hard-coded significant digits argument with a for-

mula that calculates what that number should be.

Imagine that our number is –2330.45. We can use this formula as the significant digits

argument in our ROUND function.

`LEN(INT(ABS(-2330.45)))*-1+2`

This formula first wraps our number within the ABS function, effectively removing any

negative symbol that may exist. It then wraps that result in the INT function, stripping out any decimals that may exist. Finally, it wraps that result in the LEN function to get a

measure of how many digits are in the number without any decimals or negation symbols.

In the example, this part of the formula results in 4. If you take the number –2330.45 and

strip away the decimals and negative symbol, you have four digits left.

This number is then multiplied by –1 to make it a negative number, and it is then added to

the number of significant digits we are seeking. In this example, 4*-1+2 = –2.

Again, this formula will be used as the second argument for our ROUND function. Enter this

formula into Excel, and you’ll round this number to –2300 (two significant digits).

`=ROUND(-2330.45,LEN(INT(ABS(-2330.45)))*-1+2)`

You can then replace this formula with cell references that point to the source number and

cell that holds the number of desired significant digits.

`=ROUND(B5,LEN(INT(ABS(B5)))*-1+$E$3`