Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with DATE data type | New Users to Excel | |||
Extracting data from the current date | Excel Worksheet Functions | |||
extract date from the most current date | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Count data entries and date problem | Excel Worksheet Functions |