Ranking columns based on two sets of numbers 2

[TOTCOM11]

Here is my scenario:

I am using an Excel Spreadsheet (Excel 2003). I have 10 columns with two rows each. For conversation sake I will call these rows Row 2 and Row 3. The columns are B-K. I want to insert a third row (which I already have done; Row 4) which ranks each column. Rows 2 and 3 are in numeric format. I want to insert a number into each column in Row 4 which reflects the rank of each column based on the following rules in the following priority:

(1) The columns shall be ranked by the numbers in Row 2 in ascending order. If there is a tie then:
(2) The columns shall be ranked by the numbers in Row 3 in ascending order.

If there is a tie after these two rules, I want the tying columns to share the same rank. Let's say two columns share 4th place. The ranks would look like this:

1, 2, 3, 4, 4, 6, 7, 8, 9, 10. There is no "5" because of the tie.

Does anyone know how I can do this?

 

[GlennUK]

You haven't specified whether the pairs in rows 2 & 3 are linked at all. When a tie is in effect, does the tie-breaker decision depend on the corresponding cells in row 3? Or on the th and (n+1)th ranking numbers in row 3, when the rankings n and n+1 match the ranks of the tied numbers on row 2?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

The Rows 2 and 3 are NOT linked. The tie-breaker decision DOES depend on the corresponding cells in Row 3. It is only when this ONE tiebreaker rule doesn't find a winner that the columns remain tied, thus sharing the same rank.

Let me know if you have any other questions...

 

[GlennUK]

I don't think you understand what I meant by linked ... I meant are the cells in the 2 rows inextricable associated with each other by position rather than rank? It seems from the second part of your answer that this is the case. I can now start thinking about this.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

What is your difference when are talking about position vs. rank?

 

[GlennUK]

Here's 2 rows of numbers

23 18 18 17 16 15 13 
 5  6  7  7  8  9 10

The ranking of the 18 18 pair will be decided by the comparison of the 6 and 7 that are in the same positions, won't they? Or will it be decided by the 8 and 9 in the second row ... the pair of numbers that cover the same ranking positions in the second row, as the 18's do on the first row?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TOTCOM11]

Correct,

Looking at the rows you've given, the column with the 7 will be ranked before the row with the 6. You are right in saying position is what we are looking at as opposed to rank.

Let me know if you have any other questions...

Thanks,
TOTCOM11

 

[Zathras]

It's getting a little late in the UK, so I'll jump in here.

The only way I can find is to use a hidden row of intermediate results that combine the values from rows 2 and 3.

I am working with the following test data in B2:K3
[tt]
16 18 18 17 16 16 13 14 14 14
5 6 7 7 8 5 10 7 7 8
[/tt]
I put this formula in B21 and copied it to C21:K21
[tt]
=1000*B2+B3
[/tt]
The constant 1000 assumes that the largest number in row 3 has 3 or fewer digits. If that is not a valid assumption, increase it as necessary.

Then B4 has this formula (and copied to C4:K4):
[tt]
=RANK(B21,$B$21:$K$21,1)
[/tt]
giving the following results
[tt]
5 9 10 8 7 5 1 2 2 4
[/tt]
The temporary values can be placed in any out-of-the-way area of the worksheet and hide the row. They can even be transposed to a column and then hide the column if you prefer. (With a little bit of work.)

 

[TOTCOM11]

OK, Zathras, I tried replicating your example and for some reason I'm getting the following results:

1	5	8	7	9	1	10	3	3	6

What am I doing wrong? I double checked the numbers to make sure I entered everything right and it still ends up like this. Also, I WOULD be interested in learning how to transpose the row into a column so that I can hide the column instead of the row. I know how to transpose, I'm just confused about the little bit of work part).

Thanks!
TOTCOM11

 

[Zathras]

I just re-entered data from the posts above and got my same result. You will have to be a bit more specific about what you have in which cells before it can be determined what you did wrong. Most likely something in the wrong column.

I was using "transpose" in the descriptive sense. Unfortunately, there is no easy way to do it when formulas are involved. For all practical purposes you would enter each formula one at a time. You could copy one for the shape and then edit it, but that is still a bit of work.

 

[TOTCOM11]

Alrighty,

I see what I did wrong. Instead of ADDING B2 and B3, I multiplied them. I'm going to mess around with the data a little bit now and try to get them into a column type format. Pain in the butt, but for what I'm doing right now it's worth it. Thank you for your posts, and once I get my spreadsheet to do what I want it to, I will most definitely give you a star for your efforts, Zathras!

Thanks again!
TOTCOM11

 

[TOTCOM11]

OK, I tried the code on the spreadsheet I'm trying to use and I did not get my desired results. To make things a bit easier, let me display the data I have for one of my worksheets:

	             PAUL        JAVIER        BECCA        KEITH        VERNON        NENG        JAY        SERGIO*        WENDY        CARL*

MISSES	        1            3              4            2            2            1          1            2             4            5 

AGREE W/CHRIS    19           17              8	       16           12	        9         15           16	        14	       10

RANK	          3	        7	          8	        5	        4            1	      2	        5	         9	       10

OK, the names are in cells B1:K1. The Misses are B2:K2., Agree w/Chris is B3:K3. Lastly I have put Rank in cells B4:K4. Labels are in Column A.

What I want to do is rank each person (column) on the least amount misses he or she has. If there is a tie in this category, I want to then prioritize by the greatest number of times the person agrees with Chris. If there is a still a tie, then I want the rank to be the same. I have typed in the results that I got using the code given to me by Zathras, which is not my desired result since his code sorts BOTH rows by ascending order.

To clear up any confusion, the rank outcome I WANT is the following (from 1 to 10).

(1)  Paul
(2)  Jay
(3)  Neng
(4)  Keith
(4)  Sergio*
(6)  Vernon
(7)  Javier
(8)  Wendy
(9)  Becca
(10) Carl*

Basically I want the first row (Misses) to be prioritized by ascending order and I want the second row (Agree w/Chris) to be prioritized by decending order. Can anyone help me here?

 

[TOTCOM11]

My apologies... In the VERY BEGINNING of this post (my first post), I said that I wanted rows 2 and 3 to both be in ascending order, when in reality, that's not what I wanted. Sorry for any confusion. What I want is what I stated just above this post. Thanks for your understanding.

TOTCOM11

 

[GlennUK]

Put an initial calc in B4 of
=-1000*B2+B3
and copy it across.

Put a calc of
=RANK(B4,$B$4:$K$4)
in B5 and copy across.

Put a calc of
=B5+COUNTIF($B$5:B5,B5)-1
in B6 and copy across.

Put the numbers 1 to 10 in a range of your choice, and use:
=INDEX($B$5:$K$5,MATCH(numref,$B$6:$K$6,0))
to retrieve the rankings in order ( change numref to be the ref of a number from 1 to 10 )

... and also use:
=INDEX($B$1:$K$1,MATCH(numref,$B$6:$K$6,0))
to retrieve the names in order ( change numref etc ).


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Zathras]

Then all you need to do is change the working formula to
[tt]
=B2*1000-B3
[/tt]
(and copy across) By subtracting instead of adding, you invert the influence of the values on row 3.

The numbers I get are (B4:K4)
[tt]
1 7 9 4 6 3 2 4 8 10
[/tt]
which agrees with your list.

 

[Zathras]

Glen's formula of
[tt]
=-1000*B2+B3
[/tt]
accomplishes effectively the same thing. (Note the minus sign following the equal sign.)

Glen, kudos for your imaginative use of COUNTIF with the sliding range that allows the use of INDEX to produce the names list. Very clever indeed!

 

[TOTCOM11]

Thanks Guys! This has really helped me! How do I use the following formula if two people share the same rank, such as Keith and Sergio in my example? It seems like Keith's name is the only one that comes up when I go to 4, and no one's name comes up at 5.

 

[TOTCOM11]

What exactly does the COUNTIF statement do? When I use it, it seems to only replicate what I have using =-1000*B2+B3. I'm confused...

 

[TOTCOM11]

OK, I tried the code on the spreadsheet I'm trying to use and I did not get my desired results. To make things a bit easier, let me display the data I have for one of my worksheets:

                 PAUL        JAVIER        BECCA        KEITH        VERNON        NENG        JAY        SERGIO*        WENDY        CARL*

MISSES            1            3              4            2            2            1          1            2             4            5

AGREE W/CHRIS    19           17              8           16           12            9         15           16            14           10

RANK              3            7              8            5            4            1          2            5             9           10

OK, the names are in cells B1:K1. The Misses are B2:K2., Agree w/Chris is B3:K3. Lastly I have put Rank in cells B4:K4. Labels are in Column A.

 

[TOTCOM11]

Please disregard the previous post. I was messing around to see if I could copy code over, and as you can see, I can. Sorry about that....