You've probably heard someone talk
about a nested formula in Excel,
and thought, "What on Earth?"
Well, this refers to a function that uses another function
as part of its calculation.
Let's say we want to calculate the average
of the two highest sales amount in each month.
So, to do this, we're going to use a combination
of the AVERAGE and the MAX function.
The AVERAGE function simply takes an average
of all of the values that you select,
and these values are identified
as number one, number two, and so on,
as you keep adding more values.
So, we need to calculate the MAX for number one,
so we type MAX, insert an open parentheses,
and notice how now this has switched to us working
within the MAX function.
For this first MAX calculation,
we want the highest value in January.
So, we'll select those values, close the parentheses,
and when we do that, notice how we switch back
to working in the AVERAGE function.
So, we'll insert a comma.
For the second value, type the MAX function again,
and now we select the February sales amounts.
Close the parentheses on that MAX function.
And we're back to the AVERAGE function,
so we need one more parentheses
to close out the average calculation. And press Enter.
So, what we've done here is nest the MAX function
inside the AVERAGE function.
Let's look at a more complex example.
Let's say we have a weird bonus structure
and plan to pay a bonus to any associate
whose average sales is within 15% of the value
that we just calculated.
So, we can use an IF statement with a nested formula
to figure this out.
So, let's type =IF, and we'll need the average
of the two months for the first employee,
and then we'll do greater than or equal to,
and type AVERAGE again.
MAX, and this is the same formula that we used last time.
And we'll select the January sales amount
and press F4 to make that static,
comma, the next MAX function,
and we'll select the second month,
and we'll close this parentheses,
then multiply by 0.85, close the parentheses, comma,
and then if the average of the associate
falls within this 15%, we'll say, "YES", comma,
and then if no, we'll say, "NO", and close that parentheses.
So, let's pause here for a minute before I press Enter.
The parentheses are probably the most confusing part
of nested formulas and usually where most
of the errors happen, including for me.
This is where I usually mess up.
So, it might be helpful to use the coloring as a guide.
Each formula's opening and closing parentheses
will be the same color with the main formula using black.
That means that your very first
and very last parentheses should always be black.
But notice how our final parentheses is purple,
so we need to fix this.
So, let's take a look.
So, starting with this first average,
I can tell that I'm missing a closing parentheses here,
so we'll add that.
Okay, and that gets us a little closer,
so now this is no longer purple.
This final one is red, and see how the purple moved down.
So, this means that, right now,
this closing purple is corresponding with the opening purple
for the AVERAGE function. And that's not quite right.
So, let's go a little bit deeper.
And here is where we're missing the parentheses
to correctly close out the AVERAGE function.
So, let's add that.
And now this makes a little bit more sense
where the parentheses is now purple.
Notice the opening and the closing one here.
Then, this, where we take the result of this average
of the two highest values and multiply by 0.85,
this is now encased in the red parentheses,
and now we see that the full IF formula
is now inside the black parentheses.
Press Enter. Now we can copy our formula down.
For more tutorials like this, click on these links. Thanks.