Welcome to Highline Excel
2016 video number 5. Hey, if you want to download
this Excel workbook, Busn218-Video05.xlsm, or you
want to download the PDF file, click on the link
below the video. And these are some very helpful
notes about array formulas. Now let's jump over
to our Excel workbook. Now, I actually want to go
back to the sheet Video 2 Reminder because we
want to start off by talking about the difference
between aggregate calculation and array calculation. We already went through one, two
examples back in video number 2 when we were reviewing
different formula types. Now, an aggregate
calculation is simple. We're taking a bunch
of different values, performing the
adding calculation, and delivering a single answer. That's the hallmark
of aggregate. Get a bunch of numbers,
get a single answer. Now, another synonym
for a bunch of numbers or a bunch of things, or more
technically, any time you have two or more things, you
can refer to it as an array. Now, this is not
an array formula. We are operating or calculating
on an array of answers, but when the formula evaluates,
it gives you a single answer, and that is distinguished
from an array formula. Now, this formula, if I hit F2--
we saw this in video number 2-- the final answer here
is a single answer. It's 791 because our goal was
to add the top three bowling scores for Tom. But if we F2 the inside
part sitting in the number 1 argument of SUM, that
involves an array operation, and here's how it works. That k argument is
expecting a single number, like 1, which means LARGE
would look through here and find the first biggest. Or we could give the k
argument a 2, for example, and it would go
through and it would deliver the second biggest. But the fact that we gave
it one, two, three items, we gave it an array
of items, that instructs the LARGE function
to deliver three items. And that is the hallmark of an
array calculation in an array formula. When we evaluate this
or calculate this with the F9 key, the
fact that it did not deliver a single answer, but it
delivered a array of answers, that means we're dealing
with an array formula. Control-Z. But the LARGE
did do an array operation, but then we put LARGE inside
of an aggregate function and it went ahead and calculated
the final answer, which is 791. Now let's go over to the
sheet Array Formulas. Now, let's start off by
defining what an formula is. We're going to define
it based on what it does and what it delivers. What does it do? It makes some calculation
on an array of items rather than a single item. In our LARGE example,
the array of items was the 1, 2, 3 rather than
a single item like 1 or 2. The second part to the
definition of an array formula is what it delivers. The calculation results
in an array of answers. For the LARGE, it spit out
simultaneously three answers-- the first largest, second
largest, and third largest. And we're going to call
that array of answers the resultant array. Number 3-- the
resultant array can be used inside a larger formula
or it can be the final answer. Our LARGE example we
just did-- of course, our resultant array went
into the SUM function, which was the larger formula. We will see examples
in these videos where we actually
deliver simultaneously the resultant array, the final
answer, into the spreadsheet into the cells. Finally, number
4-- array formulas require the special
keystroke Control-Shift-Enter in order to calculate correctly,
except in these situations. If we're using one of
these five functions-- and for us, we'll use
SUMPRODUCT in aggregate-- if we're using these
functions and we're making array formulas,
we do not have to use the special keystroke. Also, if the arrays
are array constants, then you don't have to
use Control-Shift-Enter. Now, I want to go
back over to our sheet and look at this definition
now that we know it, and look at the array formula
for our bowling example we did back in video number 2. I'm going to hit F2 here. Now, the array operation is
the fact that we gave 1, 2, 3-- simultaneously we gave
the k argument, three numbers. That forced the
LARGE function when it made the array calculation
to deliver a resultant array. So when I hit F9, that's
the resultant array. And in our case, we didn't
deliver the three values into the spreadsheet
cells, we kept it inside of a larger formula,
and our larger formula was the SUM function. Now I'm going to
Control-Z. Finally, we didn't have to use
Control-Shift-Enter, the special keystroke,
because our array was an array constant. All right, I'm going
to click Escape. Let's go back over to
the sheet because we're going to see a bunch
of amazing examples. Now, first, before
we do anything, here's our budget example
we've done a number of times in this class. Here's a formula. Oh yeah, we're
calculating cost of goods sold based on our expense
ratio for the month of January. We copy this formula
down and over. Now, before we make our second
array formula down here, I want to remind ourselves what
a non-array formula looks like. So I'm going to calculate cost
of goods sold for January. In this cell, equal sign--
hey, there's revenue, but notice it's a
single cell times, and there's our cost of
goods sold expense ratio. A single item with a math
operator, and there's another single item. I don't see any arrays here. So when I hit Enter,
it calculates perfectly as a non-array formula. Now, if we look at the total
expense formula up here, F2, notice the SUM function
is adding one, two, three, four cells. Now, most of the
time in budgeting, we want to see those. But sometimes you
don't want to see all of the individual detail. You just want the final number. So is there a way
in this cell right here to not use up four
cells, but in a single cell calculate all four expenses? You bet, and this is
actually the number 1 reason that we create array formulas. We want to make a bunch of
intermediate calculations or steps that we
normally do in the cells, and we want to do them
all in a single cell. All right, so you ready? Here's our second array formula. Equals sign, there is
revenue for January, a single cell, times--
that's a math operator, that will be our operation. And watch this. I'm going to highlight an
entire range or array of values. Now, any time you
have some operator and an array of values, you
are making an array formula. And because we have B4
times a whole range, B4 will actually be taken,
and it will multiply times 46, and it will be multiplied
times 19 and 15 and 4. This array operation will
deliver four answers. Now, let's go
ahead and highlight this and F9 to see this. And there's our resultant array. That is the array of
items or array of answers that that array
calculation delivers. Now, notice-- it's not
rounded, but 30,422-- boom. 12,756-- boom. 10,110, 2,643-- all
four values were calculated in a single cell. Now I'm going to Control-Z
because remember, up here we have
the ROUND function. We need to round
these, so no problem. I can wrap the ROUND function
around an array calculation, Tab, in the number argument. If I evaluate this, notice
that's the resultant array. That's the first
array operation we make, but now that a resultant
array rate will be dumped into ROUND--
Control-Z-- ROUND is not an aggregate function like
SUM, so it's definitely not going to, like,
add them, it's just going to round all four numbers. Comma 2-- because we're
rounding to the penny-- close parentheses, and if you
highlight this and evaluate it with F9, you bet
ROUND will round to the penny all four values. And notice-- one, two,
three, four values all calculated in a single
cell just like the values up here in the individual cells,
but now we're in a single cell. A more compact Excel solution. Control-Z. Now, what's our ultimate goal? We need to add them. Now, we're going to
use the SUM function. And I'm going to come to the
end and close parentheses. Now, when you're ready to enter
your formula into the cell, you have to ask yourself,
does this array formula require the special keystroke
Control-Shift-Enter? So we're going to look up here. If it is one of these
five functions right here, then we don't have to
use Control-Shift-Enter. So SUM function I
don't see in this list. And the second consideration is
are the arrays array constants? No, they're not, so this formula
requires the special keystroke Control-Shift-Enter. Now I'm going to hold
Control-Shift and then Enter, but wait a second--
I want to show you what happens if you don't
use the right keystroke for an array formula. I'm going to hit Enter. Let's just say I forgot
to do Control-Shift-Enter. That value error is
telling you, hey, you forgot to use the special
keystroke Control-Shift-Enter. So I'm going to F2. And now, you ready? Control-Shift and
Enter, and boom, just like that we get
exactly the same answer. Except for down
here, we did not have to use four individual cells
to get to our final answer. All four calculations are
done in a single cell. Now, this is an
array formula and we used Control-Shift-Enter,
so as soon as you Control-Shift-Enter,
you have to look up to the formula bar to
verify that the curly brackets were entered. Those curly brackets
are Excel telling you that it understood that you
wanted to do an array formula. Now, let me run
through that again because this is important. I put this into Edit mode. When I do
Control-Shift-Enter, that's me telling Excel that
this is an array formula. Those curly brackets
are Excel telling you it understood and calculated
this as an array formula. Now, those curly brackets
cannot be typed in. They're only put
in automatically when you use
Control-Shift-Enter. So the only real way to verify
if your formula calculated as an array formula if
that keyboard is required is to look up into
the formula bar and check for those
curly brackets. Now, the reason that checking
for those curly brackets is so important is because
yes, if we forget to do Control-Shift-Enter and I hit
Enter, that value error reminds me, so there's this reminder. But watch what happens if
instead of the array formula being below or above the array
of values in our formula, if the formula happens to be
next to the array of values-- and notice, when I
put it in Edit mode, the array formula is next
to the array of values. What does it do? It calculates through something
called implicit intersection the actual individual value as
if it was an individual cell. If I move this down
here, look at that. Because I'm next
to 15%, it gives me just that single answer. That's not what we want. But notice-- if I in this cell
right here just hit Enter, I might for a second be tricked,
thinking that I got an answer. But if I entered the formula
without Control-Shift-Enter, by always verifying up here that
the curly brackets are there, I'll never get into trouble. They're not there. That tells me I forgot to
use Control-Shift-Enter. So now, F2. If I use Control-Shift-Enter,
it doesn't matter if the array formula
is next to the array, below, or above. So you ready? Control-Shift and Enter. I am verifying. I see the curly
brackets, so I know that this calculated correctly. Now I'm going to
move this back here. Now, I actually
forgot one thing-- F2. I'm going to manually highlight
that range and hit F4. Now, what if I edited it,
which I just did here, and I forgot to do
Control-Shift-Enter? Well, of course, it's
not going to calculate. So even if you edit
it many, many times, you have to remember every
time-- Control-Shift and Enter. And look up to the formula bar. I see those curly
brackets-- boom. Now I can copy this
over to the side. I go to the last cell, F2, and
sure enough, it's looking good. I got the right cell references. Now, if I forget
to hit Enter here, I get the value error, right? So if you put it in Edit mode,
be sure to Control-Shift-Enter. If the formula has already
been entered, then, of course, if you hit F2 and check
to verify if the cell references are in
the right place, you can use Escape to revert
back to what was in the cell before you put the
cell in Edit mode, So I'm going to hit Escape. Now we got to talk about
one of the more amazing built-in functions in
Excel called SUMPRODUCT because wouldn't it be
nice if we didn't always have to remember
Control-Shift-Enter? Well, anytime you have
array calculations that you need to add, don't
use SUM, use SUMPRODUCT Now, SUMPRODUCT is
specifically programmed to perform the array
calculations and then add. Now let's come down here and
do the same, exact calculation again. I'm going to zoom in just
a little bit-- equals, and here's our January
revenue right here, times our four expense ratios. And I need to lock
it with the F4 key. If I evaluate this, hey, there's
our unrounded four expenses, so Control-Z. I need to
take that resultant array and put it inside of ROUND. I come to the end, round
it to the penny using a 2, close parentheses. If I F9, I can see clearly the
resultant array has one, two, three, four expenses. My goal is to add
them-- Control-Z. So instead of putting them
inside of the SUM function, I'm going to put it
inside of SUMPRODUCT. Now, normally SUMPRODUCT takes
array 1 times array 2 times array 3 and then adds them. And we'll see a great
example in just a moment of how to use multiple arrays. But any time you have a
series of array calculations that you need to add,
you just throw it into array 1 inside of
SUMPRODUCT, close parentheses, and watch this-- I hit Enter,
totally calculates perfect. And you look up to
the formula bar, there are no curly brackets. All right, let's copy it to the
side, go to the last cell, F2, and sure enough, we have exactly
the right cell references. Hit Escape. Now, in this
situation, we needed to calculate January,
February, and March, the actual total expenses. But what if our goal was
not the individual March, but we needed the overall total? Well, if I hit F2 here,
well, wait a second, this is adding up
each one of these, so that means if I somehow
in this cell down here could make all of these
calculations in a single cell, then I could accomplish my goal. It's no problem. Let's notice what we had to do
to get this rectangular range of expenses. I had to take, in essence,
all of the months times all of the expense ratios. If, in an array
formula, I simply take this range times
this range here, that means four cells
in four different rows times three cells in
three different columns. If I multiply them, I would get
exactly this rectangular range. So let's try our
next array formula. Cell B30. Our goal is to calculate
total overall expense in a single cell. Now, notice-- there's
our expense ratios and there are our
revenues for each month. Let's just try this--
equals, and I'm going to take, hey,
revenue for January to March in one,
two, three, columns, and I'm going to multiply times
expense ratios in one, two, three, four different rows. When I hit F9 to evaluate,
you've got to be kidding. If we look at this
resultant array, every single one
of these numbers, including this
last one of 2,624, are exactly the numbers
in this rectangular range all in a single cell. Now, we need to round them, so
Control-Z. I can simply wrap ROUND around that
resultant array, comma 2, close parentheses. And now when I evaluate
this, sure enough, every single one
of these numbers is exactly the
rectangular range. Control-Z. I need to add. I'm not using SUM. I know that SUMPRODUCT
is the perfect function for making an array
calculation then adding, all without having to
do Control-Shift-Enter. Close parentheses,
and when I hit Enter, there is the total overall
expenses in a single cell. If we look up here, sure enough
we got the same, exact answer. Now, remember-- the goal
of a single-cell formula is when we need the
total overall number, but we don't need any of
the individual calculations. That is quite amazing. Example number 3-- our goal is
to calculate overall net income in a single cell. Now, up here in
our complete table, there's the calculation
for net income. It's, of course, total
revenue minus total expenses. Well, we already just
calculated our total expenses, so I'm going to cheat down here. I'm actually going to copy this. Highlight, Control-C,
Escape, and now that part I can subtract
from total revenue. Well, total revenue is easy. Alt, equals, I
highlight three cells. This is not an array
formula this is just an aggregate calculation. That will calculate total
revenue minus Control-V-- there's our single-cell
formula for total expenses. There we go. When I hit Enter,
single-cell formula for net income-- exactly
the same as we got up here. So the benefit of
array formulas-- if you're after a
certain calculation that has many intermediate steps. But you don't need to see all
of the intermediate steps, you just want the final answer,
array formulas are amazing. Now, in these first three
examples of array formulas, we used the SUMPRODUCT
function, but we only used the first array. So in our next
example, we actually want to see how to use
these other arrays. Not only that, but notice--
we did multiplying, and the sizes or dimensions
of the arrays were different. This is one row
by three columns, this is four rows by one column. Not only that, but this
formula over here-- this wasn't even to arrays. It was just a single
cell times four items in four different rows. So in our next
example, we're going to see how to use SUMPRODUCT
to multiply arrays when they are the same dimension. Now, our goal is to
calculate total bank deposit, so in this cell here we
need our total deposit. And in our template, we
have the denominations $1, $5, $10, $20, and
$100, and every day we have a different
number of each bill. Today we have 16 $1's,
five $5's, 22 $10's, 43 $20's, and five $100's. Now, if we didn't know how
to do array formulas or use SUMPRODUCTS, here's
what we'd have to do. We'd say, $1 times
the number we have, 16, plus $5 times 5 plus
$10 bill times 22 plus $20 bill times 43 plus $100 times 5. Now, notice-- we have
two different types of math operation. We're either
multiplying or adding. Now, another way to look
that at this is this, and I'm going to hit Enter. We have a one-row by five-column
array times another one-row by five-column
array that we need to multiply each one of
the corresponding elements, and then add them. Notice, multiplying is PRODUCT,
and then adding is SUM. So instead of doing this
big, huge, long formula, we simply use SUMPRODUCT. Array 1, array 2-- we
can put as many arrays as long as they have
the same dimensions. And SUMPRODUCT will first
multiply and then add. So array 1-- one row
by five columns, comma array 2-- one row
by five columns. Close parenthesis, and there--
we get exactly the same answer. Instead of doing
this long formula, we want to use SUMPRODUCT. Now, it's very important that we
have the same size arrays here. If we accidentally
tried to do SUMPRODUCT, array 1-- this would
be a one by four, so we forget a cell, right? Comma, and then we
do a one by five, close parentheses-- it's
going to give us an error. Not only that, but in
our previous example, notice we were
multiplying arrays that were not the same size. If you tried to do this
inside of SUMPRODUCT, you'd get that same value error. But wait a second-- this
is inside of SUMPRODUCT. Yes, but when we're
multiplying, we're doing a direct math operator
on two different-sized arrays. If we have four numbers in
four different rows times three numbers in three
different columns, when we do this
direct multiplying with the multiplication
symbol, it'll give us that rectangular range. In this formula, we're just
using the array argument to avoid Control-Shift-Enter
and to get the SUM part to add. So when I hit Enter,
that works fine. When we're using the
arrays, those dimensions have to be the same. And by the way, they can be
arrays in two different rows with the same number of
columns, it could be, actually, in two
different columns with the same number
of rows, or it could be a rectangular range. In all three situations, the
two arrays or multiple arrays have to be the same dimensions. All right, Enter. Now we want to move on and talk
about function argument array operations. Example number 5-- well, we've
already done function argument array operations,
and we've done it in the k argument of
the LARGE function. We want to see here one, two,
and three examples of how to use a function
argument array operation. Now, here's a similar situation
to our bowling example. We just need to add the
three biggest sales. Well, we can use
the LARGE function. Array just means, hey, where
are all the numbers, comma, and that k argument. If I were to put a 3
here-- a single number-- then LARGE spits
out a single answer. But that's not what I want. I want the three biggest, so I'm
going to hard code the numbers, open curly bracket, 1 comma 2
comma 3, close curly bracket. So by putting an array
of numbers, 1 through 3, that instructs LARGE to
spit out three answers. Now I'm going to
close parentheses, and if we F9 this, you
can see sure enough, by putting an array
of items into k, it spits out the three biggest. Now Control-Z. Now, why this
is called a function argument array operation? Well, there's the
array, and it's sitting in the function argument k. So the array part is
the array constant, the function argument
is k, and the operation is when a LARGE spits
out the three answers. All right, now we
need to add this. So we have a choice--
we can use the SUM function or the SUMPRODUCT. Well, guess what? This is an array constant. And we know from our
two rules for whether or not we have to use
Control-Shift-Enter, we have to look at the function. And if it's an
array constant, we don't have to use
Control-Shift-Enter. Now, we will see an
exception to that later when we're entering array
formulas into multiple cells simultaneously, but
for the single cell, that array constant does not
require Control-Shift-Enter, so we can simply use
the SUM function to add. Now, of course it would
work with SUMPRODUCT also, but I'm going to use SUM here. When I hit Enter, there's
our three biggest sales. Now, this example here, we
need to add the three fastest. In many sporting events you
have some cumulative total. In our case, it's going to
be the three smallest values. And those added together
will be your final score. So we're not going to
use the LARGE function, we're going to use
the SMALL function. It's the same as LARGE. We give it the values we want
the SMALL to look through in the array argument. The k-- I want to make a
function argument array operation to get the three
smallest values, so I put my array constant. F9 will show us, sure enough, it
got the three fastest or three smallest times. Control-Z. That's
an array constant. It's not going to require
Control-Shift-Enter, so I'm simply going to
use the SUM function. And when I hit Enter, there is
the final score-- 3.23 minutes. All right, so two function
argument array operations. Let's look at example 6. This is an awesome example. Here we have sales teams, and
we need to add all of the sales from the sales column
for each sales team. Now, notice we have a
column with sales rep name. Now, one way to solve this
is to do this formula. And when I hit F2,
that is a long formula. All I did was I did
four different SUMIFs. IFs. I did one SUMIF for June,
one SUMIF for Sioux, and then the third
and fourth SUMIFs are for Poppi and Tyrone. Now, we can't simply put all of
this criteria into one SUMIFs because SUMIFs would try
to find a transaction that had June and Sioux
and Poppi and Tyrone, and there is no such thing. So simply we can do SUMIFs--
lots of them strung together. But there's a much
better way, and this is an example where our array
formula will help us to create a much smaller formula. We're still going to use SUMIFs. sum_range-- I
highlight the sales, Control-Shift, down
arrow, F4 to lock it because we are copying
this to the side, comma. And the criteria_range--
let's highlight the sales rep, Control-Shift,
down arrow, F4, comma, and the criteria. Now, normally, if we
have many conditions, we have to enter a range
and a criteria for each one, but we're going to do a function
argument array operation. In criteria 1, I'm simply going
to highlight June, Sioux, Poppi and Tyrone. And what this will
instruct SUMIFs to do-- remember, how many
items are in that argument? One, two, three, four, so it
will instruct SUMIFs to deliver four answers-- one for June,
one for Sioux, one for Poppi, and one for Tyrone. This is pretty cool. Close parentheses. And if I F9 to evaluate
it, it obeyed us perfectly. I gave the criteria argument
four different names, and so out pops from
SUMIFs one, two, three, four different sales totals. That 2,490 is for June. 1,932 for Sioux, and so. Now we need to add
them-- Control-Z. So we can choose between
SUM or SUMPRODUCT, but I'm looking at the
criteria 1 argument. There's our function
argument array operation, and that's not an
array constant. That's a range of cells. So if we put it inside
of the SUM function to add those four
numbers from SUMIFs, we'd have to use
Control-Shift-Enter. Instead, I'm using SUMPRODUCT. We'll simply use
the array argument, which can handle
that array operation without Control-Shift-Enter,
and then let the SUM part of SUMPRODUCT add. So I close parentheses at the
end, and when I Control-Enter, no curly brackets up there. It calculates perfectly. I drag it over to the side,
click in the last cell, F2, and look at that, it got all
the cell references correct. Enter. So either much shorter array
formula or much longer not array formula. I'm choosing the much
shorter array formula. Escape. Now let's go look at our
next example, example 7, and this will be another
function argument array operation. Now, we have an interesting
problem at hand-- we need to school or a
Myers-Briggs personality test in the human
resource department. So there's 32 questions
for this Myers-Briggs test, and every single question
gets either A or B. Here's a particular
person's responses, and here's the score table. We actually have to
look through here and count how many of
these the person got, and get a score
for E. Then we have to come over to the F column
and find how many of these items happened over here. Then get a score for F. Now, we can see 11B
if we go through. 11B-- that means we
would count that one. If I look for 15A,
there's a 15A. If I look for 19A, and
I can see that person did not answer 19A so we
do not count that one. So in essence, we need to
look through each one of these and get a count. Now, instead of doing it
manually or some other way, we're going to use the
COUNTIFs functions. We're going to say, hey,
COUNTIFs, here's your range, and this entire column is your
criteria as a function argument array operation. So let's try this. Equal sign, COUNTIFs. And now we want to give it
all of the possible answers for the letter E as a
relative cell reference into criteria_range, comma. And then criteria 1, that's
going to be our function argument array operation. I'm going to click on the
top cell-- Control-Shift, down arrow, F4. That means in criteria 1,
we just gave it 32 items. That means it will
instruct COUNTIFS to spit out 32 answers. So when I close
parentheses-- and by the way, the only possible answers are
a count of 1-- it found it-- or 0-- it didn't. So when I F9 to evaluate that,
you've got to be kidding me. Look at that-- all the 0's
mean all the way up to the 6B, which it found, all
the way up to the 11B. And so for this particular
letter E, 1, 2, 3-- 4 will be the score. Control-Z. We need to add those,
so what are we going to do? Well, this is a
function argument array operation without
an array constant, so I'm not going to use SUM
and Control-Shift-Enter, I'm going to use SUMPRODUCT. The array will handle
the array operation without Control-Shift-Enter,
and the SUM will add the resultant array. So when I close
parentheses, Control-Enter, that is the score for
E. When I copy it over to the side all the
way to T, hit F2, and there it is-- the cell
references are working. Now, let's look at. One other possibility for
scoring this Myers-Briggs. Now, notice up here the join
column was already created, and actually, look
at this-- someone had to create this extra
helper column here. And that's perfectly all
right, and in fact, it might be preferable. But what if you weren't
allowed to have this column, you were just allowed to
have question and answer? Could we make that happen? You bet we could, and I'm
going to copy and paste this in Edit mode, Control-C,
and paste it down here-- Control V. And notice, criteria
1-- we actually, in essence, join these two
columns, so we're going to write in our formula join
the question and answer column. So I'm going to click in
the top cell, Control-Shift, down arrow, F4. And then right here
in our formula, I'm going to use the join
operator to join two columns. So now I get my answer. Control-Shift, down
arrow, F4, and look at that-- that's our
first example of using a join operator not
to join a single item to another single item, but
we joined a whole column to a whole other column. So if I hit F9,
sure enough, you can see we've simulated
that whole helper column right in our formula. Control-Z, Control-Enter,
and copy it to the side. If we weren't allowed to have
the join column, no problem. We got rid of it. This formula is only looking
at the source raw data, in essence, and
our scoring table. This one is not going
to work because it's dependent on that helper column. Now I'm going to Control-Z
to leave that there. All right, let's go look at
our next example, example 9. Now, our goal here
is to calculate the average gross profit
for each sales rep. Now, we can't add any
extra columns here, so we're going to need to
create a single-cell solution. Now, here's the problem. First off, we don't have
gross profit calculated, so we're going to need in
our single-cell formula to take each one of the revenues
and subtract cost of goods sold to get gross profit. But then we're doing
it with, in essence, a condition or criteria, so I
really only need gross profit for Jack all the way down. So somehow in my formula,
I need to filter out all of the values
that don't match the condition or criteria. Now, I would like to use
a function like AVERAGEIFs because we're
calculating the average, but we've got to go
over to our PDFs. And on page 3, here is a list
of the sad functions in Excel that can never do
array calculations. For example, the lookup value
in VLOOKUP-- it can never do it. If you tried to do an array
calculation in this argument, it just gives you an error. And sure enough, range
and average_range and AVERAGEIF and
the average_if's criteria_range and
average_range-- they will not handle
array calculations. Now, I want you to notice
something about COUNTIFs because we just did a
function argument array operation in COUNTIFs
we did one in SUMIFs, but notice the criteria
1 argument is not listed for SUMIFs or COUNTIFs. Those can do function
argument array operations, but these criteria_ranges
and sum_ranges-- no matter how hard
you try, you're just never going to do it. You get an error message. Now, why they're programmed
this way, I don't know. All right, let's go
over and see a solution because we have to calculate
average gross profit based on this condition. Well, here's the good news-- you
can use any aggregate function like AVERAGE, MIN, MAX, and
other aggregate functions, and then inside the
number argument, we just put the IF function. The IF function will
help us filter out any values that don't match
our condition or criteria. So in the logical test
argument, we actually have to look at the
entire column of all the possible names,
F4 to lock it, and we have to ask the
question-- are any of you equal to the condition? In this case, Jack. So are any of you equal to Jack? If I highlight just that
little part and hit F9, the answer is a resultant
array of TRUEs and FALSEs. You could see that first TRUE
means yeah, it found Jack. All of these FALSEs--
it didn't find Jack. That TRUE right there
corresponds to that Jack. This resultant array
of TRUEs and FALSEs will help filter out all of
the values we don't want. Control-Z. Now, notice that's
a comparative operator. That's the first time we've seen
a comparative array operation. Now I comma, and value of TRUE--
remember, we want gross profit. Well, gross profit
is revenue, and I'm highlighting the entire column. Notice, it's the same
size as all of those TRUEs and FALSEs we got. F4 to lock it. I don't want just revenue,
so I subtract the entire cost of goods sold column. F4 to lock it. Now, if we highlight
just that part, that is a math array operation. And when I hit F9, there is
the entire resultant array of gross profits. Control-Z. Now, the
magic of the IF function right here comes from the
fact that we're not going to put anything for FALSE. When we leave FALSE
out, any time there's a FALSE in the logical
test, a FALSE value will be substituted
in for the number. So watch this--
close parentheses to leave that
FALSE argument out. When I highlight the
entire IF and hit F9, there are our filtered values. Only the values for gross
profit that correspond to Jack show up in our resultant array. Those FALSEs are our filter. And the reason that
FALSE will work as a filter is because aggregate
functions like AVERAGE and MAX and MIN all our programmed
to ignore FALSE. Now Control-Z. That is a bit of
IF function array formula magic to filter out values
we don't want. Close parentheses. Now, that function argument
right there, logical test, and the AVERAGE--
all of the functions here will not handle
those array calculations without the special keystroke
Control-Shift and Enter. I'm looking up to
the formula bar-- there's our curly brackets. Double click and send it down. Go to the last cell--
F2, and there we go. Now, if we needed to round,
we could go ahead and round the value IF TRUE, but we're not
going to worry about that here. We are just seeing
that if we need to make an aggregate calculation
with conditions or criteria and there's not a
built-in function, we just use the IF
function inside whatever aggregate function we need. Escape. Now we have two more examples
to end out this video, and these are relatively
straightforward. This is a special type
of function in Excel called an array function. Now, over in our
PDFs, there's a list of all the arrays functions. We're going to look at
the TRANSPOSE function and the FREQUENCY function. Now, here's our
same example we've been using quite a few times,
and as we learned in last video about references and
assumption tables, if we have a formula table
where the labels are listed vertically, but down in the
assumption table the labels and the numbers we need
for our formula are listed horizontally, we can't
use mixed cell references. So if we're given
a table like this, we want to be able
to transpose it. That means I need to take
everything in this row and list it in a column,
and then list everything in this row and this column. Any time you want to
transpose, you can actually-- watch this-- copy, and I'm
going to come over here, right click, and
there's TRANSPOSE. Now, this copies and
pastes as it transposes. Now, this is a
beautiful solution if you don't need to be
linked to the original data. If you need to be linked
to the original data, you don't copy, paste,
Special, Transpose, you use the TRANSPOSE function. Now, in order to use TRANSPOSE,
you actually have to count one, two, three-- hey,
look at that, there's four columns and two rows. That means when you
come over and highlight the range in advance,
you have to highlight four rows, two columns. Notice that means the
items in the columns are going to end up in the rows. Now, we have to
pre-highlight it because this is an example of
an array function that delivers multiple
values simultaneously to our highlighted range. Now, in the active cell right up
here, we type equals TRANSPOSE. And all we need is the range. Close parentheses. And now any time
you enter a formula into multiple cells
as an array, you have to hold
Control-Shift and Enter. And just like that we have
transposed and linked. Now, if this percentage
here changes, of course it changes here. And I already did the mixed
cell references up here. Everything is linked. Now, our last example is going
to be another very useful array function called FREQUENCY. Here are some sales values. And oftentimes times we need to
count how many of these sales values lie in each one
of these categories. Now, notice-- we're going to
help with these upper limits. So for the first
category, I need to count everything less
than or equal to 250, so that's the upper limit. Then when we get
to this category, because I've counted everything
less than or equal to 250, the 250 has to be the lower
limit, but it's not included. And then we have to have
everything less than or equal to 500. In math notation, that
means the sales number has to be greater than 250
and less than or equal to 500. Same thing for 750, 1,000. And to make sure that
we count everything, we're going to add one
extra category here. Even though this is
our last upper limit, we're going to add
one last category. This is just in case we're
going to count anything greater than our last upper limit. Now, this seems
complicated, right? But not when you use
the FREQUENCY function. This is just the
explicit categories that FREQUENCY function
creates automatically. Now, all you need
for FREQUENCY is the actual raw data and the
upper limits for each category. Here we go, this is
an array function so you have to highlight
the actual cells that the answers are going
to go into before you create your formula. Now, notice-- I
highlighted one more row than there are upper limits. That's because FREQUENCY always
creates this upper category to catch any accidental values. Now, most of the time
we're paying attention so we build enough upper
limits to count everything, but they wanted to make this
FREQUENCY function foolproof, so they always add
this extra category. So there it. You ready? Equals F-R, and I'm going
to do FREQUENCY, Tab. Data array-- those
are all the values you want to count, comma. And the bins_arrays are the
upper limits for each category. Remember, we're always
going to have one fewer bin array than number of
cells we highlighted. Now, we don't need
to lock these. And by the way, I didn't
even mention that. Transpose-- notice we didn't
lock any cell references. And the reason why is when
I entered this it gets entered as an array and
we'll actually go look at it. But watch this--
Control-Shift and Enter, and instantly it counts
all of these numbers when they're in each category. So for example, between greater
than 250 and less than or equal to 500, there are four. And if you go over
here and count, you can count them
on your fingers. And I sorted it to make it easy. So notice, there's the four. 500 is included in this
category because the equal sign is right there. Now, if you go look at each
one of these cells here, notice they don't move
as the array got entered. Every single time it
seems like they're locked, but they're not locked
with the F4 key. That's the way an
array function works. And in fact, I'm going
to scroll back up here. Notice, this formula
here that we entered-- in any particular
cell, you can see the array function
and the cell reference is the same in every cell. Now, one other thing
about array functions. If I come here and try to
delete it with the Delete key, it tells us that
that is not possible. And the reason why is because
that whole block of cells is considered a single unit. It is considered
part of the array. It says you cannot
change part of the array. The only way you would
ever get rid of it is if you actually
deleted everything. Now I'm going to Control-Z.
That's the same down here. And if you did something
like try to insert a row, it says no way because it cannot
move or mess with that array. Now, we mentioned earlier about
hard coding array constants. Now, here's an example
of array constants that were hard coded
in as the upper limits. Now, normally, with a
single-cell formula, we wouldn't have to use
Control-Shift-Enter, but any array formula that's
entered into multiple cells as an array has to use
Control-Shift-Enter. All right, that was an amazing
video all about array formulas. We saw some built-in
array functions-- FREQUENCY and TRANSPOSE. We saw how to do the IF function
inside of aggregate functions. We saw a number of function
argument array operations. This one was calculating
a score for Myers-Briggs. We saw this great
example where we were trying to add all the
sales for each sales team. We saw SMALL function
and the LARGE function with an array
constant that didn't need Control-Shift-Enter. We saw how to use SUMPRODUCT
to multiply and then add two arrays when they are
of the same dimension. We saw how to calculate
in a single cell net income, total
expenses, and this one, total expenses for each month. In each case, we made
a math array operation, and then housed the
array operations or array calculations inside
of SUMPRODUCT to avoid Control-Shift-Enter. All right, that's a lot of
fun with array formulas. Next video, we'll actually
talk about AND and OR logical tests or
criteria in formulas. All right, we'll
see you next video.
