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.