大于直接用大于号 > 比如 A1>B1
小鱼等于用小于号+等于号,比如 A1<=B1
大于:>
大于等于:>=
等于:=
小于等于:<=
小于:<
不等于:<>
假设要比较的值在A1单元格
这公式等于 =if(and(A1>9,A1<=14),"好",if(A1<9,"一般",""))
同时满足多个条件,可以用AND函数,
=IF(AND(A1>=2,A1<=20),"正确","")
如果平均分在D列,那么输入以下公式
=COUNTIFS(D:D,">80",D:D,"<=90") 2007以上版本
=SUMPRODUCT((D2:D200>80)*(D2:D200<=90)) 2003以下版本
B1 =IF(A1<=5,A1+2,IF(AND(A1>5,A1<=10),A1+4,IF(AND(A1>10,A1<=20),A1+6,"")))
大于20时为空白
简化公式为
B1 =IF(A1<=5,A1+2,IF(A1<=10,A1+4,IF(A1<=20,A1+6,"")))
=IF(U20<=2,100,IF(AND(U20<=8,U20>2),50,IF(U20>8,0)))
有专门的符号
大于等于 >=
小于等于 <=
B1单元格写入公式
=A1*IF(AND(A1>0,A1<=5),1,IF(AND(A1>5,A1<=10),2,))
或
=IF(A1<=0,,A1*IF(A1<=5,1,IF(A1<=10,2,)))
可以用FLOOR函数处理
=FLOOR(IF(MOD(A1*10,1),A1-0.01,A1),0.05)