Date X Data
I have,
A B 1 5/1/2005 2 5/2/2005 3 5/3/2005 4 6/1/2005 5 6/2/2005 6 6/3/2005 7 7/1/2005 8 7/2/2005 9 7/3/2005 10 7/4/2005 I want the Average of the different months, May for example, why this: =SUMPRODUCT(SUBTOTAL(1,A1:A10)*(MONTH(B1:B10)=5)*( YEAR(B1:B10)=2005)) is giving me 16.5 ? TXS |
You can use array entered:
=AVERAGE(IF(MONTH(B1:B6)=5,A1:A6)) (enter with ctrl-shift-enter at once) or if you want to use sumproduct, one way is: =SUMPRODUCT((MONTH(B1:B6)=5)*(A1:A6))/SUMPRODUCT(--(MONTH(B1:B6)=5)) which does not need to be array entered. "Farrel" wrote in message ... I have, A B 1 5/1/2005 2 5/2/2005 3 5/3/2005 4 6/1/2005 5 6/2/2005 6 6/3/2005 7 7/1/2005 8 7/2/2005 9 7/3/2005 10 7/4/2005 I want the Average of the different months, May for example, why this: =SUMPRODUCT(SUBTOTAL(1,A1:A10)*(MONTH(B1:B10)=5)*( YEAR(B1:B10)=2005)) is giving me 16.5 ? TXS |
the subtotal (1... will give you 5.5 effectively as a constant
you have three times when the rest of the equaiton is met, therefore you are adding three 5.5 to give you 16.5 to use sumproduct to get the average =SUMPRODUCT(A1:A10*(MONTH(B1:B10)=5)*(YEAR(B1:B10) =2005))/ =SUMPRODUCT((MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005)) "Farrel" wrote: I have, A B 1 5/1/2005 2 5/2/2005 3 5/3/2005 4 6/1/2005 5 6/2/2005 6 6/3/2005 7 7/1/2005 8 7/2/2005 9 7/3/2005 10 7/4/2005 I want the Average of the different months, May for example, why this: =SUMPRODUCT(SUBTOTAL(1,A1:A10)*(MONTH(B1:B10)=5)*( YEAR(B1:B10)=2005)) is giving me 16.5 ? TXS |
All times are GMT +1. The time now is 01:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com