Question

excel大神请帮忙看一下这个公式到底为什么错了

excel大神请帮忙看一下这个公式到底为什么错了
=IF(B22<=1000000,H22*1,IF(B22<=2000000,H22*1.4,IF(B22<=3000000,H22*1.8,IF(B22<=4000000,H22*2.1,IF(B22<=5000000,H22*2.4,IF(B22<=10000000,H22*2.6,IF(B22<=15000000,H22*2.8,IF(B22<=20000000,H22*3,IF(B22<=25000000,H22*3.2)))))))))
一个阶梯提成公式，不加最后的25000000这组数据就对，加上就错误了，
请问下该怎么修改才行，不胜感激！

Answer 1

=IF(B22<=1000000,H22*1,IF(B22<=2000000,H22*1.4,IF(B22<=3000000,H22*1.8,IF(B22<=4000000,H22*2.1,IF(B22<=5000000,H22*2.4,IF(B22<=10000000,H22*2.6,IF(B22<=15000000,H22*2.8,IF(B22<=20000000,H22*3,H22*3.2))))))))

建议这个用HLOOKUP函数。

假设下表在sheet2

=HLOOKUP(B22,sheet2!$A$2:$I$3,2,0)

Answer 2

输入以下公式，优化一下
=LOOKUP(B22,{0,1,2,3,4,5,10,15,20}*10^6+0.01,{1,1.4,1.8,2.1,2.4,2.6,2.8,3,3.2})*H22