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.