Scoring 3 Columns

[Manfred]

I have 3 columns of stock symbols (consisting of some 300 rows per column).
The symbols appear once in each column based on 3 separate criteria.

I wish to rank the symbols from best to worst (from top to bottom of each
column) using a separate program.

I'll be moving these symbols to an Excel spreadsheet.

But rather than averaging a symbol's ranking in the 3 columns to derive a
final score, I want to score each symbol on the basis of the lowest place
(closest to the bottom) it appears in any of the columns.

For example, if symbol abc appears in row 1 of column A, row 1 of column B
and row 5 of column C, it is to receive a lower score than symbol xyz that
appears in row 2 of column A, row 2 of column B and row 3 of column C.

Is it possible for Excel to perform such a scoring routine? If so, any
instructions concerning the placement of proper formula(e) would be
greatly appreciated.

 

[Biff]

Hi Manfred!

This can be done easily but how do you want the format?
Do you want 3 positions or the the sum of the 3 positions?
Or, the lowest position of the 3 positions?

In a separate column, say col D, starting in D2 list all
the individual symbols. In E1, F1 and G1 enter A,B and
C,respectively.

Assuming that the symbols are in the range A1:C300, enter
this formula in E2:

=MATCH($D2,A$1:A$10,0)

Now, copy across to G2 then copy down to the end of the
symbol list in col D.

This will return the positions of the symbols in the array
range. Not to be confused with the actual row number!

Now the question that arises is, how do you rank symbols
that have equal positions?

ABC - 10 2 15
XYZ - 2 15 10

I'll let you figure that out <g>

Biff

 

[Biff]

Ooops!

Assuming that the symbols are in the range A1:C300, enter
this formula in E2:
=MATCH($D2,A$1:A$10,0)

Should be:

=MATCH($D2,A$1:A$300,0)

Biff

 

[Manfred]

Biff,

This works! Thank you for responding to my inquiry.