Forum Discussion
Are CHOOSECOLUMNS() , TAKE(), DROP() etc faster than INDEX(), FILTER() to return selective columns ?
Hi, so I've made multiple worksheets in the past where when I had to return a particular columns from the spilled array. I either used INDEX() when the required column was dynamic
Eg INDEX(array#,,columns(array#)-2)
or if I knew the columns won't expand
FILTER(array#,{1,0,1,1,0}
Since my sheets are getting heavy, I wanna cease every opportunity to
micro influence the total speed. Are the new functions (take, drop,choosecolumns) faster than index,
filter method?
Should I re-do all my formulae?
SergeiBaklan Riny_van_Eekelen PeterBartholomew1
I didn't have any data to judge the answer to your question since I tend not to drive my workbooks to the point of collapse. However, I have now run a couple of test cases using Charles Williams's timing routines.
Extracting half a million values from the middle of a 1,000,000 by 1 array took ~130 ms using INDEX/SEQUENCE and ~80 ms with TAKE/DROP.
More closely related to your problem, extracting columns 2 and 5 from a 1,000,000 by 5 array took ~720 ms using INDEX/SEQUENCE and ~500 ms with CHOOSECOLS.
So, it looks as if traditional formulas take about half as long again as the newly implemented array formulas as well as the newer functions improving readability.
3 Replies
I didn't have any data to judge the answer to your question since I tend not to drive my workbooks to the point of collapse. However, I have now run a couple of test cases using Charles Williams's timing routines.
Extracting half a million values from the middle of a 1,000,000 by 1 array took ~130 ms using INDEX/SEQUENCE and ~80 ms with TAKE/DROP.
More closely related to your problem, extracting columns 2 and 5 from a 1,000,000 by 5 array took ~720 ms using INDEX/SEQUENCE and ~500 ms with CHOOSECOLS.
So, it looks as if traditional formulas take about half as long again as the newly implemented array formulas as well as the newer functions improving readability.
- Your numbers tell me that CHOOSECOLS takes less time and, therefore, is faster than INDEX/SEQUENCE. Similarly, TAKE or DROP takes less time and, therefore, is faster than INDEX/SEQUENCE.
So, from what you reported, newly implemented array formulas are faster and more readable than traditional formulas. This makes sense because the commented new functions are more specific than the more general INDEX function and, therefore, they can be more efficient. - Thanks, that helps a lot.
