22.6 Rounding Numbers

In previous lessons, we’ve seen the use of formatting to display a number rounded to a desired decimal or whole number. You may wonder, then, what the difference is between rounding a number and simply formatting the number. Formatting simply adjusts how a number is displayed on a worksheet, whereas rounding actually changes the underlying number.

You may have utilized the ROUND function previously. It is quite useful. Did you know that there are actually several more rounding functions available in Excel? They are often used in the finance realm. See the chart below for a list of the available rounding functions.

In fact, we’ve had employers in the financial industry approach us looking for students who had a particular understanding of Excel that included use of dates and times, as well as these rounding functions, since they are so commonly used within the financial instruments built and utilized in the industry.

Let’s begin by making sure you are solid in your understanding of the ROUND function. It takes two parameters—a value and the number of decimals you should round to. Some of you probably know that you can use the ROUND function to round to the nearest 10s or 100s place. To do that, you use a negative number for the second parameter. For instance, the following function will round the number 12345 to 12300.

=ROUND(12345,-2)

Another common use case is to use the ROUND function to round to a fraction of a dollar. Very few of you today have likely been asked if you have two bits, meaning 25 cents. The word bit has a long history but generally refers to any coin of a low denomination. It was used in the early Americas as one-eighth of the value of a dollar, or 12.5 cents. Hence, two bits would have been equal to about 25 cents. Though you may not have heard the term in normal conversation, it was still used commonly just one or two generations ago.

The functions DOLLARFR and DOLLARDE are both used in calculations regarding fractions of a dollar. The function DOLLARFR is used to represent a dollar value as a fraction of a dollar, such as one-tenth of a dollar. Though this may not sound reasonable to you, it certainly does to those within the financial industry, where fractions of a dollar are often analyzed.

Let’s walk through an example. Say we have the value $3.25, and we want to see the remaining $0.25 as a fraction of a dollar—tenths of a dollar to be exact. Well, to do this we will use a formula like this:

=DOLLARFR($C$7,10)

This would display the value found in cell C7 ($3.25) as 9.25. You can think of this as .25 being 2.5 out of 10. In order to do the opposite of what we just did and convert a value representing a fraction of a dollar back into a full dollar, simply utilize the DOLLARDE function.

Jump into the video to walk through this and other examples of converting fractions of a dollar. As a particular motivation to do so, note that we can utilize these above functions in the realm of other measurements, even those commonly used within baseball statistics.

Truncate

The functions INT and TRUNC are also commonly used to round numbers. They are slightly different from each other, however. INT converts a number to the nearest integer by truncating the decimal values. It rounds numbers down first, and this is especially important to remember when using it on negative numbers. If the INT function was used to convert the value -14.2 to an integer, it would result in the value of -15. The TRUNC function allows you to specify the number of decimals you’d like to truncate to. You can also use negative numbers to round to the 1s, 10s, and 100s.

The functions EVEN and ODD can also be used to round a number to the nearest even or odd integer, but further away from zero. This is how the INT function also operates, as was seen in the above example where a value of -15 was the result of its use in a conversion of the value -14.2.

Significant Digits

Significant digits may be familiar to those who have recently taken chemistry classes. Closer to our field of work, however, are their use in financial statements. Many companies use significant digits in their display of financials in reports distributed throughout the year.

Let’s discuss an example of displaying the value 1,432,187 as a simpler number that could be easily digested by those reading a quarterly financial report, for example. In order to do this, we will use the ROUNDDOWN function. If the initial value is stored in cell C44, the formula will look something like this:

=ROUNDDOWN($C$44,2-LEN($C$44))

This formula specifies that we want to round to the hundreds of thousands, or 1,400,000. This is accomplished by telling the ROUNDDOWN formula to remove the detail of the last five digits of the original value. This requires us to pass a -5 to the ROUNDDOWN formula, and that is done by subtracting the length of the original value from the number 2.

These formulas are probably best understood in practice. So go ahead and follow along as we work through the examples in the video. Please make careful note of the difference in performance in positive and negative numbers.