Forum Discussion
Are CHOOSECOLUMNS() , TAKE(), DROP() etc faster than INDEX(), FILTER() to return selective columns ?
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.
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.
