Welcome to Highline BI348 Class Video Number 8.
If you want to download this workbook, BI348 Chapter07start
or the finished file, click on the link below the video.
The first six videos in Chapter 7 were about model building.
But in this video on the sheet, Calculate Revenue,
we want to talk about a number of functions.
VLOOKUP, the straight LOOKUP function, SumProduct,
and CountIfs.
Here's our goal.
A company that sells rolls of fences
needs to calculate total revenue from a list
of customer transactions and a pricing table.
Now this data set, with number of rolls
of fences sold for each transaction,
provides the number.
But we're going to have to use the pricing table--
and notice pricing is done like this-- from 0 to 143
is $98 for a roll, from 144 to 98 is $187, etc.
So there's different pricing categories.
Now if we read the rest of this--
given the transactional data table boom,
boom, boom-- with the number of rolls
of fences sold for each transaction and the pricing
table right here, calculate the total revenue
and count the number of transactions that fell
into each pricing category.
We're going to see how to do this two ways.
One way is to add an extra column
to get the price from the table over into our data set
and then calculate total revenue.
So it would be called two helper columns.
Then it's easy.
We just use the Sum function to add.
We're going to do that.
Then we're going to see how to do the same exact calculation
without any helper columns.
So the first one is straightforward.
Let's go ahead.
And I'm going to select that field name
and point to the fill handle and click and drag.
I'm immediately going to point to the smart tag
and say, Fill Formatting Only.
Now I'm going to type Price, tab.
Then Revenue, Enter.
I'm going to have a formula here.
So I'm going to do what I've been doing all along.
I'm going to add some border and some green.
Now, how do we do this?
Well, guess what?
If we were doing this by hand, we'd look up the 540,
find the category.
We'd see that it's between 288 and 577,
and then we'd bring this 168 back to the cell.
168.
We'd have to then do that for each one of these.
Now, we're going to use the VLOOKUP function, which
all of you know how to do from prerequisites.
But I will remind you here.
That 540 is not going to look through both columns.
The fact that we have a From Units
and To Units is only for our convenience looking
at it, because the way VLOOKUP works is it
only is going to look something up
in the first column of our sorted table, 0, 144, 288, 578.
And here's how it works.
VLOOKUP will say, hey, VLOOKUP look at that 540.
And VLOOKUP will race down until it bumps into the first value
bigger than it.
540, are you bigger than this?
Yes.
Bigger than this?
Yes.
Bigger than this?
Yes.
Bigger than that?
No.
So it knows to stop in the row before.
Then, because we have three columns in our table,
we'll have to tell VLOOKUP to get the price
from the third column.
And then VLOOKUP will bring it back.
So you ready?
Equals VL, tab.
And our lookup value, that's what we have to look up.
And that's a relative cell reference.
Comma.
We have to tell VLOOKUP where the table is.
Highlight the first column and all the way
to the third column.
And I'm going to hit the F4 key to lock it,
so it remains locked on that table all the way down.
Comma.
Column index number.
That's us telling VLOOKUP which column,
the first one, the second one, or the third one,
has the thing you want to go and get and bring back to the cell.
So I'm typing a three comma.
Now here we have a choice.
Approximate max, that's when we're doing commissions
or pricing or taxes.
And-- let me move this out of the way--
we have a first column where we can't possibly
do an exact match.
We want to do the match where we take it and bump
into the first bigger one and jump back.
Now actually, technically, that's
not how internally VLOOKUP does it.
It does it a much faster way, but that's the conceptual way
to understand how it works.
And we are going to tell it approximate match,
so it does that.
Later in this class we'll have exact match,
when we're looking up text or something,
people's names or e-mail addresses.
Now here's the thing.
That screen tip shows range lookup argument
with square brackets.
Any time you see square brackets,
it means if you know the default,
you can leave that argument out.
And guess what?
We know the default. Approximate match is always
the default for VLOOKUP.
So we do not even have to put this argument in.
Any time we're doing approximate match,
we are going to backspace.
We're not even going to get to that.
We're going to leave it out.
That's the formula.
Close parentheses.
Control, Enter.
Double click and send it down.
Control 1.
And here's our Format Cells.
I'm going to say Currency.
Two decimals is fine.
Click OK.
Now while this column is already there,
I'm going to right click it and go to the mini toolbar
and get my Format Painter.
Because guess what?
I don't want to reformat it over here.
It instantly gets it right.
Now the revenue formula is easy.
Click in the top cell.
Equals the price times the actual number of units.
And we are never going to have a rounding problem here,
because we have dollar amounts, or even if we had pennies.
We don't have any fractions of pennies or tax rates,
so we don't have to use round, just two relative cell
references.
Control Enter.
Double click with my angry [INAUDIBLE] and send it down.
I could go down a few cells or go down
to the last one, Control down arrow.
And I'm going to hit the F2 key to check.
Sure enough, that's right.
While I'm down here, I'm going to check this one.
F2.
And it looks like it got the right cell references.
Escape, Control, up arrow.
Now we simply come over here.
And I'm going to highlight these two cells.
Right click, Format Painter.
Click right there.
Right click Format Painter.
And click right there.
Now I want total revenue, but I want to actually force
the wrap on this.
Notice over here, it looks like we have the built-in wrap text.
But I don't want it to have any text here.
I want it to always drop down after total revenue.
And the keyboard is Alt, Enter.
That's a forced text wrap in a cell.
And I'm going to type Helper Column, Enter.
Because we have two extra helper columns.
Price then Total Revenue.
Now we simply click in the cell.
Alt equals is the keyboard for AutoSum.
Click in the top cell.
Control, Shift, down arrow.
And I'm going to use Control Backspace to jump back
to the active cell.
And Control Enter.
That is our total.
Now I want to teach you two new functions.
And we've seen both of these in our prerequisite class.
Now just to pull a parallel for VLOOKUP,
I want to expand this column.
I'll change the size back later.
And I want to look at a different function called
LOOKUP, not V for vertical, not H for horizontal, the LOOKUP.
Now here's the amazing history of this function.
This is one of the original functions all the way back
to VisiCalc.
When Bricklin and Frankston invented VisiCalc,
they wanted a function to look up
tax rates and things like that, so they could do their taxes.
But there are some important facts about this function.
Now the amazing thing is it will do-- and notice
there are two screen tips.
They both have lookup values.
And LOOKUP only does approximate match.
So if you have an exact match situation,
if you're using LOOKUP, you have to sort the first column.
But we have a lookup value, but we have two options.
You can have a lookup vector, where
it will find a match and then a completely separate result
vector to look something up and retrieve it.
The one we're going to use here is the array.
And the amazing thing about LOOKUP value and ARRAY
is that array will do horizontal or vertical lookup.
Now actually, if you go to the website and download the files,
the PowerPoints have complete notes on all of the functions
we're using.
Now how does it determine whether it's
doing VLOOKUP HLOOKUP?
If your table is taller than it is wide
or exactly the same number of rows
as it is wide-- number of columns-- then
it does vertical lookup.
If the number of columns is bigger than the number of rows,
then it does HLOOKUP.
The final point about LOOKUP is LOOKUP, like the SumProduct
function, is one of those few magic functions in Excel
that can handle array operations without any special keystroke.
So we're going to do this.
I'm to say, hey, look up this, comma.
And I'm going to highlight the table, just like we
did in VLOOKUP and hit F4.
But wait a second.
ARRAY?
I don't see a column number.
Here's another cool thing about LOOKUP.
It always gets the value from the last column.
So actually, if you are doing VLOOKUP
for an approximate lookup, that's
a much shorter formula to enter.
Close parentheses.
Control Enter.
Double click and send it down.
It gives me the exact same values.
Now actually, I'm going to move this off to the side.
And watch this.
I'm going to use my Move cursor, right there.
Click and I'm dragging it or moving it.
And I'm going to leave this as a trail in this finished
workbook.
So this would be Lookup to Get Price.
And I'll leave that over there, move this back.
Now we want to see the real reason we
are learning how to use LOOKUP.
Control C, Control V. And this is not
going to be helper column.
This is going to be single cell formula.
Now what we would like to do is if there was a way, just
from this column, to look up every single one of these
and retrieve from our lookup table an array of prices,
we could simply multiply that array of prices
times the number of rolls.
And as an array formula, an array calculation,
we could calculate, in a single cell, our total revenue.
Now we might think we could do VLOOKUP and for the lookup
value just highlight all of the items we want to look up,
but that argument cannot handle an array operation.
So no problem.
We switch over to the original lookup function, LOOKUP.
And there it is.
This LOOKUP value, I can highlight this entire column.
Control Shift down arrow.
Control Backspace, because I'm not going to copy it.
Comma.
And then the array.
I'm simply going to highlight this table.
And close parentheses.
But let's go back and think about this.
That lookup value right there.
It's expecting a single value like over here, where it gave
us our result a single value.
But because we gave it a whole column--
and I don't know how many there are there-- let's
just say there's 10,000 there.
Because we gave it 10,000 values,
LOOKUP will deliver 10,000 values to the cell.
Now if I hit Enter, it only returns the first one,
because a cell cannot display more than one value.
But in Edit mode, I can highlight this
and hit the F9 key to evaluate.
And you've got to be kidding me.
Look at that, 168, 168, 198, 168.
Exactly like we got over here.
So Control Z. Using what's called a function argument
array operation-- meaning we gave it lots of values,
so the function is going to spit out
an array of answers-- and our array,
also knowing that this function automatically
does approximate matching retrieves
a value from the last column, we have done our job.
We have looked up every single price.
Now what do we do?
Well, I need to multiply this resultant array
times the entire column.
So what function is perfect for multiplying and then adding?
SumProduct.
Now SumProduct has a ARRAY, ARRAY.
I'm simply going to leave that there.
That first ARRAY-- remember if I F9,
it's just all the prices from this column-- Control
Z. Come to the end.
Comma to get to the second array.
And now I simply highlight the number of rolls.
Control Shift down arrow.
Control Backspace to move back to the active cell.
Internally some product will take
all of the number of rolls and the prices,
multiply each one of them, and then add them.
So when I hit Enter, it's going to give me the total.
Now one other thing about this F2 and click inside here,
that array1.
That's an array operation.
If we had put this inside of the Sum function
and used the multiplying operator,
we would have had to use a special keystroke.
Because Sum function doesn't understand array operations.
But the SumProduct has no problem with this.
So Control Enter.
And the beauty of this, of course,
is Control Shift down arrow.
Alt, E, A, A to remove everything.
This is never going to get it, right?
But that doesn't need those helper columns.
There are some situations where you're
building models, where you just don't want all the space to be
used in your model.
So having an alternative to create a single cell formula
is awesome.
Now I'm going to Control Z because, of course,
I'm going to leave that there.
Now, there is one last task.
We want to count, for each one of these pricing categories,
how many transactions we had.
Now I'm going to use this extra Price column here and use
a simple CountIfs.
I'm going to come over here, right click Format Painter.
Click.
And then here I'm going to type Count transactions
in each price category and Control Enter.
I'm going to change the width of the column.
Double click.
Equals CountIfs.
The criteria range, Control Shift,
down arrow, F4, because I am going to copy this
and I need it locked.
And there I have it.
Simply the criteria is, that particular price,
close parentheses.
CountIfs used to count the items from this column over here
with a single condition or criteria.
Control Enter.
Control Shift tilde, or grave accent,
to apply the general formatting.
And then click and drag down.
Now if we didn't have these columns here,
it's not too much more difficult.
And I can see I put this in the wrong column,
so I'm going to highlight the whole column.
Click with my Move cursor and drag over a few.
Highlight this.
Control C, Control V. And point to the smart tag and say,
Keep Source Column Widths.
And now I'm going to delete these formulas.
Now if we didn't have this Price column,
is it possible for us, given that we
have From Units, To Units, and Price,
is it possible to build a formula to count?
Sure it is.
Because we have the hard units here, right?
We simply can use CountIfs with two conditions or criteria.
Equals CountIfs.
And the criteria range, I'm going
to have to repeat it twice.
I click on the top cell, Control Shift, down arrow, F4,
because I need it locked.
Comma.
And now I'm going to scroll over here
to concentrate on this formula.
The criteria-- well, the first condition
is it has to be greater than or equal to the lower,
so in double quotes we put our comparative operator,
double quotes.
Greater than or equal to.
Always have to use two characters
when you have an equal sign.
There is no single character for greater than or equal to.
End double quotes.
And we have to join it using ampersand to our lower.
Comma.
And I don't want to go over there and highlight again.
So watch this.
I'm going to click on my screen tip
to select everything in the criteria range,
one Control C. And then click on Criteria Range
to Control V. Comma to get to my second condition.
And we have to do double quote.
And this is less than or equal to-- notice
this is set up with a 43 and a 44--
sometimes if the upper and lower of the next class of the same,
then you have to build your criteria differently.
But we have two equal signs.
Both the upper and lower are included
for counting for each category.
End double quote.
Join symbol.
I get the upper closed parentheses, Control Enter.
Now we're going to have a problem here.
When I drag this down, the last cell,
F2-- it's looking at text.
We didn't have a number here.
Now actually, if we wanted that text there-- and that's
pretty good, because it tells us exactly what's going on--
we could build a crazy formula, but I'm not going to do that.
Two ways we could do this.
We could just put some huge number here,
which I don't like.
And then of course it works, Control Z. Or I
can just come here.
And guess what?
I'm just going to get rid of the last conditions.
So now we have CountIfs only in the last cell, only saying,
are you greater than or equal to that number.
So close parentheses, Control Enter.
That will work.
Wow.
So in this video we saw two different CountIfs,
one not based on the helper column,
just the number of rows, one based on the helper column.
And then we saw, over here, an amazing array formula
with SumProduct and LOOKUP that calculated the total revenue
just with quantity column and lookup table.
And then, of course, in many situations
you just want to add some helper columns.
VLOOKUP, Calculate revenue, and Sum.
So in this video we saw some important functions, VLOOKUP,
SUM, LOOKUP function, SumProduct, and CountIfs.
Now in our last video we're going
to have a problem, where we have to use the amazing lookup
functions, INDEX and MATCH.
We'll see you next video.