Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with an averaging formula | Excel Discussion (Misc queries) | |||
Adding single cell in multiple worksheet then averaging | Excel Worksheet Functions | |||
Averaging with a formula | Excel Discussion (Misc queries) | |||
Averaging Array Formula | Excel Discussion (Misc queries) | |||
Adding/Averaging Times in Excel | Excel Discussion (Misc queries) |