EXCEL分段式计算业绩提成公式
个人达成率:小于80%,提成系数0%;个人达成率:81%~100%,提成系数1%;个人达成率:101%~150%,提成系数1.5%;个人达成率:151%~200%,提成系数2%;个人达成率:200%以上,.提成系数2.5%;
你的条件设置不够精准,给调整为:个人达成率:小于80%,提成系数0%;个人达成率:大于等于80%,小于100%,提成系数1%;个人达成率:大于等于100%,小于150%,提成系数1.5%;个人达成率:大于等于150%,小于200%,提成系数2%;个人达成率:200%以上,.提成系数2.5%; D列单元格式设置为百分比,小数为1位。 以D1单元格为例,公式:=IFS(80%>C2,0%,100%>C2,1%,150%>C2,1.5%,200%>C2,2%,C2>=200%,2.5%,TRUE,"") 以此类推
假设A1为达成率
=LOOKUP(A1,{0,0.81,1.01,1.51,2.01},{0,0.01,0.015,0.02,0.025})
热门标签: