[thelist] Excel formula
Luther, Ron
ron.luther at hp.com
Fri Jul 2 11:58:20 CDT 2004
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.
More information about the thelist
mailing list