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

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.