Basic Excel Business Analytics #09: Shortest Distance Shipping Costs: INDEX, MATCH, & IF Functions

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.

You May Also Like

About the Author: Oren Garnes

Leave a Reply

Your email address will not be published. Required fields are marked *