销售人员提成问题，求解

rtic

各位表哥表叔，现有提成方案一个，求公式！
方案如下：
销售额                          提成率
0~30000（含）              2%
30000~40000（含）        3%加上一阶段提成
40000~50000（含）        4%加上一阶段提成
50000~60000（含）        5%加上一阶段提成
60000以上                 6%加上一阶段提成

例完成35000元，提成=30000*2%+5000*3%
完成78000元，提成=30000*2%+10000*3%+10000*4%+10000*5%+18000*6%

叩谢！

新纪元

关注中...........

ctp_119

=IF(D2>60000,30000*0.02+10000*0.03+10000*0.04+10000*0.05+(D2-60000)*0.06,IF(D2>50000,30000*0.02+10000*0.03+10000*0.04+(D2-50000)*0.05,IF(D2>40000,30000*0.02+10000*0.03+(D2-40000)*0.04,IF(D2>30000,30000*0.02+(D2-30000)*0.03,D2*0.02))))

kangyuli

A2==IF(A2<=30000,A2*0.02,IF(AND(A2>30000,A2<=40000),30000*0.02+(A2-30000)*0.03,IF(AND(A2>40000,A2<=50000),30000*0.02+10000*0.03+(A2-40000)*0.04,IF(AND(A2>50000,A2<=60000),30000*0.02+10000*0.03+10000*0.04+(A2-50000)*0.05,IF(A2>60000,30000*0.02+10000*0.03+10000*0.04+10000*0.05+(A2-60000)*0.06)))))

kangyuli

=MAX(A2*{0.02,0.03,0.04,0.05,0.06}-{0,300,400,500,600},0)简化一下公式

交叉点

原帖由 kangyuli 于 2011-3-26 09:31 发表
=MAX(A2*{0.02,0.03,0.04,0.05,0.06}-{0,300,400,500,600},0)简化一下公式

速算扣除数算错了吧？
应该是{0,300,700,1200,1800}
所以公式为
=MAX(D2*{0.02,0.03,0.04,0.05,0.06}-{0,300,700,1200,1800},0)
做些缩短工作
=MAX(D2*{2,3,4,5,6}/100-{0,3,7,12,18}*100,0)

kangyuli

谢谢你帮助修改，谢谢，共同努力吧！！！！！！！！

kangyuli

向你学习，谢谢大师帮助

rtic

谢诸位表婶！6楼大哥正解！拜谢！