Help with an array formula to average the value in a third column when the condition is TRUE in two
Posted by Tom Bauer on May 03, 2001 9:29 AM
How could I use an array formula to average the value in a third column when the condition is TRUE in two other columns.
Example:
Month Year Total
1 2001 5
1 2001 15
2 2001 8
The average of the Total for Month 1 in the Year 2001 is 10.
Or an alternative:
Date Total
1/17/2001 5
1/20/2001 15
2/5/2001 8
The average for the Total number in January of 2001 is 10.
Any ideas are appreciated.
Posted by Mark W. on May 03, 2001 9:52 AM
Alternative1: {=SUM((A2:A4=1)*(B2:B4=2001)*C2:C4)}
Alternative2: {=SUM((MONTH(A2:A4)=1)*(YEAR(A2:A4)=2001)*B2:B4)}
These are array formulas which must be entered using
Control+Shift+Enter key combination. The braces, {},
are not typed by you.
Posted by Aladin Akyurek on May 03, 2001 10:01 AM
=AVERAGE(IF((MONTH(A3:A5)=1)*(YEAR(A3:A5)=2001),(B3:B5),""))
You can also use 2 cells, one for the month condition and the other for the year condition. You can then substitute cell refs for the constants in the above formula.
Aladin
Posted by Aladin Akyurek on May 03, 2001 10:18 AM
To avoid division by zero, use the following array formula:
=IF(SUM(MONTH(A3:A5)=1)>0,AVERAGE(IF((MONTH(A3:A5)=1)*(YEAR(A3:A5)=2001),(B3:B5))),"")
Aladin
Posted by Mark W. on May 03, 2001 11:20 AM
I read too quickly too...Use Aladin's
