I have two columns of figures. My objective is to select the highest figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to the top
five in the remaining column. What is the best way to tackle this? Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award both 5
pts. or split the pts. between both (or more). I'm not too bothered about this
aspect though.



--

Hi Saxman,

A couple of stages.

First in column C extract the largest from the 2 columns with the formula

=MAX(A$1,B$1) drag this down for the length of you data.

Now make a table with your ranking and points

1 25
2 20
3 15
4 10
5 5

In my example it's in I1 to J5

Now in D1 enter the formula below and drag down and it will award the points.

=IF(ISERROR(VLOOKUP(RANK(C1,C$1:C$8),I$1:J$5,2,FAL SE)),"",VLOOKUP(RANK(C1,C$1:C$8),I$1:J$5,2,FALSE))


Mike

"Saxman" wrote:


I have two columns of figures. My objective is to select the highest figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to the top
five in the remaining column. What is the best way to tackle this? Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award both 5
pts. or split the pts. between both (or more). I'm not too bothered about this
aspect though.



--

Saxman,

I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J $5,2,FALSE)),"",VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I $1:J$5,2,FALSE))

Mike

"Saxman" wrote:


I have two columns of figures. My objective is to select the highest figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to the top
five in the remaining column. What is the best way to tackle this? Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award both 5
pts. or split the pts. between both (or more). I'm not too bothered about this
aspect though.



--

Circular reference ????

"Mike H" wrote:

Saxman,

I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J $5,2,FALSE)),"",VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I $1:J$5,2,FALSE))

Mike

"Saxman" wrote:


I have two columns of figures. My objective is to select the highest figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to the top
five in the remaining column. What is the best way to tackle this? Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award both 5
pts. or split the pts. between both (or more). I'm not too bothered about this
aspect though.



--

Well a bit ovalish""

Go back to the first option

Thanls Toppers.


Mike

"Toppers" wrote:

Circular reference ????

"Mike H" wrote:

Saxman,

I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J $5,2,FALSE)),"",VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I $1:J$5,2,FALSE))

Mike

"Saxman" wrote:


I have two columns of figures. My objective is to select the highest figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to the top
five in the remaining column. What is the best way to tackle this? Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award both 5
pts. or split the pts. between both (or more). I'm not too bothered about this
aspect though.



--

Mike H wrote:


I'm having an elderly moment, you can do it in 1 formula, not two.

Create your table and put this in c1 and drag down.

=IF(ISERROR(VLOOKUP(RANK(MAX(A1:B1),C$1:C$8),I$1:J $5,2,FALSE)),"",VLOOKUP(RANK(MAX(
A1:B1),C$1:C$8),I$1:J$5,2,FALSE))

Thanks Mike, but I get a circular reference if I paste in cell C1?

Mike H wrote:

A couple of stages.

First in column C extract the largest from the 2 columns with the formula

=MAX(A$1,B$1) drag this down for the length of you data.

Now make a table with your ranking and points

1 25
2 20
3 15
4 10
5 5

In my example it's in I1 to J5

Now in D1 enter the formula below and drag down and it will award the points.

=IF(ISERROR(VLOOKUP(RANK(C1,C$1:C$8),I$1:J$5,2,FAL SE)),"",VLOOKUP(RANK(C1,C$1:C$8),
I$1:J$5,2,FALSE))

This works fine.

--

try this.Copy down
=LARGE(a:b,ROW(A1))
you may like this for the reward. copy down
=30-ROW(A1)*5
--
Don Guillett
SalesAid Software

"Saxman" wrote in message
...

I have two columns of figures. My objective is to select the highest
figure in
adjacent cells from the two columns and award points 25, 20, 15, 10, 5 to
the top
five in the remaining column. What is the best way to tackle this?
Example below.

Column A Column B
53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

Best of both columns.
53
82
71
72
68
63
52
63

The above sorted.
82 25pts.
72 20
71 15
68 10
63 5
63
53
52

As one can see above where pts. equal 63 one could rely on the sort, award
both 5
pts. or split the pts. between both (or more). I'm not too bothered about
this
aspect though.



--

Don Guillett wrote:

try this.Copy down
=LARGE(a:b,ROW(A1))

This finds the best of both columns. I need to find the best between adjacent cells
first, then sort.

you may like this for the reward. copy down
=30-ROW(A1)*5

This works fine.




--

I think this array formula (entered with ctrl+shift+enter) will do it .
Change j to a and k to b.

=LARGE(IF($J$2:$J$6$K$2:$K$6,$J$2:$J$6,$K$2:$K$6) ,ROW(A1))

--
Don Guillett
SalesAid Software

"Saxman" wrote in message
...
Don Guillett wrote:

try this.Copy down
=LARGE(a:b,ROW(A1))

This finds the best of both columns. I need to find the best between
adjacent cells
first, then sort.

you may like this for the reward. copy down
=30-ROW(A1)*5

This works fine.




--

Don Guillett wrote:

I think this array formula (entered with ctrl+shift+enter) will do it .
Change j to a and k to b.

=LARGE(IF($J$2:$J$6$K$2:$K$6,$J$2:$J$6,$K$2:$K$6) ,ROW(A1))

=LARGE(IF($A$1:$A$8$B$1:$B$8,$A$1:$A$8,$B$1:$B$8) ,ROW(A1))

It doesn't appear to select the larger number from both columns.

53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

The above give the following result below.

74
72
71
68
63
63
52
42

It should be.

82
72
71
68
63
63
53
52

--

Did you array enter as instructed?

--
Don Guillett
SalesAid Software

"Saxman" wrote in message
...
Don Guillett wrote:

I think this array formula (entered with ctrl+shift+enter) will do it .
Change j to a and k to b.

=LARGE(IF($J$2:$J$6$K$2:$K$6,$J$2:$J$6,$K$2:$K$6) ,ROW(A1))

=LARGE(IF($A$1:$A$8$B$1:$B$8,$A$1:$A$8,$B$1:$B$8) ,ROW(A1))

It doesn't appear to select the larger number from both columns.

53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

The above give the following result below.

74
72
71
68
63
63
52
42

It should be.

82
72
71
68
63
63
53
52

--

Don's formula entered in C1 and copied down to C8 gives me the result you want.

82
72
71
68
63
63
53
52

Did you remember to CTRL + SHIFT + ENTER when placing the formula in C1?

If not, you'll get the results you did get.


Gord Dibben MS Excel MVP

On Sat, 16 Jun 2007 12:59:56 -0500, "Saxman"
wrote:

Don Guillett wrote:

I think this array formula (entered with ctrl+shift+enter) will do it .
Change j to a and k to b.

=LARGE(IF($J$2:$J$6$K$2:$K$6,$J$2:$J$6,$K$2:$K$6) ,ROW(A1))

=LARGE(IF($A$1:$A$8$B$1:$B$8,$A$1:$A$8,$B$1:$B$8 ),ROW(A1))

It doesn't appear to select the larger number from both columns.

53 42
74 82
71 70
72 72
65 68
63 63
52 51
45 63

The above give the following result below.

74
72
71
68
63
63
52
42

It should be.

82
72
71
68
63
63
53
52

Don Guillett wrote:


Did you array enter as instructed?

I still get the same results.

Do I paste the formula in C1 and then CTRL + SHIFT + ENTER?


Maybe I'm doing something wrong?

--

Don Guillett wrote:


Did you array enter as instructed?

I entered it manually, did as you said and all is fine now.

Thanks a million!

--

Glad to help
--
Don Guillett
SalesAid Software

"Saxman" wrote in message
...
Don Guillett wrote:


Did you array enter as instructed?

I entered it manually, did as you said and all is fine now.

Thanks a million!

--