How to return min, mean, max using criteria?
 

How can I return the min, mean, and max values in one column based on a
value (criterion) in another column. For example, how can I return the
mean price (col 2)for all SWE4s (col 1)? Thanks for your guidance!

Item Price
NETAD 3 $8,985.60
SWE 4 $10,368.00
NET 2 $7,142.40
AD A4 $4,867.20
LYST 1 $5,529.60
LYST 2 $5,184.00
SWE 4 $11,059.20
NET 3 $9,216.00
NET 4 $1,209.60
NET 4 $1,152.00
IA 4 $1,324.80
SE 4 $1,198.08
LYST 2 $6,946.56


--
stevenva
------------------------------------------------------------------------
stevenva's Profile: http://www.excelforum.com/member.php...o&userid=16100
View this thread: http://www.excelforum.com/showthread...hreadid=275496

Steve,

With your criteriion in column A, and values in column B: assuming 10 rows
of data - you can increase that to match your actual data.

Mean (average):

=SUMIF(A1:A10,"SWE4",B1:B10)/COUNTIF(A1:A10,"SWE4")

Max:

Array enter (enter with Ctrl-Shift-Enter)

=MAX((A1:A10="SWE4")*B1:B10)

Min:

Array enter (enter with Ctrl-Shift-Enter)

=MIN(IF(A1:A10="SWE4",B1:B10))

HTH,
Bernie
MS Excel MVP


"stevenva" wrote in message
...

How can I return the min, mean, and max values in one column based on a
value (criterion) in another column. For example, how can I return the
mean price (col 2)for all SWE4s (col 1)? Thanks for your guidance!

Item Price
NETAD 3 $8,985.60
SWE 4 $10,368.00
NET 2 $7,142.40
AD A4 $4,867.20
LYST 1 $5,529.60
LYST 2 $5,184.00
SWE 4 $11,059.20
NET 3 $9,216.00
NET 4 $1,209.60
NET 4 $1,152.00
IA 4 $1,324.80
SE 4 $1,198.08
LYST 2 $6,946.56


--
stevenva
------------------------------------------------------------------------
stevenva's Profile:
http://www.excelforum.com/member.php...o&userid=16100
View this thread: http://www.excelforum.com/showthread...hreadid=275496