ROUNDDOWN function in Excel is mainly used to reflect a lower number based on specific digits or decimal places. Financial modelers and analysts, CFOs, and other business professionals need numbers to be rounded down in Excel to not only reflect conservative balances but to be able to work with significant values. Rounded down values of earnings, expenses, revenues, costs, etc. are reliable approximate values indicating the day to day operations of a business.

In this short article, we will discuss what’s a ROUNDDOWN function in Excel, how to use Excel’s ROUNDDOWN formula with examples and briefly point out the difference between other ROUND functions.

What’s Down with ROUNDDOWN Function in Excel?

The ROUNDDOWN function in Excel is 1 of the 3 functions under ROUND Functions under Math/Trigonometry formulas available in Excel to help you round numbers.

When you type The ROUNDDOWN function in Excel a description box appears:

Conventional math rule dictates that a number is rounded either up or down to a specific number of digits based on the number to the right of the rounding digit. The rounding digit is considered as the least significant digit and gets changed depending on the number following it. Similar to what the ROUND Function in Excel does. However, the ROUNDDOWN function in Excel does not follow these fundamental rules. Similar to the ROUNDUP function, Excel’s ROUNDDOWN Formula has its own separate rule.

Using the ROUNDDOWN function, just as its name implies, a number is rounded down in Excel to a lower approximate of the original number. Using Excel’s ROUNDDOWN Formula, the rounding digit regardless if the number following it is either 1,2,3,4,5,6,78, or 9, will always be rounded down towards zero or the nearest 10s,100s, etc.

Simply, the ROUNDDOWN function in Excel always rounds down towards zero. The ROUNDDOWN function in Excel does the opposite of the ROUNDUP function which rounds away from zero.

The Excel ROUNDDOWN Formula :

Where:

Number – A required argument that represents the number that has to be rounded. You can use numbers (positive + and negative – signs), reference cells, and formulas.

Num_digits – A required argument that represents the number of digits to round the number to. You can reference the number itself or a reference cell.

• If the Num_digits is greater than zero, the number rounds DOWN in Excel towards the RIGHT of the decimal point- tenths, hundredths, thousandths, etc.  (See cell D2  and D3 in the Excel table below)

• If the Num_digits is zero, the number rounds DOWN in Excel towards its nearest integer or whole number.  (See cell D4)

• If the Num_digits is less than zero, the number rounds DOWN in Excel towards the LEFT of the decimal point- ones, tens, hundreds, etc. (See cell D5 and D6)

It is important to understand that the ROUNDDOWN function in Excel can be used for numbers with positive ( + ) or negative ( – ) signs. Assume a business suffered a loss of $1,216.759. Using the ROUNDDOWN function in Excel, notice how the loss is lower depending on the Num_digits  argument used. Keep in mind that the Excel’s ROUNDDOWN Formula always rounds towards zero.

Tip: When you type Excel’s ROUNDDOWN Formula, enter the shortcut CTRL + A (Arguments) to open the Insert Function Dialog Box for more detailed instruction to help you input the arguments:

How to Round Down in Excel with ROUNDDOWN Function

The ROUNDDOWN function in Excel, like other ROUND functions, is simple and flexible to use. When you want a number rounded down in Excel, you can use not only numbers but cell references, other Excel functions (e.g. SUM Function, IF Statements, etc.) and formulas.

Assume a US Tech firm has the table below for its European business segment’s revenue.

You can use the ROUNDDOWN function in Excel to round down the US$ converted revenue balances of each segment revenue.

You can incorporate formulas (add, multiply, divide, etc.) in the Numbers argument of Excel’s ROUNDDOWN formula. For example in the US$ conversion of the Consumer Electronics segment, the Numbers argument multiplies € revenue (cell B2) by the FX rate (cell C2). Then rounds down the result to 1 decimal place which gives us revenue in US$ currency of $10.6.

ROUNDDOWN function in Excel can also be combined with other Excel functions.

You can combine the SUM Function with the ROUNDDOWN function in Excel to round down the Total EUR and Total USD revenue balances of all segments to its nearest whole number.

Using Excel’s ROUNDDOWN formula =ROUNDDOWN(SUM(B2:B5),0) results in €38m. This formula has a 2-step process:

1. Sums all Revenue in EUR (B2:B5) which results in €38.793  then
2. Rounds down the total non-rounded revenue of €38.793 to its nearest whole number; €38.

We will apply the same concept to the USD converted total revenue.

Using Excel’s ROUNDDOWN formula =ROUNDDOWN(SUM(E2:E5),0) results in $43. This formula has a 2-step process:

1. Sums all Revenue in USD (E2:E5) which results in $43.320 then
2. Rounds down the total non-rounded revenue of $43.320 to its nearest whole number; $43

Excel’s ROUNDDOWN formula can also be combined with IF Statements. Assume that the Semiconductor segment suffered a loss of €-8.612, you want to round down in Excel segments that are profitable only (revenue above zero) otherwise do nothing. How to round down in Excel in this scenario?

Excel’s ROUNDDOWN formula =ROUNDDOWN(IF(D5>0,D5,0),0) results in a rounded $ revenue balance of $0m under the Semiconductor segment. The formula undergoes a two-step process:

1. The IF function IF(D5>0,D5,0) identifies if the revenue is above or below zero. If the revenue is above zero, it will return the segment’s revenue balance otherwise it will be ignored (zero) then
2. The segment’s non-zero revenue balance will be rounded down to its nearest whole number.

The resulting segment revenues are $10, $6, $16 and zero respectively.

Key Items on How to Round Down in Excel with ROUNDDOWN Function

• The ROUNDDOWN function in Excel always rounds a number down towards zero and does not follow the conventional Math rules on rounding numbers.
• The Numbers argument in Excel’s ROUNDDOWN formula can work not only with numbers but formulas, functions, and cell references.
• If you want to round numbers using conventional Math rules, use the ROUND function in Excel
• If you want to do the opposite of the ROUNDDOWN function in Excel and round UP numbers, use ROUNDUP function
• The ROUNDDOWN function in Excel works for numbers with either positive ( + ) or negative ( – ) signs and can round either to the left or right of the decimal point.