如何使用 IF 函数编写步进函数

发布于 2024-09-07 02:42:55 字数 475 浏览 6 评论 0原文

我有 3 个数字范围，答案取决于范围。

75-79 -> 0.255
80-84 -> 0.327
85+   -> 0.559

我尝试使用嵌套 IF 函数创建一个方程来说明范围，但 Excel 指出我为此函数输入了太多参数。下面是我输入的方程式，但它不起作用。（X2 包含数字）

=IF(X2=75,X2<=79,0.255,IF(X2=80,X2<=84,0.327,IF(X2>=85,0.559,0)))

我还尝试将数字范围输入到另一张表 - Age 中，并收到错误 #Value!。

=IF(X2=Age!A1:A5,0.257,IF(X2=Age!A6:A10,0.327,IF(X2=Age!A11:A33,0.559,0)))

原文

I have 3 ranges of numbers and the answer depends on the range.

75-79 -> 0.255
80-84 -> 0.327
85+   -> 0.559

I tried to create an equation that accounts for the ranges by using nested IF functions, but Excel states that I have entered too many arguments for this function. Below is the equation that I entered that is not working. (X2 contains the number)

=IF(X2=75,X2<=79,0.255,IF(X2=80,X2<=84,0.327,IF(X2>=85,0.559,0)))

I also tried to enter the range of numbers into another sheet - Age, and got an error #Value!.

=IF(X2=Age!A1:A5,0.257,IF(X2=Age!A6:A10,0.327,IF(X2=Age!A11:A33,0.559,0)))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

_失温 2024-09-14 02:42:58

这就是我所做的：

非常简单地说：

=IF(C7>100,"Profit",IF(C7=100,"Quota Met","Loss"))

第一个 IF 语句，如果 true 将输入 Profit，如果 false 将导致下一个 IF 语句，依此类推：）

我只有基本的公式知识，但它有效，所以我会接受我是对的！

This is what I did:

Very simply put:

=IF(C7>100,"Profit",IF(C7=100,"Quota Met","Loss"))

The first IF Statement, if true will input Profit, and if false will lead on to the next IF statement and so forth :)

I only have basic formula knowledge but it's working so I will accept I am right!

回复收藏 0 原文

初见终念 2024-09-14 02:42:57

您需要对多个条件使用 AND 函数：

=IF(AND(A2>=75, A2<=79),0.255,IF(AND(A2>=80, X2<=84),0.327,IF(A2>=85,0.559,0)))

You need to use the AND function for the multiple conditions:

=IF(AND(A2>=75, A2<=79),0.255,IF(AND(A2>=80, X2<=84),0.327,IF(A2>=85,0.559,0)))

回复收藏 0 原文

错々过的事 2024-09-14 02:42:57

您的公式应采用以下形式：=IF(X2 >= 85,0.559,IF(X2 >= 80,0.327,IF(X2 >=75,0.255,0)))。这模拟了 Excel 所缺少的 ELSE-IF 操作数。您的公式每个都使用两个条件，但 IF 公式的第二个参数是条件计算结果为 true 时要使用的值。你不能以这种方式链接条件。

回复收藏 0 原文

江湖正好 2024-09-14 02:42:56

=IF(X2>=85,0.559,IF(X2>=80,0.327,IF(X2>=75,0.255,-1)))

解释：

=IF(X2>=85,                  'If the value is in the highest bracket
      0.559,                 'Use the appropriate number
      IF(X2>=80,             'Otherwise, if the number is in the next highest bracket
           0.327,            'Use the appropriate number
           IF(X2>=75,        'Otherwise, if the number is in the next highest bracket
              0.255,         'Use the appropriate number
              -1             'Otherwise, we're not in any of the ranges (Error)
             )
        )
   )

=IF(X2>=85,0.559,IF(X2>=80,0.327,IF(X2>=75,0.255,-1)))

Explanation:

=IF(X2>=85,                  'If the value is in the highest bracket
      0.559,                 'Use the appropriate number
      IF(X2>=80,             'Otherwise, if the number is in the next highest bracket
           0.327,            'Use the appropriate number
           IF(X2>=75,        'Otherwise, if the number is in the next highest bracket
              0.255,         'Use the appropriate number
              -1             'Otherwise, we're not in any of the ranges (Error)
             )
        )
   )

回复收藏 0 原文

~没有更多了~