Forum Discussion
Iterating a lambda function over an array
If you are new to Excel, you most certainly have not entered at the ground floor!
You appear to have a vertical array of matrices, each 4x4 with a column header above and a row header to the right. The best approach is probably to treat the entire gold coloured range containing the matrices as a single entity and address each matrix by index, rather than a cell reference.
The stride length from matrix to matrix is 6 so either the rows to jump in DROP would be
(elementIndex-1)*6
or, if you do not have TAKE and DROP, you could still use OFFSET but relative to the top-left gold cell. With the modern functions, you would use TAKE to pick up the 4 rows of the local stiffness matrix, otherwise declare the height and width of the element matrix to be 4 in OFFSET.
The overall formula would use MAP to pick each element in turn to return the value from each and return the SUM of the values it looks up.
I have added a simpler variant of the problem at the start of my post. I tried a few different versions of a MAP formula that might yield results, to no success thus far. I suspect I need to express 'm' (my matrix parameter) as some type of array, but online I've only found various curly bracket expressions that I think might be applicable to older versions of Excel (I use 365). Thanks again for your time.