The ROUNDDOWN function is the exact opposite of the ROUNDUP Function because whilst the ROUNDUP Function always rounds up decimal numbers regardless of whether below or above the number 5, the ROUND Down function ROUNDDOWN function always rounds down decimal numbers regardless of their distance from the number 5. For example, if we apply the function to a number, say 5.9 and we want the number rounded to the nearest whole number, the ROUNDDOWN Function would return 5. Like the example we gave in the ROUNDUP post, this could also be used for resource planning. For example, HR performing analysis on Manpower planning and taking an aggressive approach by rounding down their calculations to save cost in hiring one additional person when they can spread the workload for that point 6 (0.6) or point 3 (0.3).
In this post, we would take you through the ROUND function and how to use it in a financial model. Before that, let’s introduce you to the Syntax and arguments of the function.
Syntax & Argument
The syntax of the ROUNDDOWN Function is as follows:
=ROUNDDOWN(number, num_digits)
The ROUNDDOWN Function is a simple function with just 2 arguments
- number: This represents the number you want to round. It could be a hardcoded number or you could select a cell containing a number.
- num_digit: The place at which the user instructs excel to round to. number 0 means no decimal, i.e a whole number whilst number 1 means ROUNDDOWN to 1 decimal place and so on.
Usage Notes
Just few things to note when using this function, and they are all centered on the num_digit argument:
- Positive num_digit: If the num_digit is a positive number (greater than 0), the ROUNDDOWN Function rounds on the right side of the decimal place.
- Negative num_digit: If the num_digit is a negative number (less than 0), the ROUNDDOWN Function rounds on the left side of the decimal place.
- Zero num_digit: num_digit of 0 rounds down to the nearest whole number. i.e rounds on the left side of the decimal place.
Confused? Don’t worry, we would take some examples to illustrate:
ROUNDDOWN Examples
Round to right of decimal
To round down values to the right of the decimal, your num_digit must be greater than 0.
=ROUNDDOWN(3.429,2) // Returns 3.42
=ROUNDDOWN(8.6745,3) // Returns 8.674
Please note that this formula works perfectly if you select cells containing values instead of manually typing in the values into the formula. Let’s assume the three values above are in cells B3:B5, our formula would be:
=ROUNDDOWN(B4,2) // Returns 3.42
=ROUNDDOWN(B5,3) // Returns 8.674
Round to left of decimal
To round values to the left of decimal, your num_digit would be either 0 or a number less than 0
=ROUNDDOWN(45.1,-1) // Returns 40
=ROUNDDOWN(832,-2) // Returns 800
As stated in the previous section, this also works when you select the cells containing values instead of manually typing it in. Let’s assume we have the values in cells A1:A3
=ROUNDDOWN(A4,-1) // Returns 40
=ROUNDDOWN(A5,-2) // Returns 800
Integrating the ROUNDDOWN Function in Financial Modeling
Like we said in the Introduction, this could be very useful for manpower planning and in this section, we would illustrate with a simple example. In our example, we have the revenue and headcount for Year 20X1 which enables us to calculate the Revenue to Employee ratio, and we want to maintain this ratio in our forecast for the next 3 years. Of course, revenue may not remain same, so we need a simple working to calculate our headcount needs in line with our revenue forecast.
As you can see above, our headcount forecast for 20X2 to 20X4 shows fractions and there is nothing like a half human, so management needs to decide whether to round up or round down the headcount numbers in the forecast.
Let’s assume management decides to round down the headcount forecast; we only need to wrap our calculation in a ROUNDDOWN function to ensure we get whole number outputs:
Excel rounds down to the nearest whole number and we can proceed in using this number to calculate a high-level estimate of expected employee cost for the year.
