Results 1 to 4 of 4
Thread: When to use array formulas?

20010303, 13:47 #1
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
When to use array formulas?
If you have an Excel list of several thousand rows where column B contains formulas such as =LEFT(A2,3), you could handle it in one of two ways.
1. (the easy way) enter =LEFT(A2,3) in cell B2 and copy it down to all the cells below, or
2. select cells B2:B3000 (or whatever) and enter the array formula {=LEFT(A2:A3000,3)}.
I would have thought that #2 would use less memory, calculate quicker and result in a smaller file size. The downsides would be the reduced maintainability (the "average" Excel user has never heard of array formulas), and the fact that you can't insert rows  particularly annoying if you have different array formulas in columns C, D, etc. as well. You would have to get rid of the array formulas, insert the row(s), then recreate the array formulas.
Anyway, regarding the possible benefits listed above, from what I can tell:
(a) the array formulas use MORE memory;
( the array formulas calculate SLOWER; and
© the file size is only slightly smaller  maybe by 5%.
My conclusion at this point would therefore be that array formulas should only be used when they have to be.
Any thoughts?

20010303, 15:49 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: When to use array formulas?
My conclusion is the same. Arrays are very useful, but their recalc time impact can be such that I avoid them for calculations with a very large number of cells used in the formula argument (either a big range or many ranges). For my needs I often go with a multiple step =sumif(), and I've buried my P111 500 for twelve minute recalcs with cascaded sumif's!
John ... I float in liquid gardens
UTC 7±DS

20010305, 19:49 #3
 Join Date
 Dec 2000
 Location
 eastern Connecticut, Connecticut, USA
 Posts
 113
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: When to use array formulas?
True, you can't insert rows in the middle of the array, but there are cases where array formulas are invaluable. For example, I often have to use a 'SumProductIF' construct to calculate average salaries by month,by dept. from a long title/salary/dept listing which could have multiple counts for each title changing by month. I use an array formula construction like:
=SUM(IF(dept=something,(Month#headcount*salary)/month#headcount,0)
Once this is constructed, I can insert or delete rows withing the listing without breaking the formulas. I only have to work with a thousand rows or less so performance is no big factor.

20010305, 21:04 #4
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: When to use array formulas?
Hi,
I'd definitely agree with the timing degradation. I had a situation where I had perhaps 200300 array formulae on large vvolumes of data they were taking several minutes for a recalc. I ended up removing the formula and doing everything through VBA it takes 2 seconds now.Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.