Hope all is well. I'm trying to calculate a simple tiered commission formula. The base fee would be ($100) + (3% on the first $500) + (5% on the next $250 or $501-$750) + (7.5% on anything above the $750 threshold). When I do the calc manually on $750 the fee should be $37.50. I can't seem to figure out how to make this work and keeping getting $4.50.
Sale Price: $750
Base Fee: $10
Fee $0 to $500 ($500*3%) = $15
Fee $500 to $750 ($250*5%) = $12.50
Total fee = $37.50
Using (SUMPRODUCT(--(G20>{0;500;750}),--(G20-{0;500;750}), {0.03;0.05;0.075}))+10 gets me $45.00
Any help would greatly be appreciated.
Bookmarks