Welcome to Excel Magic
Trick number 1,417. Hey, if you want to download
this Excel workbook file so you can follow along, click
on the link below the video. Hey, in this video, we’re
going to talk about ranking. Now we’re going to do it for
unit profit and total profit. Not only that, but
we’re going to do the ranking for products
and then products within manufacturer. Now in this video we’re going
to use Excel spreadsheet functions. Then in 1,418, we’ll
do pivot tables. Then in 1,419 we’ll see how
to do it with DAX formulas. Now we have a table
with our transactions with units and profit. Then we have our
product table that gives us the price, standard
cost, and manufacturer. Over here is where we’re
going to create our formulas. And this is actually
the finished version. We need to calculate total
profit for each product, and then rank all the
profits, and then rank within each manufacturer
or the individual profits. Then we’ll do profit per unit
and rank all the profits, and then ranking
within manufacturer. Now let’s go over
to the sheet 1,497. Now I’m actually going
to hide some columns so we can zoom in and
see everything up close. I’m going to highlight the
columns and Right click, Hide. So we’re going to start off with
total profit for each product. Well, this formula
is straightforward. We’re going to use
the sum ifs function. Now, the sum range, that’s
the range with all the numbers we want to add. Now I’ve already converted
these to Excel tables by going up to Insert and
clicking on the Table button. So once we have this
as an Excel table, that Profit field
name at the top– if we hold our
cursor right above it and we see that downward
pointing black arrow– when I click, it puts the
entire column into our function. Now that’s called Table Formula
nomenclature or structured references. It always has the
table name, and then in square brackets,
the field name. Now I type a column. The criteria range,
well, I need to look through the entire
product column. So with my black downward
pointing arrow, click. There’s the table. There’s the field name, comma. The criteria? Well, for this one
row right here, I only want sum ifs to add up quad
products as a relative cell reference. Now I can close parentheses. Control-Enter, and there
is that little fill handle in the lower right hand corner. I move my selection cursor. And when I see my crosshair or
Angry Rabbit, I click and drag. I go to the last
cell, hit F2, verify that all of the cell references
are working correctly. Now we need to rank. Well, there’s a great
function in Excel. There’s actually a couple
different functions we can use when we
have a single number. We want to rank that number
against all of the others. Now actually, I just noticed
when I copy this down, notice I copy the formula
and the formatting. The smart tag is still here. So I’m going to click on
it and say please fill that without formatting. All right. So we need to rank
this individual item against all of the others. And there are a few
different functions we can see in our dropdown. RANK, that’s the original
RANK function before RANK.EQ and RANK AVERAGE. These two functions
came in Excel 2010. Anytime you see that
little yellow triangle, that means compatibility mode. It’s there for
backwards compatibility. But we want to use one of these
two functions moving forward. Now RANK and RANK.EQ
are exactly the same. When they see a tie
like, a three-way tie for third, all three
positions would get 3, 3, 3, meaning third
place for all three amounts. RANK. AVERAGE would simply
average 3, 4, 5. And so each person
would get four. Now actually, if you
scroll down below, I have an example of how
RANK.EQ, RANK.AVERAGE, and COUNTIFS, which
we’ll see later, RANKS when there’s ties, because
we don’t have any ties up here. All right. So we’re going to
use the RANK.EQ tab. Now number, that’s
the particular number you want to take and
rank against the others. Comma, the reference? I’m going to highlight
all of the numbers. F4 key to lock it since
we’re copying it down, comma. And then you can select
from the dropdown either descending or ascending. We want descending. And guess what? That is the default. Anytime you
see an argument in a screen tip with those square
brackets, it means if you know what
the default is, you can leave this argument out. And I know what it is. I’m going to backspace and
leave that argument completely out so it will
automatically do descending. Close parentheses,
Control-Enter. Now I can come to
my fill handle. And when I see my Angry Rabbit,
double click and send it down. Immediately come to the smart
tab fill without formatting. Now I’m going to go to
the last cell and F2 to verify that the cell
references are working. So we can clearly see
that quad is first in terms of total
profit and Darnell Fast Catch is second in
terms of total profit. Now what if we want to
rank within manufacturer? So here we want to rank these
three items against these three items, because they’re for the
manufacturer Gel Boomerangs. Well, there’s no Rank If
function equals Rank If. Uh-oh, that one doesn’t exist. But no problem. We can use the COUNT IFS
for ranking or ranking with conditions. Now what do we want to do? We want to isolate
these three items. And there’s the criteria for
isolating Gel Boomerangs. So for the criteria range, I
highlight the entire column of manufacturers, F4
to lock it, comma. The criteria is this
particular manufacturer as a relative cell reference. And what this will do is it
will isolate these three items. So when we put total profit in,
these will already be isolated. Now comma, how are we
going to rank these? Well, for criteria range we’re
going to put all of the profits in, F4 to lock it, comma. And then we have to ask the
question, of these three items, how many of you
are greater than? And comparative operators
in COUNTIFS sums and similar
functions always have to have their comparative
operator in double quotes. Then we join it using
the ampersand or Shift-7. That’s the join symbol. And I’m going to
join it to this. Now notice what
this question does. How many are greater than that? Zero. When I come down
to the next one, since we’re already
isolated these three, how many are greater than that? One, two. When I come down to this
one, how many are greater? Simply one. Now close parentheses. It won’t quite give us
what we want, but almost. Control-Enter. Now when I double click and
send it down, watch this. I’m going to show you a terribly
useless keyboard shortcut. I hardly ever use it. But we can open the smart
tag with Shift-Alt F10, and then Down arrow to fill
without formatting, and Enter. Now notice, 0, 1, 2. It really should be 1, 2, 3. So all we have to do is add one. Now notice when
we copy this down, it messed up the formatting. But if you have the
entire column highlighted, and in the active
cell you hit F2, since we need to
plus one for all of the formulas in this column,
now that the formulas edited, if I Control-Enter, it will
copy the formula down and leave the formatting intact. Control-Enter to populate
that edited formula down. And now we get 1, 2, 3. So we’re ranking the profits
within the manufacturer. And that doesn’t
depend on sort at all. We could completely sort
this in a different order, and it would work. Now our next task is to
calculate profit per unit. That means I have to
unhide the Product table. So I’m going to highlight D
to L, Right click, Unhide. And there we go. The table is unhidden. Now what are we going to do? Well, for quad, in order to
calculate profit per unit, I need to retrieve retail
price and then subtract the standard cost. No problem. That means we can
do two V look-ups. So equals VL Tab. The item we’re looking up? That’s the quad product, comma. The table? I’m actually going to highlight
the entire inside of the table. And in table
formula nomenclature it just puts the
table name, comma. Now column index, I’m counting
on my fingers one, two. So retail price, I need to
put a 2 for column index. So I simply type that in, comma. We’re using exact match,
because look-up table is not sorted for the first column. So I put false or zero,
close parentheses. Now that’s V looking up
just the retail price. So I’m going to copy this,
control-C in Edit mode. Come to the end, type minus
and Control-V. And guess what? The standard cost is
in the third column. So I simply highlight
and type a 3. That formula will work. Control-Enter. Now watch this. Here’s yet another way
to highlight and avoid messing up with the formatting. Before I click on
the last cell, I’m going to hold Shift to
highlight all of them. Then I’m going to hit the F2
key to put it in Edit mode, and Control-Enter to
populate it all the way down. There’s the profit per unit. Now we can RANK equals– and we’re going to use
equals RANK.EQ tab. There’s the number. I Left arrow, comma. Left arrow, Control-Shift
Down arrow F4, to highlight that
column and lock it. Close parentheses
because we’re using the default descending,
Control-Enter, double click and send it down. Shift-Alt, F10, Down arrow,
Down arrow, and Enter. Now we’re similarly going to
have to use COUNTIFS for rank within manufacturers. So equals COUNTIFS. The criteria range, the
entire manufacturer column. F4, comma, this particular
manufacturer as a relative cell reference, comma. Here’s our profit per unit,
F4, comma, and double quotes. How many of you are
greater than join to this actual
particular profit? Remember, since these three
items are already isolated, now we’re going to get how many
are greater than this biggest one? Zero. So close parentheses
and plus one. Control-Enter, Double
click and send it down. Control-Alt, F10, Down
arrow, Down arrow, and Enter. Wow. So that was a lot of
fun with adding up total profit with some IFS,
RANK and using RANK.EQUIVALENT, then a ranking with a condition
or criteria using COUNTIFS. We used two V look-ups for
calculating total profit. And then RANK.EQ and COUNTIFS. All right. Next couple videos,
we’ll see how to create the same
report with pivot tables, and then with DAX formulas. All right. See you next video.