Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averageifs & Countifs
I am trying to get the average of a multiple cell range. The criteria range
and sum range are not the same size. Does countifs work the same way regarding this? Here is the formula I am trying to rearrange to work. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07"). This one does not give me a true average because I am averaging an average. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07"). I am trying to count a number in the c - g range using a date criteria. Can you help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averageifs & Countifs
Hi Stephanie
I don't fully understand your question. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07") returns a correct value for me. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07") has an extra term that is superfluous, ,J1, =COUNTIFS($C$15:$G$350, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07") works just fine. -- Regards Roger Govier "Stephanie" wrote in message ... I am trying to get the average of a multiple cell range. The criteria range and sum range are not the same size. Does countifs work the same way regarding this? Here is the formula I am trying to rearrange to work. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07"). This one does not give me a true average because I am averaging an average. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07"). I am trying to count a number in the c - g range using a date criteria. Can you help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averageifs & Countifs
What I am trying to do is average numbers in 5 columns based on the date in
another column. Is this possible? At that point the numbers are not averaged yet. I originally couldn't get the averageifs to work because my ranges were not the same size - ie: c15:g350 and b15:b350 so I created an average of the 5 columns - ie: c15:c350 and b15:b350. This did not give me a correct value either because I was averaging something that had already been averaged. I then tried using countifs but I still can't get it to average correctly. "Roger Govier" wrote: Hi Stephanie I don't fully understand your question. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07") returns a correct value for me. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07") has an extra term that is superfluous, ,J1, =COUNTIFS($C$15:$G$350, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07") works just fine. -- Regards Roger Govier "Stephanie" wrote in message ... I am trying to get the average of a multiple cell range. The criteria range and sum range are not the same size. Does countifs work the same way regarding this? Here is the formula I am trying to rearrange to work. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07"). This one does not give me a true average because I am averaging an average. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07"). I am trying to count a number in the c - g range using a date criteria. Can you help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averageifs & Countifs
Hi Stephanie
Perhaps you could use =SUMPRODUCT((B15:B350=--"01/01/07")*(B15:B350<=--"31/01/07")*(C15:G350))/ SUMPRODUCT((B15:B350=--"01/01/07")*(B15:B350<=--"31/01/07")/5 If you are always going to be dealing with a whole month, then this can be simplified to =SUMPRODUCT((MONTH(B15:B350)=7)*(C15:G350))/ SUMPRODUCT((MONTH(B15:B350)=7)/5 -- Regards Roger Govier "Stephanie" wrote in message ... What I am trying to do is average numbers in 5 columns based on the date in another column. Is this possible? At that point the numbers are not averaged yet. I originally couldn't get the averageifs to work because my ranges were not the same size - ie: c15:g350 and b15:b350 so I created an average of the 5 columns - ie: c15:c350 and b15:b350. This did not give me a correct value either because I was averaging something that had already been averaged. I then tried using countifs but I still can't get it to average correctly. "Roger Govier" wrote: Hi Stephanie I don't fully understand your question. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07") returns a correct value for me. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07") has an extra term that is superfluous, ,J1, =COUNTIFS($C$15:$G$350, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07") works just fine. -- Regards Roger Govier "Stephanie" wrote in message ... I am trying to get the average of a multiple cell range. The criteria range and sum range are not the same size. Does countifs work the same way regarding this? Here is the formula I am trying to rearrange to work. =AVERAGEIFS(H15:H350,B15:B350, "=01/01/07", B15:B350, "<=01/31/07"). This one does not give me a true average because I am averaging an average. =COUNTIFS($C$15:$G$350, J1, $B$15:$B$350, "=01/01/07", $B$15:$B$350, "<=01/31/07"). I am trying to count a number in the c - g range using a date criteria. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs equiv in 2003 | Excel Discussion (Misc queries) | |||
Counting (Countifs) in excel2003 | Excel Worksheet Functions | |||
2 COUNTIFS | Excel Discussion (Misc queries) | |||
Multiple countifs | Excel Worksheet Functions | |||
COUNTIFs with multiple criteria | Excel Discussion (Misc queries) |