Question

求助excel函数表达，A1>=150得50;A1>=140得40;A1>=130得30;A1>=120得20;A1>=110得10;A1>=1

求助excel函数表达，A1>=150得50;A1>=140得40;A1>=130得30;A1>=120得20;A1>=110得10;A1>=100得5;A1>=90得-10;A1>=80得-20;A1>=70得-30;A1>=60得-40;A1>=50得-50;A1>=40得-60;A1>=30得-70;A1>=20得-80;A1>=10得-90;A1>=0得-100;

Answer 1

如果A1>150  ，A1<0 显示为 空

那么用公式 =IF(OR(A1>150,A1<0),"",ROUNDDOWN(A1-100,-1))

追问

䃼充：条件的150至0后面是带％号的，这个公式不能正常显示；
注意：5没有显示出来。

Answer 2

=LOOKUP(A3,{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150}%,{-100,-90,-80,-70,-60,-50,-40,-30,-20,-10,5,10,20,30,40,50})

Answer 3

=FLOOR(A1-100,10)

Answer 4

=LOOKUP(A1,{0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150},{-100,-90,-80,-70,-60,-50,-40,-30,-20,-10,5,10,20,30,40,50})