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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Will" wrote:
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)

Max,

it dun work. I believ only array can.

"Max" wrote:

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Will" wrote:
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)

"Will" wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

max,

Its work..

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

"Max" wrote:

"Will" wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Will" wrote
Max, Its work..

Yes, of course it does <g
I tested it before posting.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

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

how do i make a cell active?

"Max" wrote:

"Will" wrote
Max, Its work..

Yes, of course it does <g
I tested it before posting.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

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

"Max" wrote:

"Will" wrote
Max, Its work..

Yes, of course it does <g
I tested it before posting.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Will" wrote
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.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

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

"Will" wrote:

Max,

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

"Max" wrote:

"Will" wrote
Max, Its work..

Yes, of course it does <g
I tested it before posting.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Will" wrote
Max,
I manage to fig out... THanks so much...

whew, am I glad to hear that! You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---