Maximum & Minimum values in a coulmn

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

>="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" <[email protected]> wrote in
message news:[email protected]...
>
> ="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:C1000<>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" <[email protected]> skrev i
en meddelelse news:[email protected]...
>
> 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" <[email protected]> skrev i
en meddelelse news:[email protected]...
>
> 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" <[email protected]> skrev i en meddelelse
news:%[email protected]...
> "balmalik" <[email protected]> skrev i
> en meddelelse
> news:[email protected]...
>>
>> 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