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.
Download Practice Workbook
You can download this workbook and practice by yourself.
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)
Read More:Â Nested Formula with AVERAGE and ROUND Functions
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.
Frequently Asked Questions
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
Get FREE Advanced Excel Exercises with Solutions!
