Minif / Maxif ?

T

Thrain

value category
0.48 1
0.64 1
0.70 1
0.74 1
0.76 1
0.45 2
.....

I want to return the MIX or MAX of value for category 1, then category
2, etc. The output must be displayed in a seperate work worksheet like
this:

category min max
1 XX XX
2 XX XX

XX represents what I am trying to solve for. Any help? All replies are
greatly appreciated. thx!
 
B

Bob Phillips

=MIN(IF(Sheet1!$B$2:$B$200=$A2,Sheet2!$A$2:$A$200))

which is an array formula, so commit with Ctrl-Shift-Energy, and thenj

=MAX(IF(Sheet1!$B$2:$B$200=$A2,Sheet2!$A$2:$A$200))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Thrain

Bob said:
=MIN(IF(Sheet1!$B$2:$B$200=$A2,Sheet2!$A$2:$A$200))

which is an array formula, so commit with Ctrl-Shift-Energy, and thenj

=MAX(IF(Sheet1!$B$2:$B$200=$A2,Sheet2!$A$2:$A$200))

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


in
message news:[email protected]...
Click to expand...


Thank you very much! This worked perfectly. One last question, is there
a way to avoid setting a limit on rows? Currently, you have 2 to 200
used in the formula and the amount of rows in my table could grow. I am
not familiar with using the $ to refence cells, so I do not know how to
do this.

Thank you!!!!!
 
B

Bob Phillips

You can't use whole columns because it is an array formula, but you can get
close

=MIN(IF(Sheet1!$B$2:$B$65535=Sheet2!$A2,$A$2:$A$65535))

still an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top