Forum Discussion
Maybe Complex! Dynamic Array Query
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. Many thanks for the options. I will do some performance testing tomorrow and see if there is much difference in speed. In my case there will not be a huge number of entries - say 100 rows x 90 columns but will be interesting to see which performs best if say 5000 rows. My money is on the MMULT
James_Buist As an afterthought, here's a few more options using BYROW (see attached). I'm also betting on MMULT to take first prize, but it doesn't hurt to familiarize yourself with as many alternatives as possible... you never know when they may come in handy. Cheers!
- **bleep** you are good. I was about to try the Reduce version with what I learned form Oliver int he previous query. But you have offered 3 options. Really clever. I will play around and get my head around these. Thought there would be a matrix option but couldn't figure it out. Love all of the options
