formula for adding and averaging
 

I have 5 different columns entitled Sex, Age, Bed Time, Wake Up, and Total
Sleep. In the Sex column (column E) I have entered either "m" or "f" for male
or female. Four columns to the right of this (column I) is the column called
"Total Sleep." My question is I want to take the average of the cells in
Column I ("total sleep") only if in Column E ("Sex") there is an "m" present.
Can you tell me the formula to do this?

formula for adding and averaging
 

=AVERAGE(IF(E2:E500="m",I2:I500))

entered with ctrl + shift & enter


you need to specify the range unless you have Excel 2007

since you probably want to use other criteria it is best to use


=AVERAGE(IF(E2:E500=M2,I2:I500))

where M2 would be a cell (any cell that is not occupied already) where you
would put the
criteria. That way you don't need to edit the formula when changing the
criteria



--


Regards,


Peo Sjoblom

"frequency function with decimals"
ft.com wrote in message
...
I have 5 different columns entitled Sex, Age, Bed Time, Wake Up, and Total
Sleep. In the Sex column (column E) I have entered either "m" or "f" for
male
or female. Four columns to the right of this (column I) is the column
called
"Total Sleep." My question is I want to take the average of the cells in
Column I ("total sleep") only if in Column E ("Sex") there is an "m"
present.
Can you tell me the formula to do this?

formula for adding and averaging
 

One way;

=Sumif(E:E,"m",I:I)/Countif(E:E,"m")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"frequency function with decimals"
ft.com wrote in message
...
I have 5 different columns entitled Sex, Age, Bed Time, Wake Up, and Total
Sleep. In the Sex column (column E) I have entered either "m" or "f" for
male
or female. Four columns to the right of this (column I) is the column
called
"Total Sleep." My question is I want to take the average of the cells in
Column I ("total sleep") only if in Column E ("Sex") there is an "m"
present.
Can you tell me the formula to do this?