Forum Discussion
Matrix with Three Axes
If all you require is to collect data then a common practice is simply to repeat the block of data.
The spreadsheet is probably better than any other software for representing such data.
It gets complicated if you want to perform calculations based upon data held in such formats. The methods I showed were aimed at supporting such calculation and demonstrated new functionality emerging within Excel 365.
The other strategy for storing data is to place it within a single column and then add fields to describe the remaining attributes. Such database tables form a good basis for subsequent calculation.
Spreadsheets are unusual in that they natively support 2D arrays. Most program environments only support 1D arrays in memory and 2,3,4...D arrays are mapped. The formulas for this have general expressions Row- and column-major order - Wikipedia.
In Excel the normal representation is to create repeating blocks of data (though it is also possible to place the blocks on different sheets).
The latest versions of Excel 365 will allow the developer to define Lambda functions to address any given element of the 3D array as a function of 3 variables. Hidden within such a function is an INDEX function operating over 1D or 2D with indices derived from the 3 dimensions and stride lengths across blocks.
= MY_ARRAY(event, tipping_point, something_else)
The Lambda function would be defined to be something of the form
= LAMBDA(array,r,c,b,
INDEX(array, r, c + (b-1)*n)
)(Data, 1, 2, 3)Only the INDEX function is relevant if you do not use Excel 365.
Is their a video out there that explains how to use the lambda function for a 3d array?