CODEWITHSUNDEEP
excelexcel-formula
kale
kale

Reputation: 191

Minimum of Excel Array Formula with INDEX()

I'm having a little trouble with this array formula in Excel.

I have two ranges (columns of data) that correspond to the row and column number to an overall array that contains multipliers to be applied to a non-related function. I want to find the minimum multiplier that is found from the column/row references.

Let's assume the column number range is A1:A10 and the row number range is A15:A24 and the multiplier array is K4:M23. An inefficient way to do this is to do:

=MIN(INDEX(K4:M23,A15,A1),INDEX(K4:M23,A16,A2),etc...)

...but this will get cumbersome, especially if checking for errors, etc. Not to mention the memory usage if this function is called several thousand times (it just so happens to be).

So I thought about an array function:

{=MIN(INDEX(K4:M23,A15:A24,A1:A10))}

...but this only returns the first element in the array. If this function is entered as a multi-cell array formula, it handles it correctly, but it seems that as is, MIN is applied to each singular element and the function returns the original array size, not the single value of the minimum.

Any way around this?

Upvotes: 1

Views: 4178

Answers (4)

NBoater
NBoater

Reputation: 1

Old question but how about the following array formula (CSE): =MIN(INDEX(K4:M23,INDEX(A15:A24,N(IF(1,ROW(N1:N10)))),INDEX(A1:A10,N(IF(1,ROW(N1:N10))))))

Upvotes: 0

kale
kale

Reputation: 191

Thanks to a little inspiration by pbart, here's how I ending up doing it correctly... (a year and a half after I asked the question.)

I basically create a reference array the same dimensions as K4:M23 that has ones at the intersection of the column and row arrays and multiply them together.

=AGGREGATE(15,6,(IF(MMULT(TRANSPOSE(IFERROR(IF(A15:A24=COLUMN(1:20),1,0),0)),IFERROR(IF(A1:A10={1,2,3},1,0),0))=0,#N/A,1)*K4:M23),1)

Not sure it's the most efficient, but not too bad doing an array lookup without using Match or Index.

Upvotes: 0

pbart
pbart

Reputation: 11

I have never been able to operate on the output of index as if it were an array. What could be done instead is filter the original table of multipliers. To avoid helper cells this can be done within defined names

rowFilter:      = SIGN( MATCH(rowIndex, selectedRows, 0))       
columnFilter:   = SIGN( MATCH(columnIndex, selectedColumns, 0 ) )       
filteredMultipliers:= multipliers * rowFilter * columnFilter

The worksheet formula 

= AGGREGATE( 15, 6, filteredMultipliers, 1 )

will identify the minimum value ignoring the intentional #N/A errors.

Upvotes: 1

chuff
chuff

Reputation: 5866

If I understand your question correctly, the following VBA function should produce what you want.

The function takes three arguments: a reference to the array range; a reference to the row number range; and a reference to the column number range. It returns the minimum of the values in the cells corresponding to the row numbers and column numbers.

  Function ArrayMin(MatrixRange As Range, RowRange As Range, ColRange As Range) As Double
      Application.Volatile
      Dim colNum As Long
      Dim rowNum As Long
      Dim cellVal As Double
      Dim MinVal As Double
      Dim i As Long
      MinVal = 1000000                               'a number >= than max array range value
      For i = 0 To ColRange.Rows.Count - 1
          rowNum = RowRange(1, 1).Offset(i, 0).Value
          colNum = ColRange(1, 1).Offset(i, 0).Value
          cellVal = MatrixRange(rowNum, colNum).Value
          If cellVal < MinVal Then
              MinVal = cellVal
          End If
      Next
      ArrayMin = MinVal
  End Function

It can be installed in the standard way by inserting a new standard VBA module in your workbook and pasting the code in.

Upvotes: 1

Related Questions