EXCEL分段式计算业绩提成公式答:例如:提成规则:3W及以下 3% 3W-4W 6% 4W-6W 8% 6W-8W 10% 8W及以上 12% 则公式为 =LOOKUP(A2,{0,3,4,6,8}*10^4+1,A2*{0.03,0.06,0.08,0.1,0.12}-{0,900,1700,2900,4500})
excel分段函数公式?答:可以一段段拆开算了再加起来,假设数据在A1:A100中,写的是类似500000的真正数字,B1中输入 =MAX(500,MIN(A1,500000)*0.2%)+MEDIAN(A1-500000,0,500000)*0.15%+MEDIAN(A1-1000000,0,4000000)*10%%+MEDIAN(A1-5000000,0,15000000)*5%%+MEDIAN(A1-20000000,0,30000000)*3%%+MAX(0,A1-...
excel 分段函数公式答:5000000,10000000,20000000}))*LOOKUP(A1,{0,10000,100000,200000,500000,1000000,2000000,5000000,10000000,20000000},{0,0,0.025,0.02,0.015,0.01,0.009,0.008,0.007,0.006})由于是逐段增加的,所以,需要得到每段的基本费用 如图用公式算出了每段的基本费用作为参照 附件请参考 ...
EXCEL函数分段计算答:如下图 在B1中输入以下工式,向下拉即可 =IF(A1<=10,A1*1,IF(AND(A1>10,A1<=20),10*1+(A1-10)*2,IF(A1>20,10*1+10*2+(A1-20)*3,"")))小于10乘以1.5的工式 =IF(A1<=10,A1*1.5,IF(AND(A1>10,A1<=20),10*1.5+(A1-10)*2,IF(A1>20,10*1.5+10*2+(A1-20...
在Excel中如何设置分条件及分段计算的公式答:=IF(AND(OR(E3="散居五保",E3="城镇三无"),K3-L3<=3000),MIN(K3-L3,3000),IF(AND(OR(E3="农村低保",E3="城镇低保"),(K3-L3)<=1000),(K3-L3)*0.5,IF(AND(OR(E3="农村低保",E3="城镇低保"),(K3-L3)<=2000),500+(K3-L3-1000)*0.3,IF(AND(OR(E3="农村低保",E3...