I am trying to select the maximum and minimum values in an Excel 2000
spreadsheet. The guiding criteria is in column A and the selection values
are in column B. For example (ColumnA - ColumnB)

ID1220 - 5
ID1220 - 10
ID1624 - 8
ID1220 - 5
ID1624 - 16
ID1220 - 11

The output I would like is the minimum & maximum values for the ID1220
(which in this case are & 11) and the minimum & maximum values for the
ID1624 (8 & 16). Values in columns A & B will not be in any type of sorted
arrangement. I suspect I have to do an array, but am not quite sure how to
go about it.
Robert

Try these. Both are array formulas and need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

E1 = ID1220

For the MIN:

=MIN(IF(A1:A6=E1,B1:B6))

For the MAX:

=MAX(IF(A1:A6=E1,B1:B6))

Biff

"Robert Dieckmann" wrote in message
...
I am trying to select the maximum and minimum values in an Excel 2000
spreadsheet. The guiding criteria is in column A and the selection values
are in column B. For example (ColumnA - ColumnB)

ID1220 - 5
ID1220 - 10
ID1624 - 8
ID1220 - 5
ID1624 - 16
ID1220 - 11

The output I would like is the minimum & maximum values for the ID1220
(which in this case are & 11) and the minimum & maximum values for the
ID1624 (8 & 16). Values in columns A & B will not be in any type of
sorted arrangement. I suspect I have to do an array, but am not quite
sure how to go about it.
Robert

=MIN(IF($A$1:$A$6=C1,$B$1:$B$6))&" & "&MAX(($A$1:$A$6=C1)*($B$1:$B$6))

ctrl+shift+enter, not just enter
the above formula is combined min & max

"Robert Dieckmann" wrote:

I am trying to select the maximum and minimum values in an Excel 2000
spreadsheet. The guiding criteria is in column A and the selection values
are in column B. For example (ColumnA - ColumnB)

ID1220 - 5
ID1220 - 10
ID1624 - 8
ID1220 - 5
ID1624 - 16
ID1220 - 11

The output I would like is the minimum & maximum values for the ID1220
(which in this case are & 11) and the minimum & maximum values for the
ID1624 (8 & 16). Values in columns A & B will not be in any type of sorted
arrangement. I suspect I have to do an array, but am not quite sure how to
go about it.
Robert

maybe sumproduct can also do this...
say A1 is the search ID####

A2:B7 are the search range
(min & max) formula on cell say B2 [copy paste the formula below]

=SUMPRODUCT(SMALL((A2:A7=A1)*(B2:B7),1+SUM(1*(A2:A 7<A1))))&" &
"&SUMPRODUCT(MAX((A2:A7=A1)*(B2:B7)))

no need {}...maybe re-arrange to suit..

--
*****
birds of the same feather flock together..



"Robert Dieckmann" wrote:

I am trying to select the maximum and minimum values in an Excel 2000
spreadsheet. The guiding criteria is in column A and the selection values
are in column B. For example (ColumnA - ColumnB)

ID1220 - 5
ID1220 - 10
ID1624 - 8
ID1220 - 5
ID1624 - 16
ID1220 - 11

The output I would like is the minimum & maximum values for the ID1220
(which in this case are & 11) and the minimum & maximum values for the
ID1624 (8 & 16). Values in columns A & B will not be in any type of sorted
arrangement. I suspect I have to do an array, but am not quite sure how to
go about it.
Robert

=SUMPRODUCT(SMALL((A2:A7=A1)*(B2:B7),1+SUM(1*(A2: A7<A1))))

That only works if all the values are positive.

I'd be willing to bet that:

=MIN(IF(A2:A7=A1,B2:B7))

is more efficient.

Biff

"driller" wrote in message
...
maybe sumproduct can also do this...
say A1 is the search ID####

A2:B7 are the search range
(min & max) formula on cell say B2 [copy paste the formula below]

=SUMPRODUCT(SMALL((A2:A7=A1)*(B2:B7),1+SUM(1*(A2:A 7<A1))))&" &
"&SUMPRODUCT(MAX((A2:A7=A1)*(B2:B7)))

no need {}...maybe re-arrange to suit..

--
*****
birds of the same feather flock together..



"Robert Dieckmann" wrote:

I am trying to select the maximum and minimum values in an Excel 2000
spreadsheet. The guiding criteria is in column A and the selection
values
are in column B. For example (ColumnA - ColumnB)

ID1220 - 5
ID1220 - 10
ID1624 - 8
ID1220 - 5
ID1624 - 16
ID1220 - 11

The output I would like is the minimum & maximum values for the ID1220
(which in this case are & 11) and the minimum & maximum values for the
ID1624 (8 & 16). Values in columns A & B will not be in any type of
sorted
arrangement. I suspect I have to do an array, but am not quite sure how
to
go about it.
Robert