Learn how to Nest Functions.
Nesting Functions
A nested function is one that’s tucked inside another function. For example, we can nest a SUM function inside another function that requires a total in order for the rest of the function to work – the SUM is satisfying the number requirement, and allows the function to work.
Here’s a simple example of that, found on my Nested Demo 1 worksheet. We have a list of 4 purchased items, and using a formula that nests a function inside it, we can add in the pre-calculated sales tax and obtain a donation that’s the difference between the actual total and that amount rounded up to the next dollar. We calculate this with a SUM function nested inside a ROUND function. The ROUND function does the rounding, but requires a number to round up, and that’s where the SUM function comes into play.
So, as you can see in cell C11, the invoice total, with tax, is $729.91.
But to round that up to $730, which provides a 9 cent donation (which isn’t much, but if this was done at a store or business that has millions of customers, that would add up fast), we go to cell C12, and type:
=ROUND and press TAB to open the parentheses
Note that the ROUND function requires two arguments – a number and a number of digits.
The SUM function creates the NUMBER argument, and by typing a 0 (zero) for the second argument, we tell Excel to round up from 729.91, which is what the invoice total would normally be, to $730.00 even – the difference to be donated to a charity designated by the seller and agreed to by the customer.
So the finished nested function is:
=ROUND(SUM(C4:C9),0)
Moving on to NESTED DEMO 2, I’m using the IF function and nesting a SUM inside it – to provide the number that’s compared to another number to answer a question.
So I type:
=IF(B2<SUM(C5:C6),0.15,0.1)
To calculate the commission. Now, I’m betting that’s confusing you, so don’t worry – we’ll break that down.
The functions – one nested inside the other – say:
“If the value in cell B2, which is the sales quota of $300,000, is less than the sum of the sales, then the sales rep gets a 15% commission. If not, he or she gets 10%”.
When we do this, note that the Commission is calculated automatically, because it’s set to take the result of our IF function (and the SUM nested inside it) and use it in the formula in cell C10.
The IF function’s arguments are a LOGICAL TEST, which is the comparison of cell B2 to the SUM of cells C5 through C6),and then when we run that test, we get one answer – the value if true, and if not, we get another – the value if false.
Let me do that again for Sales Rep 2 so you can watch me build the nested functions again.
=IF(B2<SUM(D5:D6),0.15,0.1)
And again, the Commission is automatically calculated, using the result in D8.
Nesting functions is only as complex as the functions themselves. The SUM nested inside the ROUND function is simple, because there are just 2 arguments.
The SUM nested inside an IF function is a bit more complex, but very powerful – it asks a question, and then based on the answer, we can calculate another value. Pretty snazzy!