AVERAGE formula. PLEASE HELP!!

[Guest]

I know this is very simple problem to most, if not all, of you here but I am
trying to figure out how to create the formula to get the average of column B
of reports created in a certain month (ex. July). Please note that some cells
in column B have the #VALUE! and the dates in column A are in the x/x/xxxx
format. Please help. Thanks.

 

[Die_Another_Day]

=AVERAGE(IF(MONTH(A1:A1000)=7,IF(ISNUMBER(B1:B1000),B1:B1000,""),""))
Then Press Ctrl+Shift+Enter as this is an array formula

Here is the breakdown on this formula:
Average IF the Month of Column A = 7 (July) and If the same row in
column B is a number.

HTH

Charles Chickering

 

[Guest]

=Average(if(isnumber(B1:B100),B1:B100)*(Month(A1:A100)=7))

entered with Ctrl+Shift+enter rather than just enter.

 

[Die_Another_Day]

Tom, In attempt to improve my knowledge of the "SumProduct" function I
attempted to do this using it. Any suggestions on how to shorten this?
=SUMPRODUCT(--IF(ISNUMBER(B1:B37),B1:B37)*--(MONTH(A1:A37)=7))/SUMPRODUCT(--IF(ISNUMBER(B1:B37),1)*--(MONTH(A1:A37)=7))

Thanks,

Charles Chickering

P.S. FPJ this formula does not require the Ctrl+Shift+Enter

 

[Bob Phillips]

=AVERAGE(IF((ISNUMBER(B1:B100))*(MONTH(A1:A100)=7),B1:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

Yeah, use the AVERAGE array formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

You are all great. I need just one formula and this works just fine. Thank
you ALL!! I really appreciate it.

 

[Guest]

Sumproduct is a convenient way to perform array formula analysis for those
array formula problems that fit the paradigm of Sumproduct. It isn't a magic
elixer. In this case, there is no reason to use a more verbose sumproduct
over the average formula. Now I haven't used FAST Excel to do timing tests
so my statement is made in general without quantitative certification - but
for the majority of uses, I think they are sufficiently equal in performance.

 

[Bob Phillips]

As Tom says, an array formula and SP would be roughly equivalent in
performance ... if you were comparing like for like. But by trying to
calculate an average with SP in this manner you perform the bulk of the
calculation twice, once for the divisor, once for the dividend, which makes
it far more inefficient. My timings show it is nearly twice as slow, as
would be expected. The array formula here not only is more obvious (because
it states AVERAGE), but is twice as quick.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Die_Another_Day]

Tx for the info guys.

Charles

As Tom says, an array formula and SP would be roughly equivalent in
performance ... if you were comparing like for like. But by trying to
calculate an average with SP in this manner you perform the bulk of the
calculation twice, once for the divisor, once for the dividend, which makes
it far more inefficient. My timings show it is nearly twice as slow, as
would be expected. The array formula here not only is more obvious (because
it states AVERAGE), but is twice as quick.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)