| Home |
| Search |
| Today's Posts |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I get the #div/0! when using the formula =average(d33:d48) | Excel Discussion (Misc queries) | |||
| A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
| formula average | Excel Discussion (Misc queries) | |||
| Average Formula | Excel Discussion (Misc queries) | |||
| how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions | |||
Threaded Mode
