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.
|