Hello (again) mrexcel forums,
I'm working on a formula which outputs an array of values in an n*m grid, where the size of the grid may vary a lot between use cases.
I've been getting good mileage with using arrays as named variables in equations, but i'm having trouble with the MIN/MAX functions. I can use INDEX(array1,,col)*INDEX(array2,,row) to extract the row and col to be multiplied from the two subarrays array1 and array2, and find the maximum of the resultant vector.
The issue arises when i try to turn this formula into an array output, for instance by setting
where {1:15} is the array {1 2 3 ... 14 15}. This ends up multiplying the matrices as you normally would, since INDEX() of multiple columns turns them into a new array, rather than a set of vectors.
Below is a screenshot of the intended use case;
The two orange arrays are outputted from the single cell in their top left corners, as shown in the third figure. The function works as expected but must be dragged to a size manually chosen in order to work correctly.
Note that in the intended use, the two orange tables are handled as just the named variables array1= {0,1,1,... ; 0,0,0,...} and not shown typed out in the spreadsheet. a nonworking example is:
So my question is; how if possible, can i produce the output array from one formula? such as;
- Where the MAX of each row/column multiplication is found individually, rather than the MAX of all the calculated values.
- Where the column and row to be multiplied are each chosen in the same manner that dragging the non-array expression would.
I greatly appreciate any and all insight on how to solve this problem, and thanks once more for the amazing help offered by the frequenters in here, on mrexcel.com. Some sort of wizardry of using MATCH(), INDEX(), OFFSET() and SUMPRODUCT() together might be what i'm looking for, but at this point i feel stuck.
I'm working on a formula which outputs an array of values in an n*m grid, where the size of the grid may vary a lot between use cases.
I've been getting good mileage with using arrays as named variables in equations, but i'm having trouble with the MIN/MAX functions. I can use INDEX(array1,,col)*INDEX(array2,,row) to extract the row and col to be multiplied from the two subarrays array1 and array2, and find the maximum of the resultant vector.
The issue arises when i try to turn this formula into an array output, for instance by setting
Excel Formula:
=MAX(INDEX(array1,, {1:15} )*INDEX(array2, {1:7},))
Ideally the output would look something like;
0 0 3 0 0
= 0 3 0 4 0
0 0 0 2 0
where {1:15} is the array {1 2 3 ... 14 15}. This ends up multiplying the matrices as you normally would, since INDEX() of multiple columns turns them into a new array, rather than a set of vectors.
Below is a screenshot of the intended use case;
The two orange arrays are outputted from the single cell in their top left corners, as shown in the third figure. The function works as expected but must be dragged to a size manually chosen in order to work correctly.
Note that in the intended use, the two orange tables are handled as just the named variables array1= {0,1,1,... ; 0,0,0,...} and not shown typed out in the spreadsheet. a nonworking example is:
So my question is; how if possible, can i produce the output array from one formula? such as;
Excel Formula:
=MAX( --(INDEX(array1,,COLUMN(array1))*INDEX(array2,ROW(array2)))
- Where the MAX of each row/column multiplication is found individually, rather than the MAX of all the calculated values.
- Where the column and row to be multiplied are each chosen in the same manner that dragging the non-array expression would.
I greatly appreciate any and all insight on how to solve this problem, and thanks once more for the amazing help offered by the frequenters in here, on mrexcel.com. Some sort of wizardry of using MATCH(), INDEX(), OFFSET() and SUMPRODUCT() together might be what i'm looking for, but at this point i feel stuck.