Hello guys, I have a large table with very time consuming array formula calculations with nested IF criteria. The data table (Sheet1) contains 800,000 rows and ca.100 columns and the results table (Sheet2) contains 20,000 rows. Basic calculations like MEDIAN and STDEV.P and COUNT are performed if multiple criteria apply.
For example: {=MEDIAN(IF('Sheet1'!$FC:$FC<0.5,IF('Sheet1'!$FH:$FH>4,IF('Sheet1'!$L:$L='Sheet2'!$J2,IF(ISNUMBER('Sheet1'!AC:AC),'Sheet1'!AC:AC)))))}
The problem is that calculating ten columns of 20,000 rows referencing to the 800,000 row data-table takes hours even with 100% CPU usage and 8 threads.
I already sorted both tables the same way and remove all formulas finished calculating by pasting only values after each batch of calculations is done. Still it takes ages.
Is there a way to economize this kind of formula to optimize excels calculating speed?
Thank you and best regards, S.
Bookmarks