min & max values
 

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 & max values
 

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 & max values
 

=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

min & max values
 

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

min & max values
 

=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