Nested If Statement

**meerimyoo** · 01-25-2010, 03:24 PM

Help Please!!! What I am trying to do is make one continuious statement. This is what I had (and it works) but now need to add to it.

NEW SETUP:
Min Max Temp 1
7.4 25 17

=IF(ISBLANK(H9),"Incl.",IF(AND(ISBLANK(D9),ISBLANK(F9),ISNUMBER(H9)),"Ref.",IF(AND(ISBLANK(D9),ISBLANK(F9),H9="pass"),"P",IF(OR(AND(ISNUMBER(D9),ISNUMBER(H9),ISNUMBER(F9),H9>=D9,F9>=H9),AND(ISBLANK(D9),ISNUMBER(E9),ISNUMBER(F9),F9>=H9),AND(ISNUMBER(D9),ISNUMBER(H9),ISBLANK(F9),H9>=D9)),"P","FAIL"))))

But I need help adding the soft min and max. I am getting an error stating that I have too many nested if statements.
It should say " "=Pass, "R" when Temp1 is blank, "I" when all is blank, "F" when less than Min and greater than Max, "W" when they are greater than or equal to the soft max but less than the max and less than or equal to the soft min but greater than the min. and P when all else are false. I am close but I have tried so many different ways. Thanks.

SETUP:
Min Soft Min Max Soft Max Temp 1
7.4 8 25 30

=IF(ISBLANK(H10),"I",IF(AND(ISBLANK(D10),ISBLANK(F10),ISBLANK(E10),ISBLANK(G10),ISNUMBER(H10)),"R",IF(AND(ISBLANK(D10),ISBLANK(F10),ISBLANK(E10),ISBLANK(G10),H10="pass"),"",IF(OR(AND(ISNUMBER(D10),ISNUMBER(H10),ISNUMBER(F10),H10>=D10,F10>=H10),IF(AND(ISNUMBER(E10),ISNUMBER(G10),H10>=E10,G10>=H10),"W"),AND(ISBLANK(D10),ISNUMBER(H10),ISNUMBER(F10),F10>=H10),AND(ISNUMBER(D10),ISNUMBER(H10),ISBLANK(F10),H10>=D10),IF(OR(AND(ISBLANK(E10),ISNUMBER(H10),ISNUMBER(G10),E10>=H10),"W",AND(ISNUMBER(E10),ISNUMBER(H10),ISBLANK(G10),H10>=G10),"W"))),"P","F"))))

**Palmetto** · 01-25-2010, 05:22 PM

When you have to resort to this many nested IF functions it is generally a case of inefficient design in the workbook. I suggest you post a sample workbook that meets the following criteria . . .
1. EXACTLY duplicates the structure of your real workbook, and
2. Contains representative but non-sensitive dummy data, and
3. Manually shows a couple of expected results per your conditions

You should probably consider using a look up table and / or helper columns.

**mewingkitty** · 01-25-2010, 10:20 PM

Aye, that IF statement is likely easily replaced with something more efficient, and easier to update in the future. If you can upload a sample workbook we'll be able to get you fixed up much faster.

mew!