Conditional Average Array with Dates, <blanks, 0
 

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?


--
tangomj
------------------------------------------------------------------------
tangomj's Profile: http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

Hi!

One way:

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

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

Copy across as needed.

Biff

"tangomj" wrote in
message ...

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?


--
tangomj
------------------------------------------------------------------------
tangomj's Profile:
http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

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

=AVERAGE(IF(Schedule!Q7:Q46=COLUMNS(Schedule!P7:P4 6))*(Schedule!Z7:Z460,
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.


--
tangomj
------------------------------------------------------------------------
tangomj's Profile: http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

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!P7:P 46))*(Schedule!Z7:Z460,
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

"tangomj" wrote in
message ...

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

=AVERAGE(IF(Schedule!Q7:Q46=COLUMNS(Schedule!P7:P4 6))*(Schedule!Z7:Z460,
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.


--
tangomj
------------------------------------------------------------------------
tangomj's Profile:
http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

Thank you. The following formula works:

{=AVERAGE(IF((Schedule!$Q7:$Q46=COLUMNS($E:E))*(Sc hedule!$Z7:$Z460),
Schedule!$Z7:$Z46))}

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


--
tangomj
------------------------------------------------------------------------
tangomj's Profile: http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

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:$Z460),schedule!$Z7:$Z46)),"" )

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

Biff

"tangomj" wrote in
message ...

Thank you. The following formula works:

{=AVERAGE(IF((Schedule!$Q7:$Q46=COLUMNS($E:E))*(Sc hedule!$Z7:$Z460),
Schedule!$Z7:$Z46))}

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


--
tangomj
------------------------------------------------------------------------
tangomj's Profile:
http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

Thanks! It worked nicely.


--
tangomj
------------------------------------------------------------------------
tangomj's Profile: http://www.excelforum.com/member.php...o&userid=36314
View this thread: http://www.excelforum.com/showthread...hreadid=560957

Conditional Average Array with Dates, <blanks, 0
 

You're welcome!

Biff

"tangomj" wrote in
message ...

Thanks! It worked nicely.


--
tangomj