Conditional Average Array with Dates, <blanks>, 0

T

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?
 
B

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
 
T

tangomj

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

=AVERAGE(IF(Schedule!Q7:Q46=COLUMNS(Schedule!P7:p46))*(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.
 
B

Biff

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

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!P7:p46))*(Schedule!Z7:Z46>0,
Schedule!Z7:Z46))

Change this portion:

=COLUMNS(Schedule!P7:p46)

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
 
T

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
 
B

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
 

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