Progressive Commission

[JJ in SD]

F6 = $72,690.

I found this Formula on the board.

=SUMPRODUCT(--(F6>{0,7999,39999,59999,79999}),F6-{0,7999,39999,59999,79999},{2,2.5,2,2,2})% which returns $3978.72.

So I modified it slightly to:

=SUMPRODUCT(--(F6>{0,7999,39999,59999,79999}),F6-{0,7999,39999,59999,79999},{2,2.5,4,6,8})% and now it returns $5140.18.

The issue is I cannot get it to get the result I need.

The pay plan goes like this:
$0 to $7999-2%
From $8000 to $39999-2.5% (Retro to $1)
From $40000 to $59999-4% (Retro to $1)
From $60000 to $79000-6% (Retro to $1)
All from $80000 and above-8% (Retro to $1)

If the Gross Profit is $72,690, based upon the pay plan the commission should be $4,361.40, which is 6%.

Thanks in advance,
JJ in SD

 

[shg]

Try

=A1*LOOKUP(A1, {0,8,40,60,80}*1000, {2,2.5,4,6,8}%)

 

[JJ in SD]

Thank you I'll try that.