I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.

In article . com,
says...
I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.


Depends. On how the array formula is structured, how many of them are
there, and how the UDF is written.

It also possible -- and often is when the designer uses array formulas
solely to create overly complex formulas -- to improve the design of a
worksheet by *not* using array formulas.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Steve,

Array formulas are usually faster than UDFs.

HTH,
Bernie
MS Excel MVP


"Steve" wrote in message
ups.com...
I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.

The overhead involved in UDFs nearly always means that native functions
are faster.

See

http://www.decisionmodels.com/optspeed.htm

for tips on speeding things up.


In article . com,
"Steve" wrote:

I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.

99% chance that the array formulas will be faster than UDF's. If you have the
need for speed sumproduct formulas are a little faster than array formulas.

Here is a link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Steve" wrote:

I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.

Worksheet functions, even array worksheet functions, will invariably be
faster than a UDF.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" wrote in message
ups.com...
I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.

And you may find that a pivottable would be faster than both.



Steve wrote:

I have a spreadsheet where I use a lot of array formulas to essentially
do a bunch of "SUM IFing". I am wondering in general if this method
calculates faster than were I to create some user defined functions
instead. Thanks.

--

Dave Peterson