Excel中的嵌套IF函数不工作 [英] Nested IF Functions in Excel not working

问题描述

我有一个很大的excel文档，有很多的vlookup，如果函数等。在我的一些选项卡中，我无法让我的IF功能工作，我检查了我的逻辑10次。我似乎无法弄清楚为什么excel在一定条件下停止，而不是检查其余的巢。以下是数据样本如下：

I have quite a large excel document that has a good amount of vlookups, if functions, etc. In some of my tabs I cannot get my IF Functions to work and I have checked my logic 10 times over. I can't seem to figure out why excel is stopping at a certain condition and not checking the rest of my nests. Here's what a sample of the data looks like

      AE            AI           AL           AM           AN
DateResolved    DateCreated Response Time   Dummy   Response Time Bucket
    1/7/2016    1/1/2016     6                -         0-7 
    1/18/2016   1/3/2016     15               -         15-30 
    1/25/2016   1/4/2016     21               -         15-30 
    1/25/2016   1/4/2016     21               -         15-30 
    1/25/2016   12/31/2015   25               -         15-30 
    NULL        1/4/2016     Outstanding >30  1         >30 
    NULL        1/4/2016     Outstanding >30  1         >30 
    1/26/2016   1/4/2016     22               -         15-30 
    NULL        1/4/2016     Outstanding >30  1         >30 

我是采取DateResolved - （MINUS）DateCreated获取响应时间。此响应时间具有这个嵌套的IF函数：

I'm taking DateResolved - (MINUS) DateCreated to get a Response Time. This Response time has this nested IF Function:

   =IF(AE2="NULL",IF(MAX(AI:AI)-AI2>30,"Outstanding >30","Outstanding <30"),AE2-AI2)

这是很好的，因为它是我想要的。杰出> 30天显示在AL列（DATE CREATED的最大日期是7月的某个时候，但是被截断，这就是为什么当DateResolved = NULL，最大DateCreated - DateCreated> 30时，IF语句满足的原因，响应时间返回为未知> 30。

Which is great, because it did what I wanted it to. Outstanding >30 days shows up in column AL(the max date in DATE CREATED is sometime in July, but it's truncated, that's why the IF statement is satisfied when DateResolved = NULL , max DateCreated - DateCreated > 30, Response Time is returned as "Outstanding >30".

问题在于列AN - 响应时间Bucket。这是公式：

The problem lies in column AN - Response Time Bucket. Here's the formula:

=IF(AL2<=7,"0-7",IF(AL2<=14,"8-14",IF(AL2<=30,"15-30",IF(AL2>30, ">30",IF(AL2="Outstanding >30",AL2," Outstanding <30")))))

当AL为未知> 30时，我只是希望AN返回未知> 30，并且当AL为未完成时返回未知< 30 30，该公式似乎在第四个巢后停止，并将我的杰出响应时间结束到> 30，我不想要。

I simply just want AN to return "Outstanding >30" when AL is "Outstanding >30" and to return "Outstanding <30" when AL is "Outstanding <30". The formula seems to stop AFTER the fourth nest, and lumps my "Outstanding" Response times into ">30", which I DO NOT want.

我尝试了很多不同的方法，首先我在最后一个替代替代30中替换了AL2，我也尝试在列中创建一个虚拟AM，其中公式为= if（AL2 =未知> 30，1,0），然后将其代入列AN。我将我的最后一个巢设置为IF（AM2 = 1，未知> 30，未完成<30）。它仍然没有工作。在第四窝后仍然停留，并说> 30。我甚至试图拿出数字，用ALST替换ALA中的Outstanding> 30，并将OST替换为我的公式，以便excel没有错误地读取Outstanding> 30的最后一位。

I've tried this many different ways. First I substituted AL2 in the last nest for "Outstanding >30". I also tried creating a dummy in column AM where the formula is =if(AL2="Outstanding >30",1,0) and then substituting that into column AN. I set my last nest as IF(AM2=1,"Outstanding >30","Outstanding <30"). It still didn't work. It is STILL quitting after the 4th nest and saying ">30". I even tried to take the numbers out and replaced "Outstanding >30" in column AL with OST and substituting "OST" into my formula so that the excel is not mistakenly reading the last bit of "Outstanding >30".

更新：我编辑的公式包括> 30作为我的公式的最后一块，即它是如果条件为FALSE出现什么。我不知道为什么它有效，但它是。这里是：

UPDATE: I edited the formula to include ">30" as the last piece of my formula, i.e. it is what appears if the condition is FALSE. I'm not sure why it worked, but it does. Here it is:

=IF(O2<=7,"0-7",IF(O2<=14,"8-14",IF(O2<=30,"15-30",IF(O2="Outstanding >30","Outstanding >30",IF(O2="Outstanding <30","Outstanding <30",">30")))))`

有谁知道为什么？ / p>

Does anyone know why?

推荐答案

了解Excel如何处理文本（包括包含数字的文本）很重要。当您将值Outstanding> 30与任何数字进行比较时，Outstanding> 30将始终较高。换句话说：

It's important to understand how Excel handles text (including text that contains numbers). When you compare the value "Outstanding > 30" to any number, "Outstanding > 30" will always be higher. In other words:

="Outstanding >30" > 999999

将输出 TRUE 。

有多种方法可以修改您的公式工作。我建议您在检查天数之前检查AL2 =未知> 30和未完成<30。

There are multiple ways to modify your formula to work. I suggest you check if AL2 = "Outstanding >30" and "Outstanding <30" at the beginning of your formula before checking the number of days.

这是一个返工的公式。注意使用 OR 函数。

Here's a rework of your formula. Notice use of the OR function.

=IF( OR( AL2="Outstanding >30", AL2="Outstanding <30" ), AL2, IF(AL2<=7,"0-7",IF(AL2<=14,"8-14",IF(AL2<=30,"15-30",IF(AL2>30, ">30",IF(AL2="Outstanding >30",AL2," Outstanding <30"))))))

另外，你打算在公式的末尾添加一个空格吗？

Also, did you intend to include a blank space at the end of the formula?

..." Outstanding <30"

最后，您可以考虑添加一个列，使用数字（无文本）计算响应时间。即使您使用文本保留列，也可以使用底层数字来计算文本。此外，您的公式可能会使用 TODAY（）函数作为解析的日期，其值为 NULL 如果

Lastly, you might consider adding a column that calculates the response time using numbers only (no text). Even if you keep a column with text, you can use the underlying number to calculate that text. Also, your formula might use the TODAY() function as the date resolved where the value is NULL if

这篇关于Excel中的嵌套IF函数不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！