Forum Discussion
Matrix with Three Axes
I would like to create a spreadsheet that allows me to matrix three data sets (3 dimensional capability).
A two-dimensional matrix is easy. It has a horizontal axis and a vertical axis.
>> Is there any way in Excel to add a third axis (or dimension)?
If NO . . . >>is there some other application out there that would allow me to do this?
THANKS.
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.
13 Replies
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.
I LIKE your solution model. I think I might be able to use something like that to accomplish my goal.
I don't need to do ANY calculations so I think simply constructing the layout that way can work.
THANKS. !!!
- Looks like all of us were over complicating things except for PeterBartholomew1 🙂
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?
I did describe how a 4D array might arise and show the use of some lambda functions to reference elements of such an array, as well as transforming the array and summing by row or column. I used matrix multiplication to perform the summations but that is out of date now as new helper functions have appeared in Excel.
Announcing LAMBDA (microsoft.com)
https://www.youtube.com/watch?v=r4DD9PeVtmA
Announcing LAMBDA Helper Functions (microsoft.com)
An alternative to holding data as an array, it is also possible to use a data table with attributes representing the indices each in its own field. The data tends to be manipulated with Pivot tables or PowerQuery.
Pretty much any scripting or programming language will also handle arrays, The best one tends to be the one you know!
- What exactly do you need to do with that data?
You can enter 3D data in a sheet, all it requires is three columns, doesn't it?Hello there Jan,
I've attached a model for what I'm trying to develope.
Actually it's the first TWO dimensions of a 3D matrix I'd like to create.
You will see I've created sort of an X and Y axis in the spread sheet.
I use the gray cells to allow me to put an "X" wherever there is an intersection between items on each axis.
What is missing is the third axis. I need a way to be able to identify a third set of items -- and then be able to put an "X" where those items on the 3rd axis intersect with axis 1 and axis 2.
I don't now if this is possible.
>> Please share your thoughts. OR... another tool other than Excel ???
thanks.
- What does this third dimension look like? If there's a limited # of choices in each current column you could consider having multiple "sub-columns" for each of the current columns? This looks like a survey, is it?
