min & max values

  • Thread starter Thread starter Robert Dieckmann
  • Start date Start date
R

Robert Dieckmann

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
 
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:A7<>A1))))&" &
"&SUMPRODUCT(MAX((A2:A7=A1)*(B2:B7)))

no need {}...maybe re-arrange to suit..
 
=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 said:
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:A7<>A1))))&" &
"&SUMPRODUCT(MAX((A2:A7=A1)*(B2:B7)))

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

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



Robert Dieckmann said:
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
Click to expand...
Click to expand...
 
Back
Top