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

 

[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]

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

 

[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]

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)

 

[David Lipetz]

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