Highest Number Offsets

[Alec Kolundzic]

Can anyone help;

I'm trying to find the highest number in a column, then
copy that number and the data in the cells of the next
two rows into three adjacent cells.

=ADDRESS(MATCH(MAX(F:F),F:F,0),COLUMN(F1),4)

The above function gives the address of the highest
number in column F. but when I combine the above function
with the OFFSET function, as shown below, I get errors.

Any ideas

=OFFSET((ADDRESS(MATCH(MAX(F:F),F:F,0),COLUMN
(F1),4)),0,1,1,1)

 

[Andy Brown]

Any ideas

You could try 3 sep formulas ...

=MAX(F:F)
=INDEX(F:F,MATCH(MAX(F:F),F:F)+1,1)
=INDEX(F:F,MATCH(MAX(F:F),F:F)+2,1)

HTH,
Andy

 

[Alec Kolundzic]

Thanks Andy

F G H
7 6 3
8 7 2
79 8 1
10 9 0
34 10 -1
12 11 -2
13 12 -3

From the above three columns, the three function give the
following results:
79 0 0
and not:
79 8 1

Any ideas

 

[Andy Brown]

From the above three columns, the three function give the

following results:
79 0 0
and not:
79 8 1

Any ideas

I semi-agonised over whether to ask if you *really* meant "data in the cells
of the next two *rows*" ...

=MAX(F:F)
=INDEX(G:G,MATCH(MAX($F:$F),$F:$F,0),1)
=INDEX(H:H,MATCH(MAX($F:$F),$F:$F,0),1)

Rgds,
Andy

 

[Alec Kolundzic]

Thanks Andy
That works a treat.

-----Original Message-----

I semi-agonised over whether to ask if you *really* meant "data in the cells
of the next two *rows*" ...

=MAX(F:F)
=INDEX(G:G,MATCH(MAX($F:$F),$F:$F,0),1)
=INDEX(H:H,MATCH(MAX($F:$F),$F:$F,0),1)

Rgds,
Andy


.