Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using MIN/MAX in excel array formula

I have a simple array formula in excel that doesn't work in the way I wish. In columns A and B there is data (A1 is paired with B1 and so on) while in column F there is the calculation based on the parameter in column E.

In cell F1 the formula is:

{=SUM(MAX(A$1:A$9, E1)*B$1:B$9)}

What this formula does is:

=MAX(A$1:A$9, E1)*B$1 + MAX(A$1:A$9, E1)*B$2 + ...

Instead, I need a formula that does this:

=MAX(A$1, E1)*B$1 + MAX(A$2, E1)*B$2 + ...

In words, the formula I wrote (the first one) always finds the max between the values from A1 to A9 and E1, multiplies it by the i-th B value and sums the results. What I need is a formula that finds the max between the i-th A value and E1, and not between all the A values.

What I'm looking for is easily done by adding in column C the formula =MAX(A1;E$1)*B1 and then in F1 just =SUM(A1:A9), but I can't use this solution because in column F the same formula is repeated, with the E parameter changing every time.

I can use a IF instruction: in F1 I can write

{=SUM(IF(A$1:A$9>E1, A$1:A$9, E1)*B$1:B$9)}

While this formula does what I need in this case, I think it's a bad solution because I find it difficult to read and to expand. For example, if there is another parameter in column D and the factor is MIN(MAX(A$1:A$9;E1);D1), using IF will result in a very long and very unreadable and complicated formula.

Are there better solutions to my problem? Thank you all!

NOTE: syntax may vary a little because I am using the italian version of excel.

like image 934
duri Avatar asked Aug 21 '15 17:08

duri


People also ask

How do I max out an array in Excel?

The MAX IF function identifies the maximum value from all the array values that match the logical test. The formula of Excel MAX If function is “=MAX(IF(logical test,value_ if _true,value_if_ false)).”

How do you use an array formula in Excel?

Enter an array formulaSelect the cells where you want to see your results. Enter your formula. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.


2 Answers

Another possibility is a VBA function.

Public Function SumMaxMin(rRng1 As Range, rRng2 As Range, ParamArray vaMinMax() As Variant) As Double

    Dim rCell As Range
    Dim dReturn As Double
    Dim aMult() As Double
    Dim lCnt As Long
    Dim i As Long

    ReDim aMult(1 To rRng1.Cells.Count)

    For Each rCell In rRng1.Cells
        lCnt = lCnt + 1
        aMult(lCnt) = rCell.Value
        For i = LBound(vaMinMax) To UBound(vaMinMax) Step 2
            If Not Evaluate(aMult(lCnt) & vaMinMax(i + 1) & vaMinMax(i)) Then
                aMult(lCnt) = vaMinMax(i)
            End If
        Next i
    Next rCell

    For i = LBound(aMult) To UBound(aMult)
        dReturn = dReturn + aMult(i) * rRng2.Cells(i).Value
    Next i

    SumMaxMin = dReturn

End Function

For your example

=SumMaxMin(A1:A9,B1:B9,E1,">")

Adding another condition

=SumMaxMin(A1:A9,B1:B9,E1,">",D1,"<")

It will error if your ranges aren't the same number of cells or you pass arguments that don't work with Evaluate.

like image 178
Dick Kusleika Avatar answered Oct 21 '22 19:10

Dick Kusleika


Another possibility for avoiding repetitions of cell references is:

=SUM(B1:B9*ABS(A1:A9-E1*{1,-1}))/2

assuming values are non-negative. More generally to return an array of pairwise max values:

=MMULT((A1:A9-E1*{1,-1})^{2,1}^{0.5,1},{1;1}/2)

which returns:

MAX(A1,E1)
MAX(A2,E1)
...
MAX(A9,E1)
like image 3
lori_m Avatar answered Oct 21 '22 19:10

lori_m