Formulas work separately but not combined

[Nanaia]

I have multiple IF formulas nested and am running into a problem. Each IF formula works independently, but when I nest them together I run into a problem getting the correct answer. The cells with the formula that I'm struggling with are located in the attached file in column S of the REF sheet. The function of this column is to check to see if there is an existing program so we know whether we need to create a new program. After reviewing the attached TEST file and the nested formula please consider the following;
each formula works independently
the 1st through 4th formulas work together
the 5th-14th IF formulas work together
when nesting the first 4 formulas with the 5rd-14th formulas it doesn't work

I've tried putting the first 4 formulas in different locations within the 5th-14th formulas but still can't get it to work.
Does anyone have any suggestions?
Thank you for your time.

[h2ttp]

each formula works independently

the 1st through 4th formulas work together

the 5th-14th IF formulas work together

when nesting the first 4 formulas with the 5rd-14th formulas it doesn't work

"it doesn't work" -- what doesn't work ?

incidentally, >0 missing in the 9th

[Nanaia]

Thank you for catching the missing information.
When tested with all 14 formulas combined it gives the answer as false when it should be true. i.e.; When REF cell W3 shows 1123_4 and the same value is in EDGES & BEND ALLOWANCE column AJ the result in REF cell S3 should be TRUE not FALSE.

[h2ttp]

the trouble is already in row 3 ?
that counts in column AE (not AJ)
so, should indeed say FALSE

[Nanaia]

For the example I listed above, S3 should be checking to see if B3 is V and CSV!C3 is LVLZ and C3 is ALUM and V3 is _4 then checking 'EDGES & BEND ALLOWANCE' column AJ (not AE) for the value shown in W3. If you use the 14th IF formula in S3 independently or nested with the 5th through 14th formulas it works properly showing as true. When the 1st four IF formulas are combined with the 5th through 14th formulas it stops working and I can't determine why.

[joeu2004]

@Nanaia.... I have not followed the details of this thread, but perhaps the following observation will help.

The 5th condition is: IF(AND(B3="H",LEFT(CSV!C3,3)<>"LVLZ",C3="ALUM",V3="_2"),... .

If that condition were true, you would not get the intended result, namely COUNTIF('EDGES & BEND ALLOWANCE'!$Z$2:$ZF$501,W3)>0 .

Instead, you would get the result COUNTIF('EDGES & BEND ALLOWANCE'!$Y$2:$Y$501,W3)>0 .

That is because the 1st condition would also be true, namely: IF(AND(B3="H",C3="ALUM"),... .

Excel stops processing as soon as any condition is true.

In this example, one correction is to reverse the order of the 1st and 5th conditions. In other words, the more restrictive conditions must be tested before the less restrictive included conditions.

Alternatively, perhaps the entire formula should be restructured. I have not given that any serious thought.

[h2ttp]

they work in the order you told Excel to nest them

[h2ttp]

so joeu2004 beat me to the answer.
yes, please re-order the IFs.

[Nanaia]

@joeu2004 - THANK YOU! Your detailed explanation about ordering the formula's with the more restrictive conditions first instead of last appears to have done the trick. I thought it was the other way around - listing the formulas with fewer conditions before the ones that are more restrictive.
Thank you all for your help!

[Nanaia]

And apparently I spoke too soon. I re-ordered the formulas and it gave the correct results (true where it should be in lieu of all false) then went in and corrected the COUNTIF for the 5th condition - it should not be columns $Z$2:$ZF$501, it should be only column Z. So $Z$2:$Z$501. After correcting this the combination went back showing all false in the first 20 rows of the spreadsheet when there should be some true. Is there any other criteria I should be following since so many have a similar number of restrictions?

ADDENDUM: Please disregard. I had a misplaced comma that threw the whole thing off. After correcting this it does appear to be working. Thank you again for your help.

[AliGW]

Given the complexity of this nested formula, here's my suggestion: have a column for each of the formulae, and then have your final output in a column that brings those other column results together. The helper columns can be hidden easily enough.

Sometimes building everything into one formula is more trouble than it's worth.

[Nanaia]

I'm still curious to know the answer to this thought: Is there any other criteria I should be following since so many have a similar number of restrictions?

[AliGW]

You need to make sure that you deal with the bigger issues first, as others have said. Consider this:

=IF(AND(A1=1,B1=1),TRUE,IF(B1=1,FALSE,""))

What will the outcome of this be if the first condition is met?

What would the outcome be if the second condition had been put first, and the AND condition were met? Would it be the right answer?

I hope this helps.

[Nanaia]

@AliGW - I agree with you that combining formulas can be troublesome. I can create a column for each criteria check easily enough. What would you suggest for a way to connect (is that the right word?) those newly made columns so I have one true/false reference column like I do now? The CSV that we paste into the CSV sheet is constantly changing, which is why I combined so many formulae to begin with. What formula would be used to look through the new columns and come back with a TRUE if any one column is true?

[AliGW]

Can you provide the workbook with all the separate columns (before trying to combine them)? That way, one of us might be able to get it working for you.

Sorry, I missed your last question! That would be:

=COUNTIF(A2:Q2,TRUE)>0

[Nanaia]

Here is the 2nd version with the formula seperated into individual columns. The column headed Prog? (AI) is where I put the COUNTIF formula that was suggested. It appears to work. With 14 additional columns my reference sheet looks busier that it used to!

[AliGW]

I’ve logged off the laptop now, but will have a look tomorrow, but remember you can hide these helper columns.

[AliGW]

OK, Nanaia - I think this is it:

Please Login or Register  to view this content.

Following the logic I was talking about in post #13 and what others have said already, I've placed the IF statements with more conditions first, as they need to be dealt with before those with fewer, otherwise those with fewer will cause a false positive and stop calculations early on.

I did have to correct the parenthesis to make this work, so the syntax looks like this:

=IF(AND(...),COUNTIF(...)>0,IF(AND(...),COUNTIF(...)>0,...)) and so on ...

Those brackets (braces) that are relevant to the IF statements are in red. If you get the parenthesis wrong, then calculations can be skewed, as the order in which Excel does things depends on it. Obviously at the end you need your fourteen closing brackets (braces)! If you put any of these earlier on, then you will confuse Excel and not get the results you are expecting.

I have tested this and it seems to pull the results you are wanting. Let us know. I hope it helps, anyway.

As an aside, I used Word to build this, putting each formula on a separate line with the correct parenthesis and comma layout. I then jiggled the order to get that right, then copied the whole lot back into Excel and added the closing brackets. If you expand the formula window, you'll be able to see the whole thing in the attachment.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.