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
To get a rounded average of daily income:
- Enter the formula in C14.
=ROUND(AVERAGE(C5:C12),0)
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:
=AVERAGE(MAX(C5:C12),MAX(D5:D12))In C15:
=AVERAGE(MIN(C5:C12),MIN(D5:D12))
Example 3 – Nested Formula with the IF Function
3.1 Nested IF with the SUM Function
=IF(SUM(C5:D5)>40000,"Excellent","Need Improvement")
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.
=IF(AVERAGE(C5:D5)>22000,"Excellent","Need Improvement")- Press Enter and drag the Fill Handle.
3.3 IF with the AVERAGE and SUM Functions
To find sum values with average conditions:
=IF(AVERAGE(C5:C12)>12,SUM(C5:C12))
3.4 Nested 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 an option from a range:
=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 or another condition from the list:
=IF(AND(OR(C5=$C$14,C5=$C$15),D5>$C$16),"Done","N/A")
3.7 Nest Multiple IF Functions
To set a number of conditions with comments:
=IF(C5>20000,"Excellent", IF(C5>15000,"Good", IF(C5>1000,"Need Improvement")))
3.8 Conditional Formatting With Nested IF Conditions
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:
=IF($B5="TV",IF($C>20,TRUE,FALSE),FALSE)
- 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:
=INT(YEARFRAC(C5,TODAY()))
Example 5 – Nested Formula with the INDEX and the MATCH Functions
=INDEX(D5:D16,MATCH(1,(G4=B5:B16)*(G5=C5:C16),0))
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
<< Go Back to Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
