SOLVED

# Maybe Complex! Dynamic Array Query

Copper Contributor

# 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 upward) during the course of the next 2 years from the start of current year. These will be used to override the default amount on the calc sheet

Calc sheet has all  the breakdowns of everything. But I am trying now to incorporate the profile from the Input sheet into my basic monthly salary amounts. Looking for something like this. Ignore the combining with default values as this is straightforward. But I need the array of override values as below

I thought this would be quite easy but it seems not. As the periods will change from monthly to 3 monthly, one needs to sum based on dates. I have the start and end of period at the top so can do that. But the array of periods on the calc sheet does not match that on the input sheet which will just allow for 2 years of overrides, after which, simple inflation will kick in. Can't really forecast salary increases going out more than 2 year and for new starts the default value (not shown here) will be used with a starting date. So this will only be used for changes during the immediate forecast period.

A simple sumifs does actually work for a single row but I want to create a full 2D array.

=SUMIFS(Inputs!\$N\$72:\$AJ\$72,Inputs!\$N\$71#,">="&L3#,Inputs!\$N\$71#,"<"& L4#)

Tried sumproduct also and used MAP function to map the starting ending dates of the periods

=MAP(L3#,L4#,LAMBDA(x,y,SUMPRODUCT(Inputs!\$N\$72:\$AJ\$72,(Inputs!\$N\$71#>=x)*(Inputs!\$N\$71#<y))))

L3 has the array of period start dates and L4 has the period end dates (Calc sheet). \$N72:\$AJ\$72 holds the range of salaries for the profile and N71 has the header dates for the profile (Inputs sheet). This works fine as does a simpler Sumifs without the need to MAP. BUT. If I change the range of \$N72:\$AJ\$72 to contain more than one row, I get a calc error. So fine on a row by row but can't get it all in a single 2D array.

I tried ByRow in conjunction but that fails. Seems I can't combine ByRow with another lambda function say from MAP or such. I also tried using a Reduce with VSTACK. One problem I see is that the array of dates on the profile is not the same as that on the Calc sheet period start & end dates and I can't see how to align these in a clever way. Its not as easy as just mapping as the profile dates will always be in months but the Calc sheet is dynamic and the current year and forecast years can be set to 1,3,6 or 12 months. I even tried creating a THUNK for one of the lambda functions. But always get the calc error Because only sumifs actually seems to work with 2 dynamic ranges of different sizes, but I can't use an input array with Sumifs (only a range - but it also deoesnt seem to work with BYROW) and Sumproduct needs me to use a MAP function (or similar) to match the date criteria in one range (actually in 2) to the criteria range of the profile, I can't see how to avoid double nested lambdas. I was surprised that the ByRow didn't work as a wrapper around the  map and sumproduct (Or sumifs)

Anyhow, I know there are so many super smart people out there, it is probably not that hard but for me, I've run out of options to try. I even considered a matrix multiplication but couldn't get my head around that either!! I'm sure there is a solution using that.

PS. My attached sheet doesn't use the same refs as described above. I put it together to illustrate the issues. Just took a camera pic and laid it roughly on top of the cells so that the refs - starting ones on some do actually reflect the above.

5 Replies
best response confirmed by James_Buist (Copper Contributor)
Solution

# Re: 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...

# Re: Maybe Complex! Dynamic Array Query

**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

# Re: Maybe Complex! Dynamic Array Query

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

# Re: Maybe Complex! Dynamic Array Query

@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!

# Re: Maybe Complex! Dynamic Array Query

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.
1 best response

Accepted Solutions
best response confirmed by James_Buist (Copper Contributor)
Solution

# Re: 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...