嵌套If公式总是返回FALSE -谷歌电子表格

Question

我试图使用一个公式来判断单元格值是否在这些范围内，并根据其设置值。

如果值在0到10之间，将单元格值设置为"需要改进";如果值在10到15之间，则将单元格值设置为"满足期望";如果该值大于15，则将单元格值设置为"超出预期">

我输入的任何值总是导致它为假，这意味着它不应用任何这些值。

我现在的公式是:

=IF(AND(D21>=0, D21<=10), "Value is between 5 and 10", IF(AND(D21>=10, D21<=15), "Value is between 15 and 20", IF(AND(D21>=15, D21<=100), "Value is between 15 and 20", "Value is not within the specified range")))

无论我把数字改为什么，它总是不在指定的范围内，我试着用谷歌搜索了很多东西，但我不能弄清楚

Answer 1

1. 用ISBETWEEN(D21,0,10)代替AND(D21>=0, D21<=10)
2. 你设置的数字范围相互重叠，正确的方法应该是AND(>=0,<=9),AND(>=10,<=14),AND(>=15,<=100)在你的情况下，
这些嵌套的IF()很容易出错，如果你不知道你在做什么。你可以使用非嵌套的方式来做你想做的事情:

=LAMBDA(VALUE,
IF(ISBETWEEN(VALUE,0,9),"Value is between 0 and 9","")
&IF(ISBETWEEN(VALUE,10,14),"Value is between 10 and 14","")
&IF(ISBETWEEN(VALUE,15,100),"Value is between 15 and 100","")
&IF(OR(VALUE<0,VALUE>100),"Value is not within the specified range","")
)($D$21)

由于您引用的是D21而不是D1或D2，请确保您已经在单元格D21中输入了要测试的值。

实际上在这种情况下，IFS()是比IF()更好的选择:

=LAMBDA(VALUE,
IFS(
ISBETWEEN(VALUE,0,9),"Value is between 0 and 9",
ISBETWEEN(VALUE,10,14),"Value is between 10 and 14",
ISBETWEEN(VALUE,15,100),"Value is between 15 and 100",
TRUE,"Value is not within the specified range"
)
)($D$21)

Answer 2

use:

=IF(AND(D21>=0, D21<=10), "Needs Improvement", IF(AND(D21>10, D21<=15), "Meets Expectations", IF(D21>15, "Exceeds Expectations")))