max and min in an array

P

Phil Newman

hi, i have these two data columns

1 0.34564
2 0.4645
3 0.464
4 0.4786
5 0.47611
6 0.951
7 0.894
8 0.65785
9 0.654


I have two known numbers, 3 and 5, which relate to column 1. i want to
find out what it the minimum between the corresponding numbers in
column 2 between 3 and 5. note that 1 may not relate to A1 (or B1 C1
etc) so is not related to cell position, so if the cell position is
needed, this needs to be found.

e.g. the numbers in column two which correspond to between 3 and 5 in
column 1 are:

0.4645
0.464
0.4786
0.47611

the minimum is 0.464. the maximum is 0.4786.

how can i do this in excel, without knowing the numbers in the
columns, i.e. they are on a different sheet, and you just want to find
between 3 and 5 by entering 3 and 5 into a cell.

Thanks,

Phil
 
B

Bernard Liengme

If I understand correctly. Use
=MIN(IF((A1:A9>=3)*(A1:A9<=5),B1:B9))
and
=MAX(IF((A1:A9>=3)*(A1:A9<=5),B1:B9))
but they must be entered as array formulas with CTRL+SHIFT+ENTER; Excel will
enclose the formulas within braces {}

This non-array formula also works for MAX but the Min version will return 0
=SUMPRODUCT(MAX((A1:A9>=3)*(A1:A9<=5)*B1:B9))

best wishes
 
G

Gary''s Student

Hi Bill:

to simplify the explanation I am using more than one cell. You can combine
formulae. If you data is in A1 thru B9 then in C1 thru C5 enter:

=MATCH(3,A:A)
=MATCH(5,A:A)
="b"&C1&":b"&C2
=MAX(INDIRECT(C3))
=MIN(INDIRECT(C3))

to see:

3
5
b3:b5
0.4786
0.464


basically locate the slice of data of interest and then do min and max over
that slice.
 
T

T. Valko

Try this:

D1 = 3
D2 = 5

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SMALL(IF((B$1:B$9>=MIN(IF((A$1:A$9>=D$1)*(A$1:A$9<=D$2),B$1:B$9)))*(B$1:B$9<=MAX(IF((A$1:A$9>=D$1)*(A$1:A$9<=D$2),B$1:B$9))),B$1:B$9),ROWS($1:1))

Copy down until you get #NUM! errors.

The results will be sorted ascending.

0.464
0.4645
0.47611
0.4786
#NUM!

If you want the results in the order in which they appear in the table let
me know. The formula for that is a little more complicated than the one
above.

Biff
 
P

Phil Newman

Hi Bill:

to simplify the explanation I am using more than one cell. You can combine
formulae. If you data is in A1 thru B9 then in C1 thru C5 enter:

=MATCH(3,A:A)
=MATCH(5,A:A)
="b"&C1&":b"&C2
=MAX(INDIRECT(C3))
=MIN(INDIRECT(C3))

to see:

3
5
b3:b5
0.4786
0.464

basically locate the slice of data of interest and then do min and max over
that slice.

--
Gary''s Student
gsnu200704









- Show quoted text -
Click to expand...

Hi, I can't get your examples to work (none of the others work either,
or are suitable).

I get 0 returned as answers for max and min. the indirect method
gives a formula results as "volatile" and returns 0. I think this may
be because it is trying to display an array (3:5) in a single cell,
even though the formula is asking for max/min after.

Can you help?
 
P

Phil Newman

Hi Bill:

to simplify the explanation I am using more than one cell. You can combine
formulae. If you data is in A1 thru B9 then in C1 thru C5 enter:

=MATCH(3,A:A)
=MATCH(5,A:A)
="b"&C1&":b"&C2
=MAX(INDIRECT(C3))
=MIN(INDIRECT(C3))

to see:

3
5
b3:b5
0.4786
0.464

basically locate the slice of data of interest and then do min and max over
that slice.

--
Gary''s Student
gsnu200704









- Show quoted text -
Click to expand...

ah, no it's ok - my fault - the column data i had was text, and
ignored by max/min!

thanks very much! this one seems to work very well
 
P

Phil Newman

OK, I now need to plot these values, but excel graph won't allow me to
use INDIRECT.

column data:

1 0.35
2 0.46
3 0.46
4 0.48
5 0.48
6 0.95
7 0.89
8 0.66
9 0.65

using INDIRECT to find max/min between two specified numbers (3 and 5)

=MAX(INDIRECT("B"&MATCH(D3,A1:A9)&":B"&MATCH(D4,A1:A9)))

I now want to plot both data between 3 and 5.

How can I do this?

Phil
 
Top