The native Data Table provided by Excel is useful but limited to two variables.
My version is set up for 5 independent variables and an unlimited (theoretically) number of dependent variables. 5 was an arbitrary choice; some limitation is required here.
What is a native Data Table?
MD Data Table in Action — Video
3 inputs; 2 outputs
Bonus — Video
Merged my data table values into Excel’s data table
3D Hypotenuse
Screenshots
Independent Variables — Input
qvar_0 (10 items)
qvar_1 ( 7 items)
qvar_2 ( 4 items)
Dependent Variables — Results
Dependent Variables — Details (only for clarity)
Short Tabulation
Long Tabulation
(10 x 7 x 4 = 280 items)
More Details
Selecting Results Cells
Selecting Anchor for Tabulation
Added August 2, 2021
Python code snippets — using PyXLL
A visitor asked if I would share the code. I will share a few Python snippets. My version was written for use with Python and PyXLL. I presume something like it could be written using some other Python-interface-to-Excel products such as openpyxl (although I have not used openpyxl)...
..
from pyxll import xl_macro
..
..
#my function is defined as a PyXLL macro
@xl_macro()
def sense():
"""Create a multi-dimension data table alternative for Excel."""
..
# get the Excel application object from PyXLL
ex = _xl_app()
..
# example -- getting lists
list0 = [e.Value for e in ex.Range("qvar_0")]
list1 = [e.Value for e in ex.Range("qvar_1")]
list2 = [e.Value for e in ex.Range("qvar_2")]
# et cetera
..
# example -- gathering lists
ss = [list0, list1, list2, list3, list4]
..
# example -- producing all possible combinations of elements in lists in s
range_result = []
for en, e in enumerate(list(itertools.product(*ss)), start=1):
..
# then, in a loop, for all the possible combinations -- substitute, calculate, repeat
# use ex.Calculate()
..
This sounds amazing, may I ask if you might be able to share to the code for this?
Thank you for the comment. I would rather share only snippets of code. I appended a short section labeled in red “Added August 2, 2021”