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
------------------------------------------------------------------------
balmalik's Profile: http://www.excelforum.com/member.php...o&userid=36517
View this thread: http://www.excelforum.com/showthread...hreadid=562726

="C"&MATCH(MIN($C:$C),$C:$C,0)

will result in the cell in column c with the minimum (non blank) value

="C"&MATCH(MIN(IF(NOT(ISBLANK(C1:C1000)),IF(C1:C10 00<MIN(C1:C1000),C1:C1000))),C:C,0)

an array formula

will result in the cell in column c with the 2nd lowest (non blank)
value - note this requires a specific range


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=562726

to add

="C"&MATCH(MIN(IF(NOT(ISBLANK(C1:C1000)),IF(C1:C10 00SMALL(C1:C1000,2),C1:C1000))),C:C,0)

an array formula

will result in the cell in column c with the 3rd lowest (non blank)
value - note this requires a specific range

increment the "2" in the small function as required to get the 4th
lowest, 5th lowest, etc
__________________


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=562726

="C"&MATCH(MIN($C:$C),$C:$C,0)

That will fail if the range is specific: C5:C10.

="C"&INDEX(ROW(C5:C10),MATCH(MIN(C5:C10),C5:C10,0) )

Biff

"duane" wrote in
message ...

="C"&MATCH(MIN($C:$C),$C:$C,0)

will result in the cell in column c with the minimum (non blank) value

="C"&MATCH(MIN(IF(NOT(ISBLANK(C1:C1000)),IF(C1:C10 00<MIN(C1:C1000),C1:C1000))),C:C,0)

an array formula

will result in the cell in column c with the 2nd lowest (non blank)
value - note this requires a specific range


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=562726

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

Thanks for the reply...

Can I ask one more question? Can I specify a specific range? or a
maximum acceptable value? Thanks.


--
balmalik
------------------------------------------------------------------------
balmalik's Profile: http://www.excelforum.com/member.php...o&userid=36517
View this thread: http://www.excelforum.com/showthread...hreadid=562726

"balmalik" skrev i
en meddelelse ...

Thanks for the reply...

Can I ask one more question? Can I specify a specific range? or a
maximum acceptable value? Thanks.


--
balmalik


You're welcome.

For a specific range try this setup:
"Data" is the name of the range, which could
be D2:H100.

In e.g. J2 insert this formula:

=LARGE(Data,ROW()-ROW($J$2)+1)

$J$2 acts as a counter, so in case you start
in another cell, e.g. L6, $J$2 must be replaced
by $L$6.

Copy down to e.g. J3:J7. You now have the
6 greatest values in "Data" (including duplicates)

In K2 insert this array formula:

=ADDRESS(MAX((Data=J2)*ROW(Data)),MATCH(J2,OFFSET( Data,MAX((Data=J2)*
ROW(Data))-MIN(ROW(Data)),0,1),0)+MIN(COLUMN(Data))-1,4)

entered as one line. Finish with <Shift<Ctrl<Enter, also
if editing the formula later.

Copy K2 to K3:K7, and you have the addresses.

In case of duplicates the same address is displayed.
The address is the address of the last found duplicate
in "Data".

For minimum values use this setup:

In N2:

=SMALL(Data,ROW()-ROW($N$2)+1)


In O2:

=ADDRESS(MIN(IF((Data=N2)*(ROW(Data))<0,(Data=N2) *(ROW(Data)))),
MATCH(N2,OFFSET(Data,MIN(IF((Data=N2)*(ROW(Data))< 0,(Data=N2)*
(ROW(Data))))-MIN(ROW(Data)),0,1),0)+MIN(COLUMN(Data))-1,4)

Again as one line and finished with <Shift<Ctrl<Enter.
Duplicates as described above.


I don't understand your question about a maximum acceptable
value.

---
Regards
Leo Heuser

"Leo Heuser" skrev i en meddelelse
...
"balmalik" skrev i
en meddelelse
...

Thanks for the reply...

Can I ask one more question? Can I specify a specific range? or a
maximum acceptable value? Thanks.


--
balmalik


You're welcome.

For a specific range try this setup:
"Data" is the name of the range, which could
be D2:H100.

In e.g. J2 insert this formula:

=LARGE(Data,ROW()-ROW($J$2)+1)

$J$2 acts as a counter, so in case you start
in another cell, e.g. L6, $J$2 must be replaced
by $L$6.

Copy down to e.g. J3:J7. You now have the
6 greatest values in "Data" (including duplicates)

In K2 insert this array formula:

=ADDRESS(MAX((Data=J2)*ROW(Data)),MATCH(J2,OFFSET( Data,MAX((Data=J2)*
ROW(Data))-MIN(ROW(Data)),0,1),0)+MIN(COLUMN(Data))-1,4)

entered as one line. Finish with <Shift<Ctrl<Enter, also
if editing the formula later.

Copy K2 to K3:K7, and you have the addresses.

In case of duplicates the same address is displayed.
The address is the address of the last found duplicate
in "Data".

For minimum values use this setup:

In N2:

=SMALL(Data,ROW()-ROW($N$2)+1)


In O2:

=ADDRESS(MIN(IF((Data=N2)*(ROW(Data))<0,(Data=N2) *(ROW(Data)))),
MATCH(N2,OFFSET(Data,MIN(IF((Data=N2)*(ROW(Data))< 0,(Data=N2)*
(ROW(Data))))-MIN(ROW(Data)),0,1),0)+MIN(COLUMN(Data))-1,4)

Again as one line and finished with <Shift<Ctrl<Enter.
Duplicates as described above.


I don't understand your question about a maximum acceptable
value.

---
Regards
Leo Heuser


Of course you can use the formula from K2 in O2 instead of the
one given. We have a heat wave in Denmark right now, so naturally
I blame it on that :-)

The only difference is in the handling of duplicates. The formula
in O2 returns the address of the first found duplicate (if any), where the
formula in K2, as mentioned, returns the address of the last found
duplicate (if any). BTW they both return the first found, if duplicates are
in the same row.

Leo Heuser