Learn how to effectively use nested functions in Excel, a powerful tool that can enhance your data manipulation skills, particularly if you're in a career that heavily relies on Excel. Discover how to use SUM function inside a ROUND function, and SUM inside an IF function, to perform complex calculations, make decisions based on certain conditions, and increase your efficiency at work.
Key Insights
- Nested functions are functions that are tucked inside another function in Excel, such as a SUM function nested inside a ROUND function or an IF function.
- The SUM function can be used to satisfy the number requirement that allows the ROUND function to work. ROUND function requires a number to round up, which is provided by the SUM function.
- One use case of a nested function is to round up an invoice total to the nearest dollar, with the difference to be donated to a charity. This can be done using ROUND(SUM(C4:C9),0).
- An IF function can be used to compare a sales quota to the sum of sales, and based on the comparison, calculate a commission. This is achieved by nesting a SUM function inside an IF function.
- The IF function’s arguments include a logical test, which is the comparison of a particular cell to the SUM of certain cells. The test returns one answer if true, and another if false.
- Nesting functions can seem complex but they are very powerful and can significantly enhance data manipulation and decision-making capabilities in Excel.
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, they 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!