Dueling Excel looks at Scores and Tie Breaking. In this example, When there is a tie, we want to award the Highest Rank to the Largest Weight. Quite simply, after ranking a column with ties, we want to break the tie based on another column. Today, in Episode #1473, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen offer their solutions to this challenge.
Dueling Excel Podcast #90...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #90...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back. It's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from Excel Is Fun.
This is episode 91: rank ties by weight.
Mike, I've got a heck of one for you.
I already know you're going to win this one because I don't know how to actually solve it.
So, we have some scores here.
When there's a tie, we want to award the highest rank to the entrant with the largest weight.
Oh, no.
So, let's just talk about rank first.
We want to rank this item within this list of items.
Press F4 and copy down.
Typically, what they do is for all the tied items, so this 7, this 7, this 7, and this 7, they all get a 2.
The usual solution is to take that formula and say plus COUNTIF count how many things above me are the exact same score.
Above me in this case is C dollar sign 4 for C4 equals C5.
Copy that down and what we should see here is this 2 is still ranked as 2, but this 2 is now ranked as 3.
This 2 is ranked as 4.
This 2 is ranked as 5.
So, one solution that we could do is sort this descending by weight and that will make sure that the person with the highest weight gets the lowest rank.
Okay, I was trying to figure out if there's some way that we could have an adder that would say, hey, rank this item, this 207 within kind of an array that says, if these scores F4 is equal to the current score, then I want the weight.
Otherwise, I want zero.
See if that would work.
Ctrl Shift Enter and it's just coming up as a value.
I can't seem to coerce that into a proper array out there.
So, I'm just going to-- I'm going to send it over to Mike and see if he has a solution for this without sort.
Mike: Thanks, MrExcel.
Hey, oh, wait a second.
You cannot give me the point.
This is beautiful.
You did the rank and we had 2, 2, 2, 2 for these 7s and then you add this little COUNTIF bit and it gave us the right rank: 2, 3, 4, 5.
That's all given that this column is sorted, but that is a beautiful solution.
Now, yes, if the column is not sorted-- you're not allowed to sort, which would be the easy solution, then you have to do something a little bit more complicated.
Now, I'm interested in this part right here.
I actually want to copy this part of the formula, come over here, type in equal sign and Ctrl V.
I just want to see what this gives us.
Ctrl C, Ctrl V, Ctrl V. What it gives us is zero.
Well, what's happening here is that's an expandable range.
As I copy it down, it expands.
So, right now it's saying, how many of these are in that range?
One, two, three and guess what?
That’s the perfect amount.
Here's what rank gives us.
2 plus 0 is 2.
2 plus 1 is 3.
2 plus 2 is 4 etc.
So, if there was a way to simulate what COUNTIF is doing here with an expandable range on a non-sorted column, then we could have a formula.
So, let's go look over here.
So, here's our normal rank, right?
I put just a reminder, right?
So, here's the non-sorted.
So, these ones.
What I need here is to add a 0 to 2, a 2 to 2, a 1 to 2, and a 3 to 2.
Now, there's two conditions here if we're going to have a formula here.
First, is I need to ask if I'm in a cell right here, I need to look over at the 7 and say how many other items in this column are equal to 7, right?
So, I need to, in essence, isolate.
Before I jump over and ask a question of this column, I need to isolate and say, give me all the 7s.
In essence, they'll say-- there'll be an array of trues and falses.
We’ll eventually see a 1 every time we see a 7.
Then I once I do that, I've isolated this number, this number, and this number.
I can simply ask the question: for any particular position, is this number less than the other numbers associated with 7?
Let's just think about this.
If I'm right here-- if I ask is this-- 207, is it less than 207?
False.
190?
False.
205?
False.
188?
False.
That will give us our 0.
When we are right here, I'm going to ask a question: is 205 less than this?
The 205 itself, the 207, and the 190?
There’ll only be one.
205 is only less than this one number.
That will give us our 1.
All right, I'm going to build the formula right here.
Now, the first array I need to isolate.
So, I’m going to say, is this particular relative cell reference which has a 7 in it, is that equal to any of the other items in this column?
F4.
Now, that's going to give me trues and falses.
It better give me four.
F9, it gives me 4 trues, which represent the 7s.
Ctrl Z.
I need to convert those to ones and zeros.
So, I'm going to use double negative.
Double negative-- there’s lots of ways to convert trues and falses to ones and zeros, but that's a fast calculating way.
Be sure to put parentheses because you want to force the equal comparative operator to operate before or evaluate before the double negative.
That's our first array.
F9, you can see 1 1 1 1 4 where the 7s are.
That isolates before we get to the second column.
So, we're only looking at then-- the weights associated with 7.
Then comma, the second array, double negative, open parenthesis.
Now, I'm going to ask the question: is 205 less than any of the other items?
F4.
Now, right now, what's the answer?
Is 205 less than this, this, this, or this?
There's only one.
205 is less than this.
That'll give us the one in this cell to then add to the rank.
All right, I'm going to close parentheses and I'm going to copy and paste just so we can see these particular items and there it is.
Now, we can copy it up and down and because we have that relative cell reference, right, when it gets to another tie that B-- this C11 right here is really one, two, three cells to my left.
It's isolated and now it's looking just at the 2s and it gives me the proper please add one for this tie.
Now, we come over here-- actually, I’ll just copy this.
MrExcel used RANK.
That's the old function in 2010, works perfectly fine in 2010.
The new one is RANKEQUIVALENT.
So, I'll just use that.
I'm going to say, hey, please rank the scores comma within all of these.
F4, biggest is best, so I put a 0 plus that extra little bit right there.
Ctrl Enter, double-click and send it down.
So, for-- this better say two, this better say three, five and four.
All right, I’ll throw it back to MrExcel.
Bill: Hey Mike, that's brilliant.
Use SUMPRODUCT to figure out all of the people who had the same score, but a higher weight and just add that.
Great, great approach.
Couldn't figure that out for the-- to save my life.
All right, I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from Excel Is Fun.
This is episode 91: rank ties by weight.
Mike, I've got a heck of one for you.
I already know you're going to win this one because I don't know how to actually solve it.
So, we have some scores here.
When there's a tie, we want to award the highest rank to the entrant with the largest weight.
Oh, no.
So, let's just talk about rank first.
We want to rank this item within this list of items.
Press F4 and copy down.
Typically, what they do is for all the tied items, so this 7, this 7, this 7, and this 7, they all get a 2.
The usual solution is to take that formula and say plus COUNTIF count how many things above me are the exact same score.
Above me in this case is C dollar sign 4 for C4 equals C5.
Copy that down and what we should see here is this 2 is still ranked as 2, but this 2 is now ranked as 3.
This 2 is ranked as 4.
This 2 is ranked as 5.
So, one solution that we could do is sort this descending by weight and that will make sure that the person with the highest weight gets the lowest rank.
Okay, I was trying to figure out if there's some way that we could have an adder that would say, hey, rank this item, this 207 within kind of an array that says, if these scores F4 is equal to the current score, then I want the weight.
Otherwise, I want zero.
See if that would work.
Ctrl Shift Enter and it's just coming up as a value.
I can't seem to coerce that into a proper array out there.
So, I'm just going to-- I'm going to send it over to Mike and see if he has a solution for this without sort.
Mike: Thanks, MrExcel.
Hey, oh, wait a second.
You cannot give me the point.
This is beautiful.
You did the rank and we had 2, 2, 2, 2 for these 7s and then you add this little COUNTIF bit and it gave us the right rank: 2, 3, 4, 5.
That's all given that this column is sorted, but that is a beautiful solution.
Now, yes, if the column is not sorted-- you're not allowed to sort, which would be the easy solution, then you have to do something a little bit more complicated.
Now, I'm interested in this part right here.
I actually want to copy this part of the formula, come over here, type in equal sign and Ctrl V.
I just want to see what this gives us.
Ctrl C, Ctrl V, Ctrl V. What it gives us is zero.
Well, what's happening here is that's an expandable range.
As I copy it down, it expands.
So, right now it's saying, how many of these are in that range?
One, two, three and guess what?
That’s the perfect amount.
Here's what rank gives us.
2 plus 0 is 2.
2 plus 1 is 3.
2 plus 2 is 4 etc.
So, if there was a way to simulate what COUNTIF is doing here with an expandable range on a non-sorted column, then we could have a formula.
So, let's go look over here.
So, here's our normal rank, right?
I put just a reminder, right?
So, here's the non-sorted.
So, these ones.
What I need here is to add a 0 to 2, a 2 to 2, a 1 to 2, and a 3 to 2.
Now, there's two conditions here if we're going to have a formula here.
First, is I need to ask if I'm in a cell right here, I need to look over at the 7 and say how many other items in this column are equal to 7, right?
So, I need to, in essence, isolate.
Before I jump over and ask a question of this column, I need to isolate and say, give me all the 7s.
In essence, they'll say-- there'll be an array of trues and falses.
We’ll eventually see a 1 every time we see a 7.
Then I once I do that, I've isolated this number, this number, and this number.
I can simply ask the question: for any particular position, is this number less than the other numbers associated with 7?
Let's just think about this.
If I'm right here-- if I ask is this-- 207, is it less than 207?
False.
190?
False.
205?
False.
188?
False.
That will give us our 0.
When we are right here, I'm going to ask a question: is 205 less than this?
The 205 itself, the 207, and the 190?
There’ll only be one.
205 is only less than this one number.
That will give us our 1.
All right, I'm going to build the formula right here.
Now, the first array I need to isolate.
So, I’m going to say, is this particular relative cell reference which has a 7 in it, is that equal to any of the other items in this column?
F4.
Now, that's going to give me trues and falses.
It better give me four.
F9, it gives me 4 trues, which represent the 7s.
Ctrl Z.
I need to convert those to ones and zeros.
So, I'm going to use double negative.
Double negative-- there’s lots of ways to convert trues and falses to ones and zeros, but that's a fast calculating way.
Be sure to put parentheses because you want to force the equal comparative operator to operate before or evaluate before the double negative.
That's our first array.
F9, you can see 1 1 1 1 4 where the 7s are.
That isolates before we get to the second column.
So, we're only looking at then-- the weights associated with 7.
Then comma, the second array, double negative, open parenthesis.
Now, I'm going to ask the question: is 205 less than any of the other items?
F4.
Now, right now, what's the answer?
Is 205 less than this, this, this, or this?
There's only one.
205 is less than this.
That'll give us the one in this cell to then add to the rank.
All right, I'm going to close parentheses and I'm going to copy and paste just so we can see these particular items and there it is.
Now, we can copy it up and down and because we have that relative cell reference, right, when it gets to another tie that B-- this C11 right here is really one, two, three cells to my left.
It's isolated and now it's looking just at the 2s and it gives me the proper please add one for this tie.
Now, we come over here-- actually, I’ll just copy this.
MrExcel used RANK.
That's the old function in 2010, works perfectly fine in 2010.
The new one is RANKEQUIVALENT.
So, I'll just use that.
I'm going to say, hey, please rank the scores comma within all of these.
F4, biggest is best, so I put a 0 plus that extra little bit right there.
Ctrl Enter, double-click and send it down.
So, for-- this better say two, this better say three, five and four.
All right, I’ll throw it back to MrExcel.
Bill: Hey Mike, that's brilliant.
Use SUMPRODUCT to figure out all of the people who had the same score, but a higher weight and just add that.
Great, great approach.
Couldn't figure that out for the-- to save my life.
All right, I want to thank everyone for stopping by.
We'll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
