Conditional Average Array with Dates, <blanks>, 0

[tangomj]

I have the following table:

A B C E F G
H
Dates Month Age Jan Feb Mar Apr
1/3/2006 1 11 Average 33 20 47 59
1/26/2006 1 55
1/3/2006 1 <blank>
2/4/2006 2 20
3/6/2006 3 0
3/5/2006 3 47
4/2/2006 4 50
4/13/2006 4 0
4/23/2006 4 67

I'm looking for a formula that will calculate the average age for each
month excluding <blanks> and 0 value cells. I know I can use the
SUMPRODUCT function but how do I formulate the exclusions in each
array? Any ideas?

 

[Biff]

Hi!

One way:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=AVERAGE(IF(($B2:$B10=COLUMNS($A:A))*($C2:$C10>0),$C2:$C10))

Copy across as needed.

Biff

 

[tangomj]

Thanks for the reply but still not working. I get an error with the
following:

=AVERAGE(IF(Schedule!Q7:Q46=COLUMNS(Schedule!P746))*(Schedule!Z7:Z46>0,
Schedule!Z7:Z46))

Maybe I don't need to use the "Dates" P column but only the "Month" Q
column? The Z column contains the Age values.

 

[Biff]

Maybe I don't need to use the "Dates" P column but only the "Month" Q

column?

That's what I did, used the month column. It eliminates the need for an
additional function call.

=AVERAGE(IF(Schedule!Q7:Q46=COLUMNS(Schedule!P746))*(Schedule!Z7:Z46>0,
Schedule!Z7:Z46))

Change this portion:

=COLUMNS(Schedule!P746)

To:

=COLUMNS($A:A)

What that is doing is, as you copy across, the column number returned will
increment corresponding to the month:

=COLUMNS($A:A) = 1 corresponding to Jan
=COLUMNS($A:B) = 2 corresponding to Feb
=COLUMNS($A:C) = 3 corresponding to Mar
etc

Also, make sure you enter the formula as an array.

Biff

 

[tangomj]

Thank you. The following formula works:

{=AVERAGE(IF((Schedule!$Q7:$Q46=COLUMNS($E:E))*(Schedule!$Z7:$Z46>0)
Schedule!$Z7:$Z46))}

But I'm getting the #DIV/0! error on the Months which I do not have an
data. How can I default the average to a "0" or <blank> if no data i
available for that month, instead of the #DIV/0! error? Please provid
two solutions for "0" or <blank> results. Thx

 

[Biff]

Try this:

Still array entered:

To return a blank:

=IF(SUMIF(schedule!$Q7:$Q46,COLUMNS($A:A),schedule!$Z7:$Z46),AVERAGE(IF((schedule!$Q7:$Q46=COLUMNS($A:A))*(schedule!$Z7:$Z46>0),schedule!$Z7:$Z46)),"")

To return 0 just replace the double quotes "" with a zero.

Biff

 

[tangomj]

Thanks! It worked nicely

 

[Biff]

You're welcome!

Biff