CODEWITHSUNDEEP
excelexcel-formula
EttoreP
EttoreP

Reputation: 414

Formula for find maximum column value of the minimum row values

I'm looking for a formula for find the maximum value of the minimum values of a row, take a look to the image for an example:

example

As you can see, now I'm doing the min of each row and than the max of the column of the mins, but my problem is that I need to do this to a table which is populated with an external data connection, and every time I refresh the data the number of rows changes, so I can't insert a mins column because I don't know how much rows I need (and, anyway, would be better not to insert another column)...So I need a formula for obtain this result using the table references.
I also need the avarage of the mins.

I'm not very skilled with array formulas, and I can't figure out how i can specify to the formula that it must first evaluate the row (a,b,c,d) for obtain the min, and then evaluate the array of the mins obtained for find the max (or the avarage).

Any help appreciated

Ettore

Edit:
I forgot to say that there are more columns in the table, and some of this must be skipped in the evaluation of the min, consider the image below:

second example

Upvotes: 2

Views: 317

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Consider the following UDF():

Public Function MiniMax(r As Range) As Variant
    Dim wf As WorksheetFunction, i As Long, j As Long
    Dim nLastRow As Long, nLastColumn As Long
    Dim nFirstRow As Long, nFirstColumn As Long
    Dim numrow As Long, numcol As Long, k As Long

    nLastRow = r.Rows.Count + r.Row - 1
    nLastColumn = r.Columns.Count + r.Column - 1
    nFirstRow = r.Row
    nFirstColumn = r.Column
    numrow = r.Rows.Count
    numcol = r.Columns.Count

    With Application.WorksheetFunction
        k = 1
        ReDim ary(1 To numrow)
        For i = nFirstRow To nLastRow
            ary(k) = .Min(Range(Cells(i, nFirstColumn), Cells(i, nLastColumn)))
            k = k + 1
        Next i

        MiniMax = .Max(ary)
    End With
End Function

enter image description here

and if you desired automatic expansion or contraction of the range, this could be accommodated in several different ways.

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Upvotes: 0

Related Questions