Average in a month/year

[PAL]

I am trying to get the average of a set of numbers if they occur in a given
month. I get the infamous #N/A. As an array.......

=AVERAGE(IF(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!$R$3:$R$4293<>"",Work!$C$2:$C$4293)))

I would also like to count the number. I would assume I could swap out
average for count.

 

[David Biddulph]

=SUMPRODUCT(--(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),--(Work!$R$3:$R$4293<>""),Work!$C$2:$C$4293)/
SUMPRODUCT(--(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),--(Work!$R$3:$R$4293<>""))

But you'll need to change the formula to make all the arrays the same
length.

 

[T. Valko]

=AVERAGE(IF(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),IF(Work!$R$3:$R$4293<>"",Work!$C$2:$C$4293)))

You have a misplaced ")" and the average range starts on row 2 while all the
other ranges start on row 3.

Try this (array entered):

I would also like to count the number.

Maybe this:

=SUMPRODUCT(--(TEXT(Work!$Q$3:$Q$4293,"mmyyyy")=TEXT(A6,"mmyyyy")),--(Work!$R$3:$R$4293<>""),--(ISNUMBER(Work!$C$3:$C$4293)))

 

[Mike H]

Hi,

The average formula, ARRAY entered

=AVERAGE(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))

and the COUNT formula., once again ARRAY entered

=COUNT(IF(TEXT(Works!Q3:Q4293,"mmyyyy")=TEXT(A6,"mmyyyy"),IF(Works!R3:R4293<>"",Works!C3:C4293)))

Mike

 

[PAL]

Not quite right. I am getting the #DIV/0 error. I know there should be data
based on the original formula I used. Which is below. I have some many
tables with multiple rows it was too cumbersone. I hope to be able to figure
out what is wrong with the formula you and T. Valko provided.


=AVERAGE(IF(Work!$Q$3:$Q$4293>=DATE(2008,11,1),IF(Work!$R$3:$R$4293<>"",IF(Work!$Q$3:$Q$4293<DATE(2008,12,1),Work!$C$3:$C$4293,""))))

 

[T. Valko]

I am getting the #DIV/0 error.

=AVERAGE(IF(Work!$Q$3:$Q$4293>=DATE(2008,11,1),IF(Work!$R$3:$R$4293<>"",IF(Work!$Q$3:$Q$4293<DATE(2008,12,1),Work!$C$3:$C$4293,""))))

There's nothing wrong with that formula.

I know there should be data

Then there's a problem with your data. Are you sure your dates are true
Excel dates? What result do you get with this formula:

=SUMPRODUCT(--(MONTH(Work!Q3:Q4293)=11),--(YEAR(Work!Q3:Q4293)=2008))

If you get 0 that means that either your dates are not true Excel dates or
there are no dates for Nov 2008.

 

[PAL]

Your sumproduct formula gives me exactly what is expected.

 

[PAL]

Got it. The format in A6 was messed up. Thanks.

 

[T. Valko]

Good deal. Thanks for the feedback!