Question

EXCEL业绩阶梯提点怎么计算？

30万以下按1.25%，30-35万万1.5%，35-40万.75%，40以上2%，请问各位大神这个公式应该怎么写呀？

Answer 1

建议参照如下：

在J4单元格中输入公式：

=IF(I4<1000,"小微",IF(AND(I4>=1000,I4<5000),"三级",IF(AND(I4>=5000,I4<10000),"二级",IF(AND(I4>=10000,I4<50000),"一级","特级"))))

如图：

Answer 2

如果你的业绩数据在A2单元格的，则可在B2单元格输入公式：
=SUM(TEXT(A2-{0,3,3.5,4}*10^5,"0;!0")*{125,25,25,25}%%)
=SUM(TEXT(A2-{0,30,35,40}*10^4,"0;!0")*{1.25,0.25,0.25,0.25}%)
应该就可自动核算你要的提成了吧…………

Answer 3

如果业绩在B列，c2=lookup(b2,{0,30,35,40},{1.25%,1.5%,1.75%,2%})，公式下拉自动填充。

Answer 4

业绩在A2，计算公式为：=A2*LOOKUP(A2,{0,0.0125;300000,0.015;350000,0.0175;400000,0.02})