Maximum & Minimum values in a coulmn

B

balmalik

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

duane

="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
 
D

duane

to add

="C"&MATCH(MIN(IF(NOT(ISBLANK(C1:C1000)),IF(C1:C1000>SMALL(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
__________________
 
B

Biff

="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
 
L

Leo Heuser

balmalik said:
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...
Click to expand...


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
 
B

balmalik

Thanks for the reply...

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

Leo Heuser

balmalik said:
Thanks for the reply...

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


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

Leo Heuser

Leo Heuser said:
"balmalik" <[email protected]> skrev i
en meddelelse



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.
Click to expand...

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
 
Top