title: How to store unique commission calculations efficiently
body: <p>I work for an agency with over 60 clients that have 60+ unique commission calculations. Attached is a sample of what those commissions look like. As you see, they're not the easiest commission structures to follow and calculate.</p>
  <p>We've been getting by with writing 60+ unique formulas on a spreadsheet to calculate the client commissions. However, it does get challenging to keep up with as we have to double check and triple check every single formula of over 60 formulas. I was wondering if is there a more efficient way to keep up with these calculations? I know there have to be a better way but I can't think or find anything on the internet. A few example of our unique formulas:</p>
  <p>Commission: GROSS - $84,000</p>
  <p>'&lt; $83,333 - 6%</p>
  <p>'&gt; $83,333 - 5%</p>
  <p>'&gt; $166,666 - 4%</p>
  <p>'&gt; $250,000 - 3%</p>
  <pre><code>=MAX(0,MIN(F31-84000,83333)*6%)+
  MAX(0,MIN(F31-84000-83333,83333)*5%)+
  MAX(0,MIN(F31-84000-166666,83333)*4%)+
  MAX(0,MIN(F31-8400-250000,F31-8400-250000)*3%)
  </code></pre>
  <p>Commission: GROSS - $485,000
  '&lt; $83,333 - 4%</p>
  <p>'&gt; $83,333 - 3.5%</p>
  <p>'&gt; $166,666 - 3%</p>
  <p>'&gt; $250,000 - 2.5% + $3,750</p>
  <p>'Invoice min: $9,000</p>
  <pre><code>=IF(G33+(MAX(0,MIN(F33-485000,83333)*4%)+
  MAX(0,MIN(F33-485000-83333,83333)*3.5%)+
  MAX(0,MIN(F33-485000-166666,83333)*3%)+
  MAX(0,MIN(F33-485000-250000,F33-485000-250000)*2.5%))&lt;9000,9000-G33,
  MAX(0,MIN(F33-485000,83333)*4%)+
  MAX(0,MIN(F33-485000-83333,83333)*3.5%)+
  MAX(0,MIN(F33-485000-166666,83333)*3%)+
  MAX(0,MIN(F33-485000-250000,F33-485000-250000)*2.5%))
  </code></pre>
  <p><a href="https://i.stack.imgur.com/ZAi9H.png" rel="nofollow noreferrer">https://i.stack.imgur.com/ZAi9H.png</a></p>