Average in a month/year

P

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.
 
D

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

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)))
 
M

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
 
P

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top