Using a Nested Formula in Excel – 5 Examples

Nested formulas contain one or more functions as parameters or arguments within another function.

Download Practice Workbook

Download the workbook and practice.

Example 1 – Nested Formula with the AVERAGE and ROUND Functions

Nest the AVERAGE function and the ROUND function to get a rounded average of daily income.

• Enter the formula in C14.

Read More: Nested Formula with AVERAGE and ROUND Functions

Example 2 – Nested formula with the AVERAGE, MAX and MIN Functions

To measure the average of maximum and minimum values, nest the AVERAGE, the MAX or MIN functions.

The following formulas are used in C14 and C15 for maximum and minimum average.

In C14:

In C15:

Example 3 – Nested Formula with the IF Function

3.1 Nested IF with the SUM Function

To set conditions for the IF function based on the sum value, nest the IF and SUM functions:

Read More: Use Nested IF and SUM Formula

3.2 Nested IF with the AVERAGE Function

Assess several average values.

• Use the formula in E5.

• Press Enter and drag the Fill Handle.

3.3 IF with the AVERAGE and SUM Functions

To find sum values with average conditions, use the AVERAGE and SUM functions with the IF function:

3.4 Nested IF and AND Functions

To apply multiple conditions to a data range, nest the IF and the AND functions:

3.5 Nested IF and OR Functions

To choose an option from a range, use a combination of the IF and the OR functions:

3.6 Nested IF, AND, and OR Functions

To show either one  or another condition from the list, nest the IF, AND, and OR functions:

3.7 Nest Multiple IF Functions

To set a number of conditions with comments, nest multiple IF functions in a formula:

3.8 Conditional Formatting With Nested IF Conditions

Use a nested IF formula with Conditional Formatting to highlight cells.

• Select B5:C16.
• Go to Home >> Conditional Formatting >> New Rule.

• Enter the following formula in Format values where this formula is true:

• Customize formatting and click OK.

The selected cells are highlighted.

Example 4 – Nested INT, YEARFRAC, and TODAY Functions

To measure time or years till the current date, nest the INT, YEARFRAC and TODAY functions.

Example 5 – Nested Formula with the INDEX and the MATCH Functions

To find values based on criteria, nest the INDEX and MATCH functions.

Frequently Asked Questions

1. How deep can I nest formulas in Excel?

Microsoft Excel allows you to nest formulas up to 64 levels.

2. Can I use cell references within a nested formula?

Yes, you can apply cell reference in the nested formula. It helps make the formula more dynamic.

Nested Formula in Excel: Knowledge Hub

• Use Nested IF Function

<< Go Back to Excel Formulas | Learn Excel