title: Nested IF Functions in Excel not working
body: <p>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</p>
  
  <pre><code>      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 &gt;30  1         &gt;30 
      NULL        1/4/2016     Outstanding &gt;30  1         &gt;30 
      1/26/2016   1/4/2016     22               -         15-30 
      NULL        1/4/2016     Outstanding &gt;30  1         &gt;30 
  </code></pre>
  
  <p>I'm taking DateResolved - (MINUS) DateCreated to get a Response Time. This Response time has this nested IF Function:</p>
  
  <pre><code>   =IF(AE2="NULL",IF(MAX(AI:AI)-AI2&gt;30,"Outstanding &gt;30","Outstanding &lt;30"),AE2-AI2)
  </code></pre>
  
  <p>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".</p>
  
  <p>The problem lies in column AN - Response Time Bucket. Here's the formula:</p>
  
  <pre><code>=IF(AL2&lt;=7,"0-7",IF(AL2&lt;=14,"8-14",IF(AL2&lt;=30,"15-30",IF(AL2&gt;30, "&gt;30",IF(AL2="Outstanding &gt;30",AL2," Outstanding &lt;30")))))
  </code></pre>
  
  <p>I simply just want AN to return "Outstanding >30" when AL is "Outstanding >30" and to return "Outstanding &lt;30" when AL is "Outstanding &lt;30". The formula seems to stop AFTER the fourth nest, and lumps my "Outstanding" Response times into ">30", which I DO NOT want.</p>
  
  <p>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 &lt;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".</p>
  
  <p>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:</p>
  
  <pre><code>=IF(O2&lt;=7,"0-7",IF(O2&lt;=14,"8-14",IF(O2&lt;=30,"15-30",IF(O2="Outstanding &gt;30","Outstanding &gt;30",IF(O2="Outstanding &lt;30","Outstanding &lt;30","&gt;30")))))`
  </code></pre>
  
  <p>Does anyone know why?</p>