"balmalik" skrev i
en meddelelse ...

Hi Guys,

Can you help me on how to find the location of the maximum value in a
cloulmn? I also want to find the 2nd, 3rd maximum too. Again, I want to
find its location and not the value only. I have more than a 1000 cells
in my coulmn and therefore, it's hard to find its location.

Thanks in advance for the help...


--
balmalik


Hi Balmalik

One more way.

1. Select 3 cells in a row, e.g. H2:J2
2. While the cells are selected enter the formula

=ADDRESS(MATCH(LARGE(C2:C29,{1,2,3}),C2:C29,0)+ROW (C2)-1,COLUMN(C2),4)

the formula must be finished with <Shift<Ctrl<Enter, also
if edited later.

H2:J2 now contains the addresses of the 3 highest values in C2:C29.

If for example the 4 highest numbers in the range are 51, 49, 49, 43, the 3
highest will be 51, 49, 49 and not 51, 49, 43.

If you want the 4 highest, select 4 cells and replace {1,2,3} by {1,2,3,4}
etc.

To find the minimum values replace LARGE by SMALL


--
Best regards
Leo Heuser

Followup to newsgroup only please.