多个excel if语句产生值1,2或3 [英] multiple excel if statements to produce value 1,2 or 3

问题描述

我正在尝试找出用于此任务的正确if语句：

I am trying to find out the right if statement to use for this task:

if B5 is higher than C5 = 1
if B5 is within 2% higher than C5 = 2
if B5 is lower than C5 = 3

，如果B5高于C5 = 2
，则为1

我有第二个陈述的公式

I have the formula for the second statement

=IF(AND(B5>(C5-(C5*2/100)),B5<(C5+(C5*2/100))),"yes","no")

我只是不确定如何将它放入一个语句中以获得最后输出的3个数字中的一个。我挣扎的主要事情是，在excel中只有两个答案if语句可以返回

Im just not sure how to put it into one statement to get one of the 3 numbers to come out at the end. main thing im struggling with is that there are only two answers in an excel if statement it can return

推荐答案

只是为了确保我正确理解，当B5在C5的2％范围内（大于，等于或小于）时，公式应该返回2吗？如果是这样，您可以使用以下公式：

Just to make sure I understand correctly, when B5 is within 2% of C5 (either greater than, equal to, or less than), should the formula return "2"? If so, you can use the following formula:

=IF(ABS(B5-C5)<=C5*0.02,2,IF(B5>C5,1,3))

此公式在B5时返回2比C5小2％或者比C5大2％。如果您希望它分别在这些情况下返回3和1，则可以使用以下代码：

This formula returns "2" when B5 is exactly 2% less than or exactly 2% greater than C5. If you want it to return "3" and "1" in these cases respectively, you can use the following instead:

=IF(ABS(B5-C5)<C5*0.02,2,IF(B5>C5,1,3))

这篇关于多个excel if语句产生值1,2或3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！