How to obtain Averages from a list of multiple items?
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 |
Hi!
=SUMIF(A1:A50,"cat",C1:C50)/COUNTIF(A1:A50,"cat") Do the same for dog. Biff "MadameJunk" wrote in message ... 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 |
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! In article , "MadameJunk" wrote: 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 |
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 "MadameJunk" wrote: 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 |
=SUMPRODUCT(--(A1:A15="Cat"),--(B1:B15))/COUNTIF(A1:A15,"Cat")
Swap Dog for Cat, Regards, Alan. "MadameJunk" wrote in message ... 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 |
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 "MadameJunk" wrote: 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 |
All times are GMT +1. The time now is 08:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com