Forum Discussion
Maybe Complex! Dynamic Array Query
I have an input sheet with staff salaries - and starting dates etc to pull in monthly salary costs for forecasting. But also want a 'profile' to enable a user to input a revised amount (Usually upwar...
James_Buist The attached file(s) contains a few different examples using MMULT, MAP and REDUCE-VSTACK. Hopefully one of them can be adapted to meet your needs. Cheers!
EDIT: simplified MMULT in v2 file...
James_Buist The attached file(s) contains a few different examples using MMULT, MAP and REDUCE-VSTACK. Hopefully one of them can be adapted to meet your needs. Cheers!
EDIT: simplified MMULT in v2 file...
OK! the test results. Only did on the first three in Dynamic Array Options v2
I used 800 rows x 176 cols (140800 cells). I used Manual calc and tested calc speed visually with Alt Ctrl F9. I also did the fill down test. ie how long it takes for the dynamic array to spill when the = sign is added to the formula i.e formula on or off.
Top performer (as expected) - MMULT - Under a sec to recalc. Instant spill
Second place - MAP - 2 sec recalc and over 1 sec spill delay
Last - REDUCE - 6 secs recalc - 6s spill delay
So there you have it. Excel loves Matrix Multiplication. Maths beats programming.
I used 800 rows x 176 cols (140800 cells). I used Manual calc and tested calc speed visually with Alt Ctrl F9. I also did the fill down test. ie how long it takes for the dynamic array to spill when the = sign is added to the formula i.e formula on or off.
Top performer (as expected) - MMULT - Under a sec to recalc. Instant spill
Second place - MAP - 2 sec recalc and over 1 sec spill delay
Last - REDUCE - 6 secs recalc - 6s spill delay
So there you have it. Excel loves Matrix Multiplication. Maths beats programming.
