Nested functions in Excel refer to combining the operations of two or more functions in one cell.
In Excel 2003 you can have 7 levels of nested functions, while more recent versions allow you to nest up to 64 functions.
The Order for Solving Nested Functions in Excel
When evaluating nested functions, Excel executes the deepest, or innermost function, first and then work its way outward.
In the example below we will be nesting the SUM function inside the ROUND function.
Since the SUM function is inside the ROUND function, Excel will first add up the range of data listed in the SUM function before it rounds off the result to the appropriate number of decimal places.
Excel's ROUND and SUM Functions
The steps that will be followed in this example are:
- Add together the data in cells D1 to D3 using the SUM function
- Round off the above answer to 2 decimal places using the ROUND function
The SUM function provides a quick way to add numbers together in an Excel spreadsheet.
The syntax for the SUM function is:
=SUM ( Number1, Number2, ... Number255 )
Up to 255 numbers can be entered into the function.
The syntax for the ROUND function is:
=ROUND( Number, Num_digits )
Num_digits = the number of digits or the number of decimal places that the number is to be rounded to.
In the example below, the SUM function will be used as the Number argument in the ROUND function.
Example: Nesting Functions in Excel
- Enter the following data into cells D1 to D3: 4.351, 5.824, 9.17.
- Click on cell D4, the cell where the functions' result will be displayed.
- Click on the Formulas tab.
- Click on the Math & Trig option on the ribbon to open the Function drop down list.
- Scroll down through the list and click on the ROUND option to bring up the Function Arguments dialog box.
- Click on the Number line in the dialog box.
- Type sum (d1 : d3) to enter the SUM function as the Number argument of the ROUND function.
- Click on the Num_digits line in the dialog box.
- Type a 2 in this line in order to round the answer to the SUM function to 2 decimal places.
- Click OK.
- The answer 19.35 should appear in cell E1 since we have rounded off the sum of the data in cells D1 to D3 (19.345) to 2 decimal places.
- Click on cell E1 and you should see the nested function
= ROUND(SUM (D1 : D3), 2) in the formula bar.