10

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.

Improve this question
3
  • Can you expand upon your reasons for rejecting the array formula you propose? I don't quite follow your examples with using further parameters. Given that that array formula is, in my opinion, the most efficient and most logical solution to your original problem, perhaps you should re-write your post so that it places more emphasis on these additional parameters. Commented Aug 21, 2015 at 18:35
  • If you wanted to 'cap off' the maximum value with a value in D1 the formula would be {=SUM(MIN(D1, IF(A$1:A$9>E1, A$1:A$9, E1))*B$1:B$9)} which doesn't seem too much of a stretch. Commented Aug 22, 2015 at 9:30
  • For the record you could use the identity MAX(A1,E1)=(A1+E1+ABS(A1-E1))/2 (as in response below) which could perhaps be useful if A1 and E1 were replaced by long formulas but in practice IF is the easiest option. Commented Aug 22, 2015 at 14:07

6 Answers 6

Reset to default
10

The problem is that MAX takes an array as an argument. Functions that normally take an array never return an array - they were designed to turn an array into one number. No matter how many arrays you throw at MAX, it's always just going to return one number.

I couldn't come up with a good solution, so here's a bad one

=SUMPRODUCT(((A1:A9*(A1:A9>E1))+(E1*(A1:A9<=E1)))*B1:B9)

I don't think that really increases the maintainability of the IF-based formula that you're trying to avoid. I think you're stuck with IF or a helper column.

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

1 Comment

You pointed out the real problem: MAX only returns one number, so there isn't the easy solution I was looking for. Our solutions are very similar, but yours is a little more elegant. Thanks!
4

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.

Improve this answer

1 Comment

This one seems to be the only real solution that lets the user do everything he needs without restrictions. I don't accept this as the correct answer just because in your other one you point out what the real problem is and answer the question in the title.
3

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)
Improve this answer

Comments

2

Lots of good answers already, but the following gets the job done with a formula that differs by exactly one character from the one in the original question.

Define AMAX to be the LAMBDA function:

=LAMBDA(arraya,arrayb,IF(arraya>arrayb,arraya,arrayb))

and you can enter in cell F1:

=SUM(AMAX(A$1:A$9, E1)*B$1:B$9)
Improve this answer

Comments

0

I don't remember ever cracking this problem, but for maintainability I'd probably do something like this:

{=SUM((A1:A9<E1)*E1*B$1:B$9)    +    SUM((A1:A9>=E1)*A1:A9*B$1:B$9)}
Improve this answer

1 Comment

I find this formula more elegant than mine, but it keeps the same problems, so this is just a little step foward. Thanks!
0

If I understand the problem correctly, using IF instead of MAX should do:

=SUM(IF($A$1:$A$9>E1;$A$1:$A$9;E1)*$B$1:$B$9)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.