1. Computing

Nested Functions in Excel

Nested ROUND and SUM Functions Tutorial


Nested Functions in Excel

Nested Functions in Excel

© Ted French

Nested functions in Excel refer to combining the operations of two or more functions in one cell.

How nesting works is that one function is used as an argument for the first function.

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 use and syntax for each function is:

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 ROUND function is used when rounding integers and decimal numbers to make them easier to work with in a spreadsheet.

The syntax for the ROUND function is:

=ROUND( Number, Num_digits )

Number = the number to be rounded - it can also be a formula, another function, or a cell reference to a number or formula.

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

  1. Enter the following data into cells D1 to D3: 4.351, 5.824, 9.17.

  2. Click on cell D4, the cell where the functions' result will be displayed.

  3. Click on the Formulas tab.

  4. Click on the Math & Trig option on the ribbon to open the Function drop down list.

  5. Scroll down through the list and click on the ROUND option to bring up the Function Arguments dialog box.

  6. Click on the Number line in the dialog box.

  7. Type sum (d1 : d3) to enter the SUM function as the Number argument of the ROUND function.

  8. Click on the Num_digits line in the dialog box.

  9. Type a 2 in this line in order to round the answer to the SUM function to 2 decimal places.

  10. Click OK.

  11. 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.

  12. Click on cell E1 and you should see the nested function
    = ROUND(SUM (D1 : D3), 2) in the formula bar.

©2014 About.com. All rights reserved.