Minif / Maxif ?

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!

=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)


"Thrain" <Thrain.1zaa7m_1133310303.2349@excelforum-nospam.com> wrote in
message news:Thrain.1zaa7m_1133310303.2349@excelforum-nospam.com...
>
> 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!
>
>
> --
> Thrain
> ------------------------------------------------------------------------
> Thrain's Profile:
http://www.excelforum.com/member.php...o&userid=29192
> View this thread: http://www.excelforum.com/showthread...hreadid=489274
>

Originally Posted by 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)


"Thrain" <Thrain.1zaa7m_1133310303.2349@excelforum-nospam.com> wrote in
message news:Thrain.1zaa7m_1133310303.2349@excelforum-nospam.com...
>
> 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!
>
>
> --
> Thrain
> ------------------------------------------------------------------------
> Thrain's Profile:
http://www.excelforum.com/member.php...o&userid=29192
> View this thread: http://www.excelforum.com/showthread...hreadid=489274
>

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!!!!!

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)


"Thrain" <Thrain.1zc46m_1133395804.57@excelforum-nospam.com> wrote in
message news:Thrain.1zc46m_1133395804.57@excelforum-nospam.com...
>
> Bob Phillips Wrote:
> > =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)
> >
> >
> > "Thrain" <Thrain.1zaa7m_1133310303.2349@excelforum-nospam.com> wrote
> > in
> > message news:Thrain.1zaa7m_1133310303.2349@excelforum-nospam.com...
> > >
> > > 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!
> > >
> > >
> > > --
> > > Thrain
> > >
> > ------------------------------------------------------------------------
> > > Thrain's Profile:
> > http://www.excelforum.com/member.php...o&userid=29192
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=489274
> > >
>
>
> 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!!!!!
>
>
> --
> Thrain
> ------------------------------------------------------------------------
> Thrain's Profile:
http://www.excelforum.com/member.php...o&userid=29192
> View this thread: http://www.excelforum.com/showthread...hreadid=489274
>