Be able to use nested functions
📊 20 Spreadsheets – Nested Functions
What are Nested Functions?
Think of nested functions like Russian nesting dolls: one doll fits inside another. In a spreadsheet, one function is placed inside another to perform a more complex calculation. For example, you can put an IF inside a SUM to add only values that meet a condition.
🔧 How to Build a Nested Function
- Start with the inner function – the one that does the first calculation.
- Use the result of that function as an argument for the outer function.
- Close all parentheses in the correct order.
- Check the formula for errors by pressing Enter and reviewing the result.
📚 Example 1 – Sum with a Condition
| Cell | Formula | Result |
|---|---|---|
| C1 | =SUM(IF(A1:A10>5,B1:B10,0)) | $= \sum_{i=1}^{10} \text{IF}(A_i>5, B_i, 0)$ |
Here, IF checks each cell in A1:A10. If the value is greater than 5, it takes the corresponding B cell; otherwise, it uses 0. The SUM then adds all those results together.
🔀 Example 2 – Nested IF with AND/OR
| Cell | Formula | Result |
|---|---|---|
| D1 | =IF(AND(B1>10,C1<5),"Good","Bad") | $= \text{IF}(\text{AND}(B_1>10,C_1<5),\text{Good},\text{Bad})$ |
The AND function checks two conditions. If both are true, the outer IF returns “Good”; otherwise, it returns “Bad”.
📚 Example 3 – Nested VLOOKUP
| Cell | Formula | Result |
|---|---|---|
| E1 | =VLOOKUP(A1,Sheet2!A:B,2,FALSE) | $= \text{VLOOKUP}(A_1,\text{Sheet2}!A:B,2,\text{FALSE})$ |
| F1 | =VLOOKUP(E1,Sheet3!A:C,3,FALSE) | $= \text{VLOOKUP}(E_1,\text{Sheet3}!A:C,3,\text{FALSE})$ |
First, VLOOKUP finds a value in Sheet2. The result of that lookup (in E1) is then used as the lookup value in another VLOOKUP on Sheet3. This chaining is a classic example of nesting.
🎯 Examination Tips
- Always check the order of parentheses – the inner function must close before the outer one.
- Use F2 to edit a formula and see each part highlighted.
- When writing nested functions, write the innermost part first on paper to avoid confusion.
- Remember that IF can return numbers, text, or even another function.
- Practice with sample data sets; the more you experiment, the easier it becomes.
Revision
Log in to practice.