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.

## 16 Comments

many thanks indeed as usual… ; D

@Francis Osuna You are welcome!

how to lock individual cells just after the data is entered????

You, sir, are awesome.

Great video, thanks!

One little thing: In your pricing table you switched up Units to 288 and Units from 289, resulting in different transaction counts.

SUMPRODUCT always comes in handy ðŸ™‚

I believe you could have used SUMIFs instead of SUMPRODUCT using boolean multiplication but would have resorted to using CSE. SUMIFs is faster at calculating than SUMPRODUCT. Also, can you list or show a video on which arguments for different functions can and cannot handle array operations? Thanks.

why you have different result when you countif price/roll $187 with unit form 144 -289 ? my logic say it should be have same result. ðŸ™‚

hey Mike thanks for all the kickass videos which help me a lot. Just wonder can you please recommend a good VBA course online? Since I cannnot find vba course in your channel but I find vba skills a high demand among employers. Many Thanks!

Hi Mr Excel,

I remember you once said as > is a comparative operator, which is viewed as text by excel, we need to put" " around it, when using it in a formula. May i please ask why when calculate total shipping cost in shipping cost tab, if I type the formula as =IF(I8*N2"<75",75,I8*N2), it will give me a error, but when I take off the " " from formula, it is all good to go? when should I use " ", and where should I put them, "<75", "<"75 ?

Below mention is my different account number & each account number made multiple times transaction of different amount.

Therefore, i need to calculate each account's total or sum of transaction amount that they made.

Account Amount

2013001 5

2013002 10

2013001 23

2013002 14

2014001 16

2015001 28

2013002 31

2013002 87

2016001 16

2016001 24

2016001 98

2013002 54

2013002 60

Required Result

Account Total Amount

2013001 28

2013002 256

2014001 16

2015001 28

2016001 138

Hello! A tip to extend the reach of your videos: First of all, two considerations: #1 Currently the channel has subtitles in English for most videos. #2 The tool of caption translate from youtube is very failure, mainly because the videos having many technical terms. | The tip: It would be very useful to be activated function "contribute closed captions", that allows viewers to produce translations of the subtitles. Tutorial: https://support.google.com/youtube/answer/6052538

In Brazil, for example, only a small margin of the population is able to understand the English language. My friends give up watching your channel for this reason. A perfect translation would be very grateful. ^^ By the way, I can help with the translation of the subtitles if the function is activated!

Great videos, I noticed a difference in results @ min 19.51, you have a count of 29 vs 34 using different formulas.

Please clarify this difference.

Thank you

Thanks

Hi, Mike thank you for all the helpful tips and tricks, just i wanted to ask you since i am not so knowledgeable what function or formula could i use to make an offer within brackets. i specifically want to create an offer for a group of travellers and i want to make an offer with a price from 10-15 persons, and from 15-20 persons ans so on. For your kind reply I thank you in advance.

Thank you, Mike, for one more excellent video and your kindness in replying my comments, GOD BLESS YOU AND YOUR FAMILY!