Help with long if then statement

[Szpila]

I need to create a if then statement for commissions calculations and i
is on a cumulative total of sales. I think the formula is to long an
do not know how to shorten it and make it work.



IF(B22>500001,(C21*0.1),IF(B22>250001,IF(C22>500001,(((C22-500000)*0.1)+((500000-B22)*0.08)),IF(C22>250001,(C21*0.08),IF(B22<250001,IF(C22>500001,(((C22-500000)*0.1)+20000+(250000-B22)*0.05)),IF(C22>250001,(((C22-250000)*0.08)+(250000-B22)*0.05),(C21*0.05)))))))

Commission
2014 Example Jan Feb
Expected Annual Sales
$400,000 $135,000
Actual Sales
On Premise/Managed Hosted - 344,861 -
344,861 -
SaaS/Hosting Fees - - -
- -
Anticipated commission
0 0 0
Commission earned
Premise 8,500 20,089 -
SaaS/Hosted - -

 

[Ron Rosenfeld]

I need to create a if then statement for commissions calculations and it
is on a cumulative total of sales. I think the formula is to long and
do not know how to shorten it and make it work.



IF(B22>500001,(C21*0.1),IF(B22>250001,IF(C22>500001,(((C22-500000)*0.1)+((500000-B22)*0.08)),IF(C22>250001,(C21*0.08),IF(B22<250001,IF(C22>500001,(((C22-500000)*0.1)+20000+(250000-B22)*0.05)),IF(C22>250001,(((C22-250000)*0.08)+(250000-B22)*0.05),(C21*0.05)))))))

Commission
2014 Example Jan Feb
Expected Annual Sales
$400,000 $135,000
Actual Sales
On Premise/Managed Hosted - 344,861 -
344,861 -
SaaS/Hosting Fees - - -
- -
Anticipated commission
0 0 0
Commission earned
Premise 8,500 20,089 -
SaaS/Hosted - - -

This task is usually best accomplished using a table and VLOOKUP. Perhaps if you explained the commission structure, someone could help you. It's a bit difficult for me to figure out from your formula.