MIN function

[David Lipetz]

As described in another post of mine, I am calculating totals based on
criteria. In this case, I'm using SUMIF to calculate totals using criteria
that is between two numbers (>=1 and <=5 for instance).

Here is my SUMIF formula:
=SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$A$336,">"&I2,Revised!$L$2:$L$336)

What I now want to do is determine the Min, Max, Median, and Average of the
cells that fall within the range specified by the parameters.

Not sure how to go about doing this...

[Peo Sjoblom]

=MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$
336))

=AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2
:$A$336))

etc


--

Regards,

Peo Sjoblom


"David Lipetz" <[email protected]> wrote in message
news:[email protected]...
> As described in another post of mine, I am calculating totals based on
> criteria. In this case, I'm using SUMIF to calculate totals using criteria
> that is between two numbers (>=1 and <=5 for instance).
>
> Here is my SUMIF formula:
>
=SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$
A$336,">"&I2,Revised!$L$2:$L$336)
>
> What I now want to do is determine the Min, Max, Median, and Average of
the
> cells that fall within the range specified by the parameters.
>
> Not sure how to go about doing this...
>
>

[David Lipetz]

Hmmm. These don't work for me.

In Sheet labelled Control, I have two columns to set parameters - column A
is the first range and column B is the second range.

A B Min Max Mean
1 5
6 10
11 15

On the Sheet labelled Verified is my data. Column A contains a number,
column L contains a dollar amount.

A ... L
4 $1525

What I am attemting to do is get the MIN, MAX, MEAN, and MODE for the values
in Verified column L where the values in Verified column A fall in the range
I specify using columns A and B on the Control sheet.

Does this make sense?

"Peo Sjoblom" <[email protected]> wrote in message
news:%23yRKd%[email protected]...
> =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$
> 336))
>
> =AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2
> :$A$336))
>
> etc
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "David Lipetz" <[email protected]> wrote in message
> news:[email protected]...
>> As described in another post of mine, I am calculating totals based on
>> criteria. In this case, I'm using SUMIF to calculate totals using
>> criteria
>> that is between two numbers (>=1 and <=5 for instance).
>>
>> Here is my SUMIF formula:
>>
> =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$
> A$336,">"&I2,Revised!$L$2:$L$336)
>>
>> What I now want to do is determine the Min, Max, Median, and Average of
> the
>> cells that fall within the range specified by the parameters.
>>
>> Not sure how to go about doing this...
>>
>>
>
>

[Peo Sjoblom]

Use the same formula but replace the range after the comma with the second
range, i.e.

=MIN(IF((RangeA>=x)*(RangeA<=y),RangeL))

entered with ctrl + shift & enter


--

Regards,

Peo Sjoblom

"David Lipetz" <[email protected]> wrote in message
news:%[email protected]...
> Hmmm. These don't work for me.
>
> In Sheet labelled Control, I have two columns to set parameters - column A
> is the first range and column B is the second range.
>
> A B Min Max Mean
> 1 5
> 6 10
> 11 15
>
> On the Sheet labelled Verified is my data. Column A contains a number,
> column L contains a dollar amount.
>
> A ... L
> 4 $1525
>
> What I am attemting to do is get the MIN, MAX, MEAN, and MODE for the
values
> in Verified column L where the values in Verified column A fall in the
range
> I specify using columns A and B on the Control sheet.
>
> Does this make sense?
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:%23yRKd%[email protected]...
> >
=MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$
> > 336))
> >
> >
=AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2
> > :$A$336))
> >
> > etc
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "David Lipetz" <[email protected]> wrote in message
> > news:[email protected]...
> >> As described in another post of mine, I am calculating totals based on
> >> criteria. In this case, I'm using SUMIF to calculate totals using
> >> criteria
> >> that is between two numbers (>=1 and <=5 for instance).
> >>
> >> Here is my SUMIF formula:
> >>
> >
=SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$
> > A$336,">"&I2,Revised!$L$2:$L$336)
> >>
> >> What I now want to do is determine the Min, Max, Median, and Average of
> > the
> >> cells that fall within the range specified by the parameters.
> >>
> >> Not sure how to go about doing this...
> >>
> >>
> >
> >
>
>

[David Lipetz]

Thanks again. I don't understand the use of the asterisk (*) in the formula.
I made your suggested correction but the result of the formula is incorrect.

The formula you provided is averaging the entire column L rather than just
average those rows whose column A falls within the set criteria (established
on sheet Control: A2 and B2)


"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Use the same formula but replace the range after the comma with the second
> range, i.e.
>
> =MIN(IF((RangeA>=x)*(RangeA<=y),RangeL))
>
> entered with ctrl + shift & enter
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "David Lipetz" <[email protected]> wrote in message
> news:%[email protected]...
>> Hmmm. These don't work for me.
>>
>> In Sheet labelled Control, I have two columns to set parameters - column
>> A
>> is the first range and column B is the second range.
>>
>> A B Min Max Mean
>> 1 5
>> 6 10
>> 11 15
>>
>> On the Sheet labelled Verified is my data. Column A contains a number,
>> column L contains a dollar amount.
>>
>> A ... L
>> 4 $1525
>>
>> What I am attemting to do is get the MIN, MAX, MEAN, and MODE for the
> values
>> in Verified column L where the values in Verified column A fall in the
> range
>> I specify using columns A and B on the Control sheet.
>>
>> Does this make sense?
>>
>> "Peo Sjoblom" <[email protected]> wrote in message
>> news:%23yRKd%[email protected]...
>> >
> =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$
>> > 336))
>> >
>> >
> =AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2
>> > :$A$336))
>> >
>> > etc
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> >
>> > "David Lipetz" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> As described in another post of mine, I am calculating totals based on
>> >> criteria. In this case, I'm using SUMIF to calculate totals using
>> >> criteria
>> >> that is between two numbers (>=1 and <=5 for instance).
>> >>
>> >> Here is my SUMIF formula:
>> >>
>> >
> =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$
>> > A$336,">"&I2,Revised!$L$2:$L$336)
>> >>
>> >> What I now want to do is determine the Min, Max, Median, and Average
>> >> of
>> > the
>> >> cells that fall within the range specified by the parameters.
>> >>
>> >> Not sure how to go about doing this...
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Peo Sjoblom]

It works believe me, did you enter it with ctrl + shift & enter?

=AVERAGE(IF((RangeA>=x)*(RangeA<=y),RangeL))


will average values in RangeL where RangeA is greater than or equal to x AND
less than or equal to y

note that it if it is not entered with ctrl + shift & enter it will average
the whole RangeL (look in help for array formulas)


--

Regards,

Peo Sjoblom

"David Lipetz" <[email protected]> wrote in message
news:[email protected]...
> Thanks again. I don't understand the use of the asterisk (*) in the
formula.
> I made your suggested correction but the result of the formula is
incorrect.
>
> The formula you provided is averaging the entire column L rather than just
> average those rows whose column A falls within the set criteria
(established
> on sheet Control: A2 and B2)
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > Use the same formula but replace the range after the comma with the
second
> > range, i.e.
> >
> > =MIN(IF((RangeA>=x)*(RangeA<=y),RangeL))
> >
> > entered with ctrl + shift & enter
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "David Lipetz" <[email protected]> wrote in message
> > news:%[email protected]...
> >> Hmmm. These don't work for me.
> >>
> >> In Sheet labelled Control, I have two columns to set parameters -
column
> >> A
> >> is the first range and column B is the second range.
> >>
> >> A B Min Max Mean
> >> 1 5
> >> 6 10
> >> 11 15
> >>
> >> On the Sheet labelled Verified is my data. Column A contains a number,
> >> column L contains a dollar amount.
> >>
> >> A ... L
> >> 4 $1525
> >>
> >> What I am attemting to do is get the MIN, MAX, MEAN, and MODE for the
> > values
> >> in Verified column L where the values in Verified column A fall in the
> > range
> >> I specify using columns A and B on the Control sheet.
> >>
> >> Does this make sense?
> >>
> >> "Peo Sjoblom" <[email protected]> wrote in message
> >> news:%23yRKd%[email protected]...
> >> >
> >
=MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$
> >> > 336))
> >> >
> >> >
> >
=AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2
> >> > :$A$336))
> >> >
> >> > etc
> >> >
> >> >
> >> > --
> >> >
> >> > Regards,
> >> >
> >> > Peo Sjoblom
> >> >
> >> >
> >> > "David Lipetz" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> As described in another post of mine, I am calculating totals based
on
> >> >> criteria. In this case, I'm using SUMIF to calculate totals using
> >> >> criteria
> >> >> that is between two numbers (>=1 and <=5 for instance).
> >> >>
> >> >> Here is my SUMIF formula:
> >> >>
> >> >
> >
=SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$
> >> > A$336,">"&I2,Revised!$L$2:$L$336)
> >> >>
> >> >> What I now want to do is determine the Min, Max, Median, and Average
> >> >> of
> >> > the
> >> >> cells that fall within the range specified by the parameters.
> >> >>
> >> >> Not sure how to go about doing this...
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[David Lipetz]

Peo - you are absolutely correct. I neglected to enter the formula as an
array. THANK YOU! Your assistance is most appreciated!

"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> It works believe me, did you enter it with ctrl + shift & enter?
>
> =AVERAGE(IF((RangeA>=x)*(RangeA<=y),RangeL))
>
>
> will average values in RangeL where RangeA is greater than or equal to x
> AND
> less than or equal to y
>
> note that it if it is not entered with ctrl + shift & enter it will
> average
> the whole RangeL (look in help for array formulas)
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "David Lipetz" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks again. I don't understand the use of the asterisk (*) in the
> formula.
>> I made your suggested correction but the result of the formula is
> incorrect.
>>
>> The formula you provided is averaging the entire column L rather than
>> just
>> average those rows whose column A falls within the set criteria
> (established
>> on sheet Control: A2 and B2)
>>
>>
>> "Peo Sjoblom" <[email protected]> wrote in message
>> news:[email protected]...
>> > Use the same formula but replace the range after the comma with the
> second
>> > range, i.e.
>> >
>> > =MIN(IF((RangeA>=x)*(RangeA<=y),RangeL))
>> >
>> > entered with ctrl + shift & enter
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> > "David Lipetz" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> Hmmm. These don't work for me.
>> >>
>> >> In Sheet labelled Control, I have two columns to set parameters -
> column
>> >> A
>> >> is the first range and column B is the second range.
>> >>
>> >> A B Min Max Mean
>> >> 1 5
>> >> 6 10
>> >> 11 15
>> >>
>> >> On the Sheet labelled Verified is my data. Column A contains a number,
>> >> column L contains a dollar amount.
>> >>
>> >> A ... L
>> >> 4 $1525
>> >>
>> >> What I am attemting to do is get the MIN, MAX, MEAN, and MODE for the
>> > values
>> >> in Verified column L where the values in Verified column A fall in the
>> > range
>> >> I specify using columns A and B on the Control sheet.
>> >>
>> >> Does this make sense?
>> >>
>> >> "Peo Sjoblom" <[email protected]> wrote in message
>> >> news:%23yRKd%[email protected]...
>> >> >
>> >
> =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$
>> >> > 336))
>> >> >
>> >> >
>> >
> =AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2
>> >> > :$A$336))
>> >> >
>> >> > etc
>> >> >
>> >> >
>> >> > --
>> >> >
>> >> > Regards,
>> >> >
>> >> > Peo Sjoblom
>> >> >
>> >> >
>> >> > "David Lipetz" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> As described in another post of mine, I am calculating totals based
> on
>> >> >> criteria. In this case, I'm using SUMIF to calculate totals using
>> >> >> criteria
>> >> >> that is between two numbers (>=1 and <=5 for instance).
>> >> >>
>> >> >> Here is my SUMIF formula:
>> >> >>
>> >> >
>> >
> =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$
>> >> > A$336,">"&I2,Revised!$L$2:$L$336)
>> >> >>
>> >> >> What I now want to do is determine the Min, Max, Median, and
>> >> >> Average
>> >> >> of
>> >> > the
>> >> >> cells that fall within the range specified by the parameters.
>> >> >>
>> >> >> Not sure how to go about doing this...
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>