Average in a month/year
 

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.

Average in a month/year
 

=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.
--
David Biddulph

"PAL" wrote in message
...
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.

Average in a month/year
 

=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):

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

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

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

Average in a month/year
 

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,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

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.

Average in a month/year
 

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(20 08,12,1),Work!$C$3:$C$4293,""))))



"Mike H" wrote:

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,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

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.

Average in a month/year
 

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(2 008,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.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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(20 08,12,1),Work!$C$3:$C$4293,""))))



"Mike H" wrote:

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,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

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.

Average in a month/year
 

Your sumproduct formula gives me exactly what is expected.

"T. Valko" wrote:

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(2 008,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.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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(20 08,12,1),Work!$C$3:$C$4293,""))))



"Mike H" wrote:

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,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

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.


.

Average in a month/year
 

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

"T. Valko" wrote:

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(2 008,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.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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(20 08,12,1),Work!$C$3:$C$4293,""))))



"Mike H" wrote:

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,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

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.


.

Average in a month/year
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Got it. The format in A6 was messed up. Thanks.

"T. Valko" wrote:

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(2 008,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.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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(20 08,12,1),Work!$C$3:$C$4293,""))))



"Mike H" wrote:

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,"m myyyy"),IF(Works!R3:R4293<"",Works!C3:C4293)))

Mike



"PAL" wrote:

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.


.