Excel 2019 Bible

Chapter 15: Using Formulas for Financial Analysis  . . . . . . . . . . . . . . . . . . . . . . . . . . . . 

10

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

given number of significant digits.

FIGURE 10.11

Rounding numbers to one significant digit

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