1

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?

Improve this question
3
  • Have you tried SMALL? Commented Mar 12, 2013 at 4:06
  • Yep. SMALL(__,1) behaves just like MIN in this case. Commented Mar 12, 2013 at 4:08
  • Try out something like this: =SMALL(INDIRECT(ADDRESS(A1:A10,A15:A24)),1) Commented Mar 12, 2013 at 4:36

4 Answers 4

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I think you're missing a parameter - instead of Cells(rowNum, ColNum) the above example would be rngSource(rowNum, colNum) where rngSource = K4:M23...
@Peter, my read of the question was that the row range and column range contained absolute sheet references, but I think you're right. Will amend my answer.
1

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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))))))

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.