Using MIN/MAX in excel array formula

Question

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.

Answer 1

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.

Answer 2

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)