Ranking

[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)

 

[Guest]

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

 

[Guest]

Max,

it dun work. I believ only array can.

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

 

[Guest]

it dun work. I believ only array can.

It works fine as suggested. Either you didn't try it (did you?) or you
applied it incorrectly.

Attached is a sample which evidences the suggestion works:
http://www.savefile.com/files/628657
CF to highlight top 3.xls


---

 

[Guest]

max,

Its work..

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

 

[Max]

Max, Its work..

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

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) ..

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.

---

 

[Guest]

Max,

Sorry... wat do u mean by "active"

how do i make a cell active?

 

[Guest]

Max,

How do i make a cell active? wat do u mean by A1 Active?

 

[Max]

How do i make a cell active?
wat do u mean by A1 Active?

Re my last response:

.. The active cell is the cell you click on to start selecting the range.
It'll appear as "white" within the selected range.

---

 

[Guest]

Max,

I manage to fig out... THanks so much...

Max,

How do i make a cell active? wat do u mean by A1 Active?

 

[Max]

Max,
I manage to fig out... THanks so much...

whew, am I glad to hear that! You're welcome.

---