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?

 

[TOTCOM11]

OK... Here is my last question for this thread. Now that I have the form working the way I want it to for 10 columns/people, I want to be able to modify it "on the run" so that it ranks the columns as my form changes. Glenn helped me set up my form so that I can use it as a front end interface. Basically I have 10 or so worksheets(not including the form you have helped me with in this thread), and I use this current form to summarize the data off of the other forms. I bring up the information I want by typing the number of the worksheet into a cell, and using the INDIRECT function, I am able to bring this information up.

My problem is that when the number of people/columns = 10, everything works great, anytime I have a number of people other than 10, I get #Value! errors. The maxiumum number of columns I will ever have is 10.

Basically the desired effect is if Worksheet 1 has x <= 10 people on it, it will rank them according to the formulas you guys helped me put together in this thread. I simply want to modify the formulas so that I can rank the people as I change the form number.

If you have any questions about what I'm trying to do, let me know. You guys have been such a help. Thank you for your time and effort on this matter.

TOTCOM11

 

[TOTCOM11]

OK, I've thought about this for a little bit and I think I have an idea of how to do this...but I need some assistance. In Row B16:K16, I have numbers 1-10 (ex: B16=1, C16=2, etc...). In Row B17:K17 I have the letters B-K. This is my thinking. I want to somehow manipulate the equation: =IF(B1<>"",RANK(B14,$B$14:$K$14),"") so that the "K" in the equation changes to the appropriate letter depending on how many columns are being used. I have a COUNT() function in cell B19 counting how many columns have data (ie: NOT NULL ("")). I want to match this number with the with the appropriate letter by using the information I have in rows 16 and 17. So in essence, if I have 4 columns, cell B19 reads "4", finds "4" in row 16, looks at the cell below it with the letter "E" in it, and stores this letter in the equation where the "K" used to be. Somehow I think I have to use the functions INDEX, MATCH, and INDIRECT, but I am not sure how to do this. Any suggestions???

 

[Zathras]

You could do it with a simple modification to my original formulas:
[tt]
B4: =IF(B2,RANK(B6,$B$6:$K$6,1),"")
B6: =IF(B2,B2*1000-B3,99999)
[/tt]
Copy across to column K
Hide row 6
Leave blank(s) in rows 2 and 3 where data are not supplied, starting in col K and working to the left.

 

[TOTCOM11]

What do you mean about leaving blanks where data is not supplied? When data is not supplied, the form automatically makes the cell NULL (""). I have the form populated automatically. When I try what you proposed with 10 columns, it works fine. But if I use less than 10 columns, I get #Value! errors across the entire row because Excel is looking at the entire row B6-K6...instead of just B6-E6 in an example where I would only use 4 columns. I need some assistance...

 

[Zathras]

Only you can see what is in cell K2. You say it is NULL, but is it really?

If it is a blank, then you could use this version of the formulae:
[tt]
B4: =IF(B6<>99999,RANK(B6,$B$6:$K$6,1),"")
B6: =IF(B2<>" ",B2*1000-B3,99999)
[/tt]
Play around with the compare value in the formula in cell B6 until it matches what is really going into row 2. This example tests for a single space. (B2<>" ")

If it is, as you say, "" then try this version:
[tt]
B6: =IF(B2<>"""",B2*1000-B3,99999)
[/tt]

 

[TOTCOM11]

OK, I finally got it! Get ready for this. This is what I did:

My rank row is row 4. In row B12:K12 I used the equation given to me by Glenn: =-1000*B2+B3 and copied this across the whole row. In cell B14 I used a simple =COUNT(B2:K2) to see how many columns actually had data in them. In cell B15 I typed in the following equation:

=INDEX(B10:K10,MATCH(B14,B9:K9,0))

This equation lets me find the letter of the last column that contains data. In row B9:K9 I typed in the numbers 1-10, and in row B10:K10 I typed in the letters B-K, representing the letters of the column that corresponded with the number in row 9. Then in row B4:K4 I used the following formula and copied it across:

=IF(B2<>"",RANK(B12,$B$12:INDIRECT($B$15&13),1),"")

The way this works is when I switch between data sheets by placing the sheet number in a cell I have specified. Excel then looks to see how many columns there are and finds the corresponding column letter in cell B15. It then inserts this letter into the RANK equation so it knows how many columns to look at. It took a lot of work, and I couldn't have done it without the help of Zathras and Glenn. Thanks so much guys! I really appreciate it! Finally, this thread can be put to rest!