show highest and lowest figure in a range

[nigelog]

Hi
how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003. Any help appreciated. Rgds Nigel

[sweep]

Hi,

If your data is in A1:A10,

=LARGE(A1:A10,1)

and

=SMALL(A1:A10,1)

[nigelog]

Hi Dave

I did not think it all through before I asked. Yes thanks the formula can highlight the highest and lowest figures in say C3:C56, but when it displays this can it pick up a name in say A3:A56 and display it alonside the highest/lowest figure. Would help if I can find a way. Rgds Nigel

[xld]

=max(a:a)
=min(a:a)

[xld]

Try

=INDEX(A3:A56,MATCH(MAX(C3:C56),C3:C56,0))

[nigelog]

Brilliant xld, many thanks