Last weeks Dueling Excel Episode #1473 looked at Ranking Ties by Weight. Today, in Episode #1478, Bill shows a method using a temporary column.
...This blog is the video podcast companion to the book, 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!
...This blog is the video podcast companion to the book, 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:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1478: rank ties redo.
Hey, yes, all right, it's Friday.
It should be a Dueling podcast, but, you know, it's the holiday.
So, Mike and I will be back next Friday with a Dueling podcast.
Let's visit last Friday's podcast where we were trying to rank these scores and when there was a tie, award the highest rank to the person with the highest weight.
A lot of people sent in a suggestion: why don't you just build a temporary column that is the score plus weight divided by a thousand?
That was a great idea because then what we're going to have is all of these are ranked-- they have a score of 7, but they have different values in the Temp column.
So, we’ll kind of insert a new column here.
Rank.
=RANK of this temporary value within that whole range of values.
Shoot that down.
Double-click the fill handle.
See that now within the 7s here, their order two, three, four, five because of the little decimal weight there.
A very easy way to solve that problem.
I had tried an array formula.
Mike came up with a way, but I think that this way beats all of those.
So, very fast way to go.
For those of you in the U.S., hope you’re having a great Thanksgiving holiday weekend.
We'll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
Learn Excel from MrExcel podcast, episode 1478: rank ties redo.
Hey, yes, all right, it's Friday.
It should be a Dueling podcast, but, you know, it's the holiday.
So, Mike and I will be back next Friday with a Dueling podcast.
Let's visit last Friday's podcast where we were trying to rank these scores and when there was a tie, award the highest rank to the person with the highest weight.
A lot of people sent in a suggestion: why don't you just build a temporary column that is the score plus weight divided by a thousand?
That was a great idea because then what we're going to have is all of these are ranked-- they have a score of 7, but they have different values in the Temp column.
So, we’ll kind of insert a new column here.
Rank.
=RANK of this temporary value within that whole range of values.
Shoot that down.
Double-click the fill handle.
See that now within the 7s here, their order two, three, four, five because of the little decimal weight there.
A very easy way to solve that problem.
I had tried an array formula.
Mike came up with a way, but I think that this way beats all of those.
So, very fast way to go.
For those of you in the U.S., hope you’re having a great Thanksgiving holiday weekend.
We'll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
