Using a function as one of the arguments in a formula that uses a function is called nesting, and we’ll refer to that function as a nested function. For example, by nesting the AVERAGE and SUM function in the arguments of the IF function, the following formula sums a set of numbers (G2:G5) only if the average of another set of numbers (F2:F5) is greater than 50. Otherwise, it returns 0.
The AVERAGE and SUM functions are nested within the IF function.
You can nest up to 64 levels of functions in a formula.
-
Click the cell in which you want to enter the formula.
-
To start the formula with the function, click Insert Function
Excel inserts the equal sign (=) for you.
-
In the Or select a category box, select All.
If you are familiar with the function categories, you can also select a category.
If you're not sure which function to use, you can type a question that describes what you want to do in the Search for a function box (for example, "add numbers" returns the SUM function).
-
To enter another function as an argument, enter the function in the argument box that you want.
The parts of the formula displayed in the Function Arguments dialog box reflect the function that you selected in the previous step.
If you clicked IF, the Function arguments dialog box displays the arguments for the IF function. To nest another function, you can enter it into the argument box. For example, you could enter SUM(G2:G5) in the Value_if_true box of the IF function.
-
Enter any additional arguments that are needed to complete your formula.
Instead of typing cell references, you can also select the cells that you want to reference. Click
Tip: For more information about the function and its arguments, click Help on this function.
-
After you complete the arguments for the formula, click OK.
-
Click the cell in which you want to enter the formula.
-
To start the formula with the function, click Insert Function
-
In the Insert Function dialog box, in the Pick a category box, select All.
If you are familiar with the function categories, you can also select a category.
-
To enter another function as an argument, enter the function in the argument box in the Formula Builder or directly into the cell.
-
Enter any additional arguments that are needed to complete your formula.
-
After you complete the arguments for the formula, press ENTER.
Examples
The following shows an example of using nested IF functions to assign a letter grade to a numeric test score.
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
|
Score |
||
|---|---|---|
|
45 |
||
|
90 |
||
|
78 |
||
|
Formula |
Description |
Result |
|
'=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) |
Uses nested IF conditions to assign a letter grade to the score in cell A2. |
=IF(A2>89,"A",IF(A2>79,"B",IF(A2>69,"C",IF(A2>59,"D","F")))) |
|
'=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) |
Uses nested IF conditions to assign a letter grade to the score in cell A3. |
=IF(A3>89,"A",IF(A3>79,"B",IF(A3>69,"C",IF(A3>59,"D","F")))) |
|
'=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) |
Uses nested IF conditions to assign a letter grade to the score in cell A4. |
=IF(A4>89,"A",IF(A4>79,"B",IF(A4>69,"C",IF(A4>59,"D","F")))) |
Tips:
-
To learn more about formulas in general see Overview of formulas.
-
For a list of available functions, see Excel functions (alphabetical) or Excel functions (by category).
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
