How to obtain Averages from a list of multiple items?

[Guest]

Example:Excel worksheet-Column A has list of Cat or Dog (say 15 items)-Column
C has their age in days old (10,3,4,etc)
I need (2) Averages- Average age of Cats and Average Age of Dogs

 

[Biff]

Hi!

=SUMIF(A1:A50,"cat",C1:C50)/COUNTIF(A1:A50,"cat")

Do the same for dog.

Biff

 

[Domenic]

Try...

=AVERAGE(IF($A$1:$A$15="Dog",$C$1:$C$15))

or

=AVERAGE(IF($A$1:$A$15=F1,$C$1:$C$15))

....where F1 contains your criterion, such as 'Dog'. Both formulas need
to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

Hi,

Use the formulas:

=SUMPRODUCT(--($A$1:$A$100="cat")*($C$1:$C$100))/SUMPRODUCT(--(A1:A100="cat"))

=SUMPRODUCT(--($A$1:$A$100="dog")*($C$1:$C$100))/SUMPRODUCT(--(A1:A100="dog"))

Regards,
B. R. Ramachandan

 

[Alan]

=SUMPRODUCT(--(A1:A15="Cat"),--(B1:B15))/COUNTIF(A1:A15,"Cat")
Swap Dog for Cat,
Regards,
Alan.

 

[Guest]

OK.
=COUNTIF(A1:A15,"cat") will give you the number of cats
=COUNTIF(A1:A15,"dog") will give you the number of dogs
=SUMIF(A1:A15,"cat",C1:C15) is the total age of cats
=SUMIF(A1:A15,"dog",C1:C15) is the total age of dogs
then divide total ages by numbers of cats or dogs