Array Vs Non-Array (What are the Pros & Cons?)

i first got exposed to a non-array way of doing a formula by user Marcol. the problem with array formulas is that it slows down Excel (especially in a big file) & for basic users, it is definitely easier to press ENTER rather than CTRL + SHIFT + ENTER. While the 2nd reason is definitely valid, i'm not sure about the speed. i couldnt find any source that had such testing, so i simply assumed it's faster until i recently had the time to test the results.

In the file attached are 3 worksheets with different formulas in Column G. the test i did actually separates this 3 worksheets into 3 workbooks instead. and instead of just 5 rows each, i had 25,000 rows (A2:C25000 & G2:G25000). you just have to copy the formulas down if you want to. for convenience's sake, i shall not upload 3 workbooks of 25,000 rows, with each file around 5MB.

I used 3 methods; 1st sheet using the array formula, 2nd one using the INDEX method Marcol introduced me to, & the 3rd using a new formula in Excel 2010 called AGGREGATE
so the results are surprisely as such....

Methods	Speed of Calculation	File Size
Array	1:01 min	5410 KB
Non-Array with INDEX	1:19 min	5215 KB
Non-Array with Aggregate	0:58 min	4920 KB

so the 2nd method is actually slower than the 1st, despite it being a non-array formula. The file size though, is a little smaller. It's only the 3rd method with the new formula that actually saves in speed & size.

Comments, anyone?

It was an interesting exercise. In looking at this, I could not look at the aggregate function, and I had to delete the IFERROR() functions (so my functions actually return the error). I do not know how these changes affected my results. I also did my tests on 7500 cells rather than 25000. A few of my own comments and observations

1) the array formula version took 35, 35, and 34 seconds to calculate. The non-array version took 39, 39, and 39 seconds. Where your tests show ~33% increase in computation time, I show ~15% increase. How much of that difference is in the IFERROR() function and how much is in the smaller spreadsheet I used, I don't know.
2) Another approach that has traditionally been frowned upon because it is usually slower is the use of VBA UDF's. I decided to test that on this as well. As near as I could tell, your formulas searched column 2 for "y" then returned all the values from column 1 associated with those y's. Here's my code

Please Login or Register  to view this content.

It was quickly put together, I'm sure there are better ways to do this. But, in terms of computation speed, the results I got were interesting. When I first entered the UDF array formula, it took a couple of minutes. The first calculation event after that took 50 s. Each successive calculation event took 24, 12, 6, <1. After a handful of calculation events, the UDF version was near instantaneous. I don't know if each calculation event caused it to compile the UDF more efficiently, or exactly why it continued to get faster.
3) In the end, I am reminded of something my mentor put into one of his programs (he was an old school fortran programmer). His comment was something like, "I know that this programming structure slows down the program. But I find it also makes it easier for the programmer. Easier for the programmer means there is less chance for error [and less time spent debugging], and means that it is easier to modify the program when needed." I find myself doing the same kind of thing - make my spreadsheets as convenient for me as possible, even at the expense of computational time. Computer's are becoming pretty fast - if I spend twice as much of my time building and debugging a spreadsheet to save 30% computational time, I may never recover the extra time I spent building the spreadsheet.

Sorry to bring this one back up, but I thought of another approach to the problem.

It seems to me that the reason the UDF version is so much faster is because the UDF route needs to evaluate the "where are the y's" part of the function only once per calculation event. In the given worksheet formulas, the "where are the y's" part of the function is evaluated with each function call (7500 times in my spreadsheets). It occurred to me to try another technique that tends to be avoided - the use of a helper column. By adding a column that performs the "where are y's" function, we can eliminate all the duplication in the original approaches. In this version, D2 contains the formula =if(B2="y",1). D3 and copied down is =if(B3="y",count(d$2:D2)+1). The output column can now use a simple lookup formula to extract the data corresponding to the y's =INDEX($A$2:$A$7500,match(row()-1,$D$2:$D$7500,0)). I couldn't find my good stopwatch, but ticking "seconds" off in my head, I estimate 2-3 seconds per calculation event with this approach.