Articles

Basic Excel Business Analytics #08: Total Revenue Calculation: VLOOKUP or LOOKUP/SUMPRODUCT?


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.

Comment here