Forum Discussion
Formula that output values of all possible permutations
Most of this calculation just melted away.
It turned out that each term of the 2x2 matrix involved the product of calculation 1 and calculation 2 (since they are the sum and difference of two lookups respectively, their product is equivalent to the difference of the squares of the values looked up)
Taking the product out as a factor of the matrix left a constant matrix that applies to every data row.
= Calc1 * Calc2 * Weighting
For me that formula occupies cell E2 and spills to fill the green cells.
I think implicit intersection will work on legacy systems if the formula is copied to the entire range.
XLOOKUPS would also need to be reverted to VLOOKUPS or INDEX/MATCH.
Hi Peter - Thank you for helping with this. This is much appreciated.
Will look into the spreadsheet you revised asap and revert with comments.
This is a stab at producing a workbook that uses (named) relative references rather than dynamic array formulae (it pains me to do it and I cannot guarantee that I have got it right but it seems to be working on my computer).
By the way, if you are targeting all possible combinations any value1 that equals a value2 will give rise to a row of zeros.