title: Using MIN/MAX in excel array formula
body: <p>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. </p>
  
  <p>In cell F1 the formula is:</p>
  
  <pre><code>{=SUM(MAX(A$1:A$9, E1)*B$1:B$9)}
  </code></pre>
  
  <p>What this formula does is:</p>
  
  <pre><code>=MAX(A$1:A$9, E1)*B$1 + MAX(A$1:A$9, E1)*B$2 + ...
  </code></pre>
  
  <p>Instead, I need a formula that does this:</p>
  
  <pre><code>=MAX(A$1, E1)*B$1 + MAX(A$2, E1)*B$2 + ...
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I can use a IF instruction: in F1 I can write</p>
  
  <pre><code>{=SUM(IF(A$1:A$9&gt;E1, A$1:A$9, E1)*B$1:B$9)}
  </code></pre>
  
  <p>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.</p>
  
  <p>Are there better solutions to my problem? Thank you all!</p>
  
  <p>NOTE: syntax may vary a little because I am using the italian version of excel.</p>