Ranking

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With ref to the below table:

a b c d e

A 1 5 6 8 2
B 6 9 10 11 12
C 9 8 7 7 4
D 1 2 3 4 19
E 13 14 15 20 5
F 6 7 8 9 10

Which formula shd I use if I need to rank them according to col & row:

Largest: (E, d)
2nd : (D, e)
3rd : (E, c)
 
How about using CF to highlight the top 3 within the range?

Assume source range in A1:E6
Select A1:E6 (A1 active), then apply cond formatting using Formula Is:
=MATCH(A1,LARGE($A$1:$E$6,COLUMN($A:$C)),0)
Format to taste, Ok out
 
Max,

it dun work. I believ only array can.

Max said:
How about using CF to highlight the top 3 within the range?

Assume source range in A1:E6
Select A1:E6 (A1 active), then apply cond formatting using Formula Is:
=MATCH(A1,LARGE($A$1:$E$6,COLUMN($A:$C)),0)
Format to taste, Ok out
Click to expand...
 
max,

Its work..

If i have 100 over cells in the table, meaning i have to do the CF one by one?
 
Will said:
Max, Its work..

If i have 100 over cells in the table,
meaning i have to do the CF one by one?
Click to expand...

No, no .. you can do it all at one go by selecting the range (with the
active cell at the top left corner of the selected range), then apply the CF
formula which references that top left corner's active cell

If you re-read my 1st response, the step given was
Select A1:E6 (A1 active) ..
Click to expand...

Example:

If your 100 cells range to be CF'd is say, B2:F21,

Select B2:F21 (with B2 active),
then apply the CF using the formula:
=MATCH(B2,LARGE($B$2:$F$21,COLUMN($A:$C)),0)

Note that the CF formula references the active cell (B2) in the selected
range. The active cell is the cell you click on to start selecting the
range. It'll appear as "white" within the selected range.

---
 
Back
Top