Using MAX() to output multiple values from one formula

nicklasx

New Member
Joined
Oct 26, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

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.
1635511401769.png



1635511436357.png



1635511657254.png


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:
1635513177500.png



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.
 

Attachments

  • 1635512971151.png
    1635512971151.png
    45.7 KB · Views: 19

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,224,813
Messages
6,181,107
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top