AVERAGE formula. PLEASE HELP!!
 

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.

AVERAGE formula. PLEASE HELP!!
 

=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

FPJ wrote:
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.

AVERAGE formula. PLEASE HELP!!
 

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

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

--
Regards,
Tom Ogilvy


"FPJ" wrote:

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.

AVERAGE formula. PLEASE HELP!!
 

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

Tom Ogilvy wrote:
=Average(if(isnumber(B1:B100),B1:B100)*(Month(A1:A 100)=7))

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

--
Regards,
Tom Ogilvy


"FPJ" wrote:

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.

AVERAGE formula. PLEASE HELP!!
 

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

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

AVERAGE formula. PLEASE HELP!!
 

Yeah, use the AVERAGE array formula.

--
HTH

Bob Phillips

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

"Die_Another_Day" wrote in message
ups.com...
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

Tom Ogilvy wrote:
=Average(if(isnumber(B1:B100),B1:B100)*(Month(A1:A 100)=7))

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

--
Regards,
Tom Ogilvy


"FPJ" wrote:

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.

AVERAGE formula. PLEASE HELP!!
 

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

"Die_Another_Day" wrote:

=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

FPJ wrote:
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.

AVERAGE formula. PLEASE HELP!!
 

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.


--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

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

Tom Ogilvy wrote:
=Average(if(isnumber(B1:B100),B1:B100)*(Month(A1:A 100)=7))

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

--
Regards,
Tom Ogilvy


"FPJ" wrote:

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.

AVERAGE formula. PLEASE HELP!!
 

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)

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


--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

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

Tom Ogilvy wrote:
=Average(if(isnumber(B1:B100),B1:B100)*(Month(A1:A 100)=7))

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

--
Regards,
Tom Ogilvy


"FPJ" wrote:

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.

AVERAGE formula. PLEASE HELP!!
 

Tx for the info guys.

Charles

Bob Phillips wrote:
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)

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


--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

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

Tom Ogilvy wrote:
=Average(if(isnumber(B1:B100),B1:B100)*(Month(A1:A 100)=7))

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

--
Regards,
Tom Ogilvy


"FPJ" wrote:

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.