Processing
 

Nested IF Functions in Excel not working

09/09/2016 15:54#1

DukeLuke

Member

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

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  

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) 

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".

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"))))) 

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.

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".

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")))))` 

Does anyone know why?

09/09/2016 16:20Top#2

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

The reason this is happening is that

"Outstanding >30">30 

is evaluating to TRUE.

For whatever reason, it appears that Excel treats text as greater than any number value when doing a comparison like this.

09/09/2016 16:20Top#3

Joined at: 10 months ago

Post: 21

Thank: 0

Thanked: 0

Reorganize your formula:

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

09/09/2016 16:41Top#4

ChrisB

Member

Joined at: 10 months ago

Post: 10

Thank: 0

Thanked: 0

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 

will output TRUE.

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.

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" 

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

Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

5 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re