The ROUND Function in excel is most useful when you are carrying out mathematical operations in your model. Division of numbers and multiplication of decimals are the foremost reasons to adopt the use of the ROUND Function in your model. Using the ROUND Function eliminates the need of manually going into cells to round to the nearest tenths, hundredth, ones or whichever you desire.In today’s post, we would walk you through all you need to know about the ROUND Function in excel. Let’s get right into it.
Syntax & Arguments
The Syntax of the ROUND Function is as follows:
=ROUND(number, num_digits)
The ROUND Function has 2 arguments namely “number” and “num_digits”. Let’s see what they represent below:
- number: number to round, could be hardcoded number, or derived from a function.
- num_digits: The place at which the number above should be rounded to. You specify by inputting a number from zero (0) and above. 0 instructs excel to round to ones or nearest whole number (i.e no decimal). 1 is rounding to the nearest tenths, 2 is rounding to the nearest hundredths, 3 for the nearest thousandths and so on.
Things to note
- The ROUND function rounds up when the last significant digit is greater than or equal to 5. This means that if the last significant digit is 5, 6, 7, 8 or 9, excel increases the number to the left by one and removes that last significant number. for example, “=ROUND(27.46,1)” becomes 27.5.
- The ROUND function rounds down when the last significant digit is less than 5. This means that if the last significant digit is 0, 1, 2, 3, 4, the number to the left of the last significant number remains unchanged whilst the last significant number is removed. For example, “=ROUND(27.43,1)” becomes 27.4.
- If the num_digit is zero (0), the number is rounded to the nearest integer. “=ROUND(5.7,0)” equal 6
- If the num_digits inputted is greater than zero (0), the number is rounded to the specified number of decimals. For example, “=ROUND(7.57, 1)” is rounded to 7.6. IF on the other hand the num_digit specified is less than zero (0), the ROUND function rounds to the left of the decimal point. For example, =ROUND(45.5, -1)” would be rounded up to 50.
- If the num_digit is zero (0), the number is rounded to the nearest integer (i.e decimal places are eliminated). For example =ROUND(25.4, 0) is rounded to 25.
ROUND Function Examples
Round to right of decimal
To use the ROUND function, all you need to do is to wrap the cell containing the value, or the value itself in a ROUND Function and then specify the num_digit as seen below.
=ROUND(17.357,2) // Rounds to 2 decimal places
=ROUND(B3,3) // Rounds to 3 decimal places
=ROUND(35.57254,4) // Rounds to 4 decimal places
Round to left of decimal
As discussed in the things to note section, a num digit of zero (0) would round to the nearest whole number whilst a negative num_digit would round to the left of the decimal.
=ROUND(B3,-1) // Rounds to nearest 10
=ROUND(1194.57,-2) // Rounds to nearest 100 (returns 1200)
=ROUND(B3,-3) // Rounds to nearest 1000
Using the ROUND Function in Financial Modeling
Yes, we are finally in this section, let’s see the practical use of the ROUND function in modeling. In our example, we have a very simple information provided about a project that we know was able to cover its initial outlay within the first year. However, what we need to know is how many months it took to payback.
To return the Payback period, we just divide the Initial Outlay by the Year 1 cashflow and then multiply by 12 months. The situation however is that after carrying out the operation, we may have decimals, but we want to return a whole number and then attach the text “Months” with the & Operator. Let’s see the formula below:
Now let’s look at what would be returned if we don’t use the ROUND Function:
As you can see above, our result without using the ROUND Function looks very messy, and this illustrates why the ROUND function is key for clean and neat presentation of your work in excel. With the ROUND Function, you can force excel to display numbers containing decimals in a uniform manner. You can choose not to display decimals by using zero (0) as the number for the num_digits argument or simply specify the number of decimals you wish to display.