[thelist] Excel formula

[Luther, Ron]

Richard Bennett asked:

>>I'm trying to get a calculation done in an excel file, using formulas only,
>>The file i have is like this:
>>John	Fred		Mike		Minimum
>> 10		  30		  20		    10
>> 11		  15		  25		    10

>>[want]
>>1st		2nd		3d
>>Mike	Fred
>>John	Fred		Mike

Hi Richard,

With the data organized like that, I think you're gonna need a macro.
(If you were only ever going to have 3 people I think you could 'brute 
force' a solution ... but it wouldn't be very extendable if you needed 
this to work for 7, 15, or 30 people.)

I think the more general case would probably have you playing with the 
RANK and HLOOKUP functions.  The problem is with the HLOOKUP function - 
the names would have to be below the ranking which would more than likely 
necessitate some kind of loop.  

Here's most of a 'brute force' technique:

(1) Add three more columns ("restated John", etc. with restated values 
to take care of your 'minimum' constraint (D2 contains the minimum 
value):
=IF(A2<=$D2,"X",A2)

(2) Add three more columns (headed "John", "Mike", etc again) to put 
in the 'rankings' using:
=IF(ISNUMBER(F2), RANK(F2,$F2:$H2,3)," ")

<The rank function ignores alpha values, so you'll get the correct rank.>

(3) Now add your final three columns you'll have to string some ugly 
compound conditionals together in order to pull off the appropriate 
column header ... this is the part that won't be extensible.

HTH,

RonL.