all right so in this video i'm going to go over rank function in excel and google sheets
and also how to do something like dense rank so i'll go over both excel and google sheets
here let's get started with excel here so if i have these products and their ratings
i might want to have them ranked by highest to lowest rating or something like that so if i go
here and do equals rank so you'll see there's this rank function and there's this rank.eq function
there's also this rank average avg function so three variations so i'm gonna do rank
and here we have the number we're trying to rank so i'll go ahead and click on the seven so we're
trying to see within this range of numbers this seven uh what's the rank so i'm gonna do that
comma and then as a reference i'm gonna go ahead and select that range of numbers so if i do this
close parenthesis and hit enter see it says the rank of this number seven is nine so now if i
just go ahead and select this range right here f4 to make it absolute reference and just pretty much
drag this down to see what we get you'll see we get the rank of all of these numbers and you can
see right here if we look at these numbers the highest one we have is 10 which is the first so
as you can see is one and the next highest one in this range if you look it's gonna be
this nine and a half and as you can see it's not two it says three now the reason it says three is
because we have more than one ten so if you look in this list we have two tens so because we have
two tens we have duplicates what it does it skips two even though it ranks both of this one and then
it goes to three for the next highest one so three so now what it's gonna do after three if we have
more than one nine and a halfs which we do we have two of them then it's going to skip the four
and it's gonna go to five see in this case c9 would be the next highest number so we got five
and then for nine there are no other nines so if we go to the next one which is eight and a half
it will be see after five it's six because there is no duplicate number to basically
increase that rank the same sort of results you're gonna get if you do rank dot eq so i'm gonna take
this number comma and then within this range of numbers so i'm gonna do f4 enter and drag it down
and if you look at my results they're exactly the same
so in addition to this there's also this rank avg and again we're going to take the number
and then the range of numbers f4 close parenthesis hit enter
and you'll see that this one is going to give us a little different results compared to the other
two so because we have more than 110 see we have a 10 here and we have another 10 here
let's actually just do some conditional formatting so it's easier to see them
so we'll do a color scale so you can see we have this 10 and we have this other 10 and as you can
see we got one and a half which is the average of one and a two because we have the first and second
positions in our rank occupied by ten so between those two positions we have an average of one
point five in these functions there's also a third argument right here if i do another comma see we
have the order so by default we basically start ranking highest numbers like one two three like
that but you could also go the other way around so if i change this to one which basically is
ascending what's going to happen is that the rank is going to change so now as you can see number
four which is the lowest number in this list is one and then it's gonna go the other direction
i'm gonna undo this so all of this we can do in google sheets exactly the same way if i go back to
google sheets see i do equals rank and again there is the rank eq and rank average exactly the same
functions we're going to take the number comma and then the data which is this range of numbers just
like that we're going to do f4 absolute reference close parenthesis and drag this down and as you
can see we got exactly the same results so again similar to excel it starts from 1 then goes to 3
and so on so this is how rank function works and then if you wanted to do that last argument
ascending or not see we can do one to make it ascending which will basically flip our
results if i do this i'm gonna undo that and go back to this so that's that
so dance rank would basically just go the way i guess most people would expect
it should go so we would start from one being ten
and then nine and a half being two and then nine being three and so on until we get
all of these numbers now unfortunately there is no dance rank function in excel or google sheets
now what i'm going to do before i actually move to dense rank i'm going to show you one last thing
when you do this rank function you can also do an array so instead of taking this one cell and just
checking within this range we could actually just take the whole range like this and check
the rank within that range of numbers so if i hit enter see it will basically just spill the array
and that would just work out so the same sort of thing we should be able to do in google sheets
it's going to be a little different though so if we go back and do this range
as an array if i hit enter let's actually get rid of this one so we get it from highest to
lowest see it doesn't give us all of them it just gives us the first one so to make this work as an
array in google sheets example we're going to do ctrl shift enter or command shift enter to
basically add this array formula wrapper and now if i hit enter i will get the same sort of result
so there you go so that's our array doing the same sort of thing in this case we have dynamic arrays
and excel figures out that it's an array and it spills it so now let's get to dense rank so i'm
going to go ahead and delete this function and we'll create something that will get us what we
need so i'm going to take this step by step to explain you exactly how i'm going to get there
so what i'm going to do first i'm just going to do this unique function
and unique function will help us just get a unique list so for example if i select all
of these ratings which have some duplicates if i'd enter see it will just get us a unique list
so now i could take that and sort this list by adding a sort function around this unique function
and if i'd enter that should sort our list as you can see from lowest number to highest number
now if we want to sort it the other way around from the highest to lowest so i can simply just
add parameters to the sort function so after this unique function i'll do a comma and then we
have this sort index which i'm going to skip and i'm going to do another comma and there is sort
order so by default we have ascending i'm going to change this to descending which is negative one
in case of excel enter see we got from highest to lowest so the reason i do this
is that now if i just search this rating seven in this list and i find that seven is one two three
four five six position number six then i'm gonna know the rank for that is six and if i search for
nine and a half in this list it will be the second position which is basically going to give us two
so the function we can use to do that search is the function match so if i
go here and do equals match and search for this rating 7 comma in this list right here
see this syntax in excel means because this is a dynamic array it spills the results
basically we take the results of this array i'm gonna do a comma and then exact match
would be zero i'm gonna close parenthesis and hit enter and you'll see that number seven has rank
six so that's because one two three four five six position six in the list so at this point
i could just grab this formula sort function copy it escape go back to my function and replace this
and paste that sort function in there i am going to make this range absolute with f4 so
it stays in place at least for now and at this point if i hit enter and drag this formula down
as you can see 10 is rank one nine and a half is rank two and then we have rank three four nine
and so it goes so this would be similar to dance rank and what's nice about this is that we can
also use arrays here so instead of doing just c2 i can just select this whole column just like this
hit enter now this is not working because all of these other formulas are getting on the way
i'm going to clear all of those and as you can see one formula as an array formula works for
the whole range just like that so we got our dense rank using this combination of match sort
and unique and again if you wanted to rank your numbers the other way so from lowest to highest
so here we could just change this to ascending order to get from lowest to highest or that's
the default you could also just remove that if i hit enter see now we have the lowest number
one rank the next lowest number is two and so it goes i'm going to change this to negative one
just like that and this we don't really use so i'm gonna erase that and that's our
final formula to get what we're gonna call dance rank i'm gonna do the same thing now
in google sheets and it's gonna be roughly the same formula with a few differences because sort
function doesn't exactly work the same way as it does in excel and then also we need array formula
wrapper in google sheets logically it's going to be exactly the same thing so i'm going to go back
and we'll call this dance rank i'm going to clear
this let's also add conditional formatting in google sheets too so it's easier to see
so i'll do conditional formatting color scale for our ratings
and i want the highest one to be dark so i'll just switch this to be white and this to be green
good enough so now i'm going to close this let's do that dense rank function so again
we're going to start by getting unique list so equals unique this is the list we have right
there so if i select that range right there it should give me a unique list i hit enter
then we'll go back and sort this so again if you just do regular sort like this similar to excel
it will just sort this lowest to highest
we can do a comma and then again sort column in this case there's only one column we're gonna do
one comma and then finally is this ascending or not so i want descending so i'm gonna do false
and you could do true if you want ascending right so now you can see i got from 10 to 4.
so at this point now i could use this list for that match function right so i'm going to go here
and do equals match and take this cell this rating comma and check it in this range right here comma
and this will be exact match which is zero again exactly the same way we did in excel hit enter and
see the rank is 6 for this good now i'm going to take this formula copied without equal sign
hit escape go back select this range we have in the middle and replace it with my formula
and finally to be able to drag this formula down i'm going to select this range and do
f4 to make it absolute reference so at this point if i take this formula and drag this down
we'll have our dense rank and we should be able to just erase this and as you can see
this is one then this is two and this is three and it works so finally if you wanted this to be
an array formula then i'm going to clear all of these and replace this c2 with this range
right there and again if i just hit enter it's not going to work however if i do ctrl shift enter
or command shift enter if you're on a mac it's going to add this array formula wrapper you
could also just type array formula and put this thing in right formula same thing if i hit enter
there it is we have an array function that gives us this dense rank
and that should do it for this video thanks for watching and i'll see in the next one