title: In Excel, how can I add values from two "vectors" sorted by rank?
body: <p>I feel like this is actually a quite simple problem but I couldn't find a way to word it in a good way and so I haven't found an answer yet.</p>
  
  <p>Basically, in an Excel sheet, I have 4 calculated values (let's say A1:D1). Meanwhile, I have a fixed vector of 4 other values (10, -20, -40, -50).
  I'm looking for an efficient way of adding 10 to the biggest value of A1:D1, -20 to the second biggest value of A1:D1, -40 to the third and -50 to the last.</p>
  
  <p>My current solution is rather ugly. For each of my four calculated values, I have <code>=&lt;calculation&gt;+IF(RANK(A1,A1:D1)=1,10,IF(RANK(A1,A1:D1)=2,-20,IF(RANK(A1,A1:D1)=3,-40,-50)))</code></p>