Averaging values in column A when certain criteria are met in colu

V

VichyB

I have values in column Y, and a number of different names in column C. I
would like to create an average of the Y values for a select group of the
names from C. I have tried the following two ways and get a #DIV/0! error
both times. Any help is appreciated!

{=AVERAGE(IF(($C$2:$C$60000="Jane")*($C$2:$C$60000="John")*($C$2:$C$60000="Joan")*($C$2:$C$60000="Jean"),$Y$2:$Y$60000))}

{=AVERAGE(IF($C$2:$C$60000="Jane",(IF($C$2:$C$60000="John",(IF($C$2:$C$60000="Joan",(IF($C$2:$C$60000="Jean",(IF($Y$2:$Y$60000))))))))))}
 
J

Jacob Skaria

Try

(with blank entries in Y for mathcing records)
=AVERAGE(IF($C$2:$C$60000={"Jane","John","Joan","Jean"},$Y$2:$Y$60000))

(excluding blanks
=AVERAGE(IF(($C$2:$C$60000={"Jane","John","Joan","Jean"})*($I$2:$I$60000<>""),$Y$2:$Y$60000))

If this post helps click Yes
 
J

Jacob Skaria

Please note that these are array formulas. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

If this post helps click Yes
 
V

VichyB

that did it! thanks so much.

Jacob Skaria said:
Try

(with blank entries in Y for mathcing records)
=AVERAGE(IF($C$2:$C$60000={"Jane","John","Joan","Jean"},$Y$2:$Y$60000))

(excluding blanks)
=AVERAGE(IF(($C$2:$C$60000={"Jane","John","Joan","Jean"})*($I$2:$I$60000<>""),$Y$2:$Y$60000))

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top