Excel help

Factual Questions
1

It would be useful for me to be able to do the two following things:

  1. Suppose there are numbers in columns B, C, D, E, and F. For each row, I’d like to be able to return whether the figure in column B is in first, second, third, fourth, or fifth place.

  2. Suppose there are numbers in columns B, C, D, E, and F, except for row 1 which contains labels. For each row, I’d like to return the contents of B1 if the number in B is highest, the contents of C1 if the number in C is highest, etc. Failing that, it would suffice to return an arbitrary result depending on which of the numbers is highest.

2

  1. : RANK may do what you want. I haven’t ever used it, so I’m not that familiar with it.

  2. MAX(range). I have used this, and it is very easy to use. You can use LOOKUP(value ref, range) to indicate what cell has the max value.

3

If that doesn’t work, you should be able to do it with IF functions.

4

RANK will do what you want.

  1. Suppose you want this result in Column G. By “first place” I assume you mean “highest value.” Column G in each row would have the formula

=RANK(B2,B2:F2)

This formula can be copied to any subsequent row.

Note that if the value in B is in a tie, it’s the place of the tie (e.g., if it’s tied for 4th, it returns 4, and no value would be ranked 5th).

  1. This is a little more tedious but not difficult. Suppose you want this result in Column H. Column H in row 2 would have the formula

=IF(RANK(B2,B2:F2)=1,B$1,IF(RANK(C2,B2:F2)=1,C$1,IF(RANK(D2,B2:F2)=1,D$1,IF(RANK(E2,B2:F2)*1,E$1,IF(RANK(F2,B2:F2)=1,F$1,“ERROR”)))))

This formula can be copied to any subsequent row.

If there is a tie, the leftmost column will be the one selected.

Note that this is nested 6 IFs deep, Excel has a limit of 7. I also added an error condition, which should never actually occur but if it does it means there is an error in the formula.

Non-numerical values in the rows will cause a #VALUE! error.
I have tested this in Excel 2003 and it works.