# Nested Formula in Excel (All You Need to Know)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn about nested formula in Excel and their application with different functions. You will get to know about the use of the most common functions such as AVERAGE, ROUND, SUM, IF, AND, OR, TODAY, INDEX, MATCH functions, and so on as nested functions.

The nested formula contains one or more functions as parameters or arguments within another function. It is mostly used for complex analysis.

## Examples Nested Formula in Excel

### 1. Nested Formula with AVERAGE and ROUND Functions

We will nest the AVERAGE function with the ROUND function to get a rounded average of daily income.

• Insert the formula on cell C14.
`=ROUND(AVERAGE(C5:C12),0)`

### 2. Nested AVERAGE with MAX and MIN Functions

If you want to measure the average for maximum and minimum values from your dataset, you need to nest AVERAGE with MAX or MIN functions separately. The following formulas are used respectively on cells C14 and C15 for maximum and minimum average.

The formula for cell C14:

`=AVERAGE(MAX(C5:C12),MAX(D5:D12))`

The formula for cell C15:

`=AVERAGE(MIN(C5:C12),MIN(D5:D12))`

### 3. Nested Formula with IF Function

#### 3.1 Nested IF with SUM Function

If you want to set up conditions of the IF function based on the sum value, you need to apply a nested formula of IF and SUM functions.

`=IF(SUM(C5:D5)>40000,"Excellent","Need Improvement")`

Read More: Use Nested IF and SUM Formula

#### 3.2 Nested IF with AVERAGE Function

We can assess several average values in Excel.

• Apply the formula based on the nested IF function with the AVERAGE function on cell E5.
`=IF(AVERAGE(C5:D5)>22000,"Excellent","Need Improvement")`
• Press the Enter button and drag the Fill Handle icon.

#### 3.3 IF with AVERAGE and SUM Functions

If you want to find out sum values with the average conditions, you need to next AVERAGE and SUM functions with the IF function.

`=IF(AVERAGE(C5:C12)>12,SUM(C5:C12))`

#### 3.4 Nested IF and AND Functions

To apply multiple conditions for a range of data, you can nest the IF and AND functions,

`=IF(AND(C5>=2000,D5>=20000),\$C\$16,IF(AND(C5>=10000,D5>=10000),\$C\$15,""))`

#### 3.5 Nested IF and OR Functions

To choose any one option from a range, you have to apply a formula with a combination of IF and OR functions,

`=IF(OR(C5>=20000,D5>=20000),\$C\$16,IF(OR(C5>=10000,D5>=10000),\$C\$15,""))`

#### 3.6 Nested IF, AND, and OR Functions

To show either one condition or another from the list, you have to apply the IF, AND, and OR functions together,

`=IF(AND(OR(C5=\$C\$14,C5=\$C\$15),D5>\$C\$16),"Done","N/A")`

#### 3.7 Nested Multiple IF Functions

When you want to set a number of conditions with comments in a dataset, you can nest multiple IF functions in a formula,

`=IF(C5>20000,"Excellent", IF(C5>15000,"Good", IF(C5>1000,"Need Improvement")))`

#### 3.8 Conditional Formatting With Nested IF Conditions

You can use a nested IF formula with Conditional Formatting to highlight cells.

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

• Insert the following formula in the image below marked 2.

`=IF(\$B5="TV",IF(\$C>20,TRUE,FALSE),FALSE)`

• Then customize formatting, and finally click OK.

Our selected cells are highlighted in the dataset.

### 4. Nested INT, YEARFRAC, and TODAY Functions

To measure time or year till today, you can use nested INT, YEARFRAC and TODAY functions.

`=INT(YEARFRAC(C5,TODAY()))`

### 5. Nested Formula with INDEX and MATCH

If you find any value according to some category or criteria, you need to use a nested formula based on INDEX and MATCH functions.

`=INDEX(D5:D16,MATCH(1,(G4=B5:B16)*(G5=C5:C16),0))`

## Things to Remember

• Apply the formula carefully.
• Insert the cell reference properly.
• Design nested functions as per your requirements.
• Careful about closing the bracket after applying each function in a formula.

## Conclusion

We believe that after going through the article you will be an expert nested formula user in Excel. If you want to explore any nested formula in Excel in detail, you can also visit the knowledge hub section we have a number of related articles. For further queries, you are always welcome to comment below.

1. Why would I use a nested formula?

The nested formula is useful when you deal with complex conditions. By applying nested formulas, you can easily manipulate data and get the desired result.

2. How deep can I nest formulas in Excel?

Microsoft Excel allows you to nest formulas up to 64 levels deep. But using more nested formulas in a worksheet can make your dataset more complex and it will be difficult to understand sometimes. So make sure to add nested formulas as much as you can read and maintain easily.

3. Are there any limitations or considerations when using nested formulas?

When using nested formulas, itâ€™s essential to pay attention to the correct order of functions and closing parentheses. Make sure that each function is closed correctly and nested within the correct location. Itâ€™s also important to consider any dependencies or potential errors that may arise due to incorrect or mismatched formulas.

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

Yes, you can apply cell reference in the nested formula. Cell reference will help you to make your formula more dynamic and it is useful when you want to apply this nested formula for multiple cells.

## Nested Formula in Excel: Knowledge Hub

<< Go Back to Excel Formulas | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Annyca Tabassum

Annyca Tabassum holds a BSc degree in Geography and Environment from Shahjalal University of Science and Technology, Bangladesh. She has a deep passion for Excel. As an Excel & VBA Content Developer for ExcelDemy, she not only provides solutions to complex issues but also demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, she is interested in Data Analysis with MS Excel, SPSS, Python Web... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF