Tiered Commission - Flat Rate

**rjhendrix** · 09-20-2012, 08:20 PM

Hi all,

After searching and searching, figured I'd try asking the question -

I'm looking for a way to calculate flat commission (not percentage) based on tier levels. Here's an example:

TIER 1: 1-4 Units Sold = $25 per unit
TIER 2: 5-8 Units Sold = $42 per unit
TIER 3: 9-15 Units Sold = $103 per unit
TIER 4: 16+ Units Sold = $123 per unit

I'm currently learning Excel, so I blame it solely on my lack of knowledge at the moment. The problem that I run into is that I need TIER 1 to STAY at $25 per unit, TIER 2 to STAY at $42 per unit, etc.
example:
4 units sold = $100
5 units sold = $142 ..and so on
Right now, my calculations cause 5 units to ALL jump to $42 making the result $210

All my searches bring me around to percentage calculations and I have yet to find a formula that does what I need it to. I've worked in Nested IF and even tried SUMPRODUCT but haven't been able to return a successful calculation. Any help is appreciated as is your patience in a "newbie" like me.

-RJ

**shg** · 09-20-2012, 08:28 PM

       -A-- -B--
   1   Tier Comm
   2            
   3      0   25
   4      4   42
   5      8  103
   6     15  123
   7   Sold Comm
   8      1   25
   9      2   50
  10      3   75
  11      4  100
  12      5  142
  13      6  184
  14      7  226
  15      8  268
  16      9  371
  17     10  474
  18     11  577
  19     12  680
  20     13  783
  21     14  886
  22     15  989
  23     16 1112
  24     17 1235
  25     18 1358

The formula in B8 and copied down is

=SUMPRODUCT((A8>$A$3:$A$6)*(A8-$A$3:$A$6)*($B$3:$B$6-$B$2:$B$5))

The blank in B2 is necessary.