Welcome to Highline BI-348,

class video number nine. Hey, if you want to

download this workbook, BI-348 Chapter 7 Start,

or the finished version, click on the link

below the video. In this video we have a

shipping cost problem. And it’s a look-up

problem where we’re not going to be able to use

the VLOOKUP function. Now let’s just read

our problem here. A compressor sales company

has four shipping centers, Seattle, Milwaukee,

Birmingham, and Oakland. It delivers the compressors

to customers by truck. The per mile shipping

charge is $3.11 with a minimum charge of $75. For each customer, determine

the shipping center– either one, two, three, or

four– which has the shortest distance, and then calculate

the total shipping charge for each customer transaction. Now here’s our database

with all of our customers and there’s the zip code. So you could see, for

this particular zip code, this table, which

has Seattle at that zip code, Milwaukee zip code,

Birmingham zip code, Oakland zip code, but each

row in this table tells us how many miles

from the particular shipping center to this is zip code. So if we’re looking

through here, then if we’re shipping

to Chandler, Arizona, we would want to use

Oakland, California. Ah, once we determine

that for every single one of our records– and

let’s Control-Down Arrow– so the table shows us

all of the transactions looks like we have

about 70 customer transactions that we need to

calculate the Min distance. Then we need to figure out,

amongst one, two, three, four, which one of

the shipping centers. And then we need to

calculate shipping costs. So it looks like

three extra columns. Now I’m going to go ahead

and click in this cell. And I went ahead and

added Min Distance, Shipping Center,

Total Shipping Charge, and added some formatting here. Now the Min formula, remember

for each one of these customer zip codes for a particular

shipment of a compressor, here are the miles. So we need to create a formula

right here that’ll tell us the min number of miles. That’s not a hard one. We say equals– hey

there’s the Min function. And we’ll highlight the four

relative cell references. Close parentheses,

Control-Enter, and double click and send it down. I’m going to Control-Down

Arrow just to check, and sure enough,

it got it right. Control-Up Arrow. So for each one of these

customers zip codes, which represents a shipment

for a compressor, we know the Min distance. Now we need to calculate

the shipping center. Well this is an easy one here. And I’m going to right click,

click the Format Painter, and click both of these cells. And so we have our green

[? arrow ?] [? pointer. ?] Well, this is not hard. I already know it’s 766, so I

look at 766 amongst these four, then I jump up

and I get Oakland, and then I bring Oakland

to put it back here. Next for this one, 758,

it looks Oakland, Oakland. Now this one right

here, what do I do? I look up 373

amongst these four, I find the relative

position one, two, I go up and get the Milwaukee

and bring it back over here. Now VLOOKUP is not going to

do this, because, notice, we always have a Min distance

to look up in a range of values. So for any one of these,

we have a look-up value, a look up range to

determine the position, and then we have, in

every single formula, we’re always going to have

these values to look up. Now, last video, we learned

about the VLOOKUP function, and that’s never

going to work here, because that only does

approximate match, and we cannot sort

these values at all. So, no problem, there is an

awesome look-up function, which we saw lots in

our prerequisite class, the Index function. Now the Index function, we’ll

see it a lot in this class, but that array is where you put

the values you want to look up. And it’s amazing. It can be a one-way horizontal,

one-way vertical, or a two-way. Our array of values to look

up is a one-way horizontal. Now I’m copying this down,

and I always need it locked, so I hit the F4 key. Comma. Now index needs a row. And notice these are columns,

this first argument row number. If the array is

one-way, and it doesn’t matter if it’s vertical or

horizontal– vertical, that means filled with row

positions, or horizontal, filled with column positions. If that array is one-way,

then the row number argument is all you need to give it. Just some relative

position, and it will retrieve the right element. Later in the class

we’ll use the array and we’ll see that we have

to use both row and column for a two-way look up. Now what are we going to do? Well I already can see here

if I was looking up 766 amongst these four, one,

two, three, four, it would be the fourth relative position

in these numbers, which would easily get me Oakland. So what do we use? In the row number, we

use the Match function. Now the Match is a

look-up function. And I’m going to go

ahead and give it a look-up value of

our Min distance. There it is, look-up

value, comma look up array. It’s always looking it

up in those four values. And, notice, both the look-up

value and the look-up array are relative cell references. Now what does Match do? It’s not going to look

up 766 and get 766. No, Match function

looks through a range and returns the

relative position. One, two, three, four,

it’ll return a four. Now, comma, we’re using

exact match, zero, because these values

are not sorted. Close parentheses. That’s all we need. Notice it comes back

to our Index function and that Match is going to

deliver a relative position to Index’s row number. Now I very carefully close

parentheses, Control-Enter, double click, and send it down. That is pretty cool. We’re using Index and Match,

F2, to look up a Min value among some miles for

particular zip code and return the shipping center. Now our third task here is

to calculate total shipping charge. Well we know the

Min miles, 766– oh, but we’re given

some formula input, so I’m going to, very

carefully and off to the side, I’m going to build

an assumption area. Now I’m always going to put

formula inputs, parameters, assumptions, and variables. The textbook uses parameters,

I like to use formula inputs, but all of these are

synonyms for what we’re putting into formulas. Things they can very. Increase the column

widths of this first one, because we’re going to

have some long labels. And then the second one,

and add some formatting. And now, over here, we have

a per mile shipping charge. It looks like it’s $3.11, so

3.11 Control-Enter, Control-1, Tab, Arrow, Arrow,

Enter for our currency. By the way, Control-1– there’s

a keyboard, Control-Shift-4 for currency, the difference

is that Control-Shift-4 always puts these red brackets

for accounting and red for a negative, and

I don’t like that. I like the minus, and

so that’s the first one in the Format Cells text box. We still have minimum

charge and that’s going to be 75,

Control-Enter, and I’m just going to right click

that paintbrush, and click right there. Add some borders. Now we can build our formula. Let’s just try a simple formula. Equals– well I know

the miles, times– and there is the

per mile charge– and I need to F4, to lock

it down, on that 3.11. We’re not going to run into

any rounding error here, so I don’t need

the round function. Control-Enter, double

click, and send it down. Well I can already see for this

one right here, four miles, and there’s a charge of $12.44. But see, shipping to Berkeley,

California from Oakland is not very far. So we need to amend our

formula, because the Min charge is $75, F2. And I’m going to

use the If function, because, really, if this

amount is less than $75, then we need to put in the

$75, otherwise, falsehood, be equal to or greater, then

we just let this formula run. So I’m going to use If. And I’m going to have to

use that little bit twice. So I’m going to copy it. I’m going to ask

the question, hey are you less than our hurdle? And I’m going to lock

it down with the F4 key. If it’s less, then I

need this hurdle, F4. That’s the value if true. Otherwise, the value of false

Control-V as our formula. And If function always

needs some logical test that comes out true or false. Then we put what

to put in the cell if it comes out true,

what to put in the cell if it comes out false. So you can always think of the

If function as the function you want to use when you

have one of two things you’re putting into a cell. Now in this case, we have a

cell reference or a formula. All right, Control-Enter,

double click, and send it down. And so now for those situations

where we had less than $75, we get, not that

$12.44, but the $75. Now my cells, right there, I’m

going to Control-Down Arrow, because I need to F2 and

check if I got all the cell references right. And it looks like I did. Control-Up Arrow. Wow. So in this video, we saw how to

solve a shipping cost problem using the Min, the

awesome Index and Match, and using the If function to put

one of two things in the cell. All right, this is the last

video for chapter seven. Chapter seven was all about

building smart spreadsheets with a set of rules about good

spreadsheet model building. All right, next

chapter we’ll actually jump back to chapter two and

do descriptive statistics. All right. We’ll see you next video.

## 11 Comments

super… many thanks… ; D

Nice one!

Excellent stuff on "smart" spreadsheets.

These formula are so cool. Thanks for uploading.

cool…by using a formula how do i pull the values from the entire row or column just by using a single cell reference.? do you have any video of it.?

You can use MAX formula to calculate Shipping Charge, like =MAX(miles*price, 75)

@Francis Osuna, Glad you like it!

This was amazing! I tried following this tutorial using Numbers (from Apple) and it still worked really well. Just a little slow since locking down cell references isn't quite the same as it is with Excel!

I love it! Eu adorei isso! Agora em usando Excel em Ingles

you make me feel proud of myself because every single day you teach me something new and i really do appreciate thank you Mr. Mike

Thanks

Mike great videos and energy in delivering the material, thank you again!