Multiple IF-AND Statements in an Equation -- Having Trouble Adding a Simple IF Statement to It

[Carrie2525]

I have this lengthy equation that is just a bunch of IF-AND statements that are pieced together. The equations all work, individually (I tested.) The long equation also works until I try to add this last condition that is just a simple IF statement. I'm sure I'm just messing up a parenthesis, or something minor, but I cannot get it to work. So frustrating to be this close and still miss.

Here are all of the equations I need to piece together:

=IF(AND(O7-(O19-29)<91,O17="I"),O19+90)
=IF(AND(O7-(O19-29)>90,O7-(O19-29)<180,O17="I"),O19+179)
=IF(AND(O7-(O19-29)>179,O17="I"),"Indefinite")
=IF(AND(O7-(O19-44)<91,O17="E"),O19+90)
=IF(AND(O7-(O19-44)>90,O7-(O19-44)<180,O17="E"),O19+179)
=IF(AND(O7-(O19-44)>179,O17="E"),"Indefinite")
=IF(O15="N","N/A")

Here is the long formula that works, minus that last (only) IF statement:

=IF(AND(O7-(O19-44)>179,O17="E"),"Indefinite",IF(AND(O7-(O19-44)>90,O7-(O19-44)<180,O17="E"),O19+179,IF(AND(O7-(O19-44)<91,O17="E"),O19+90,IF(AND(O7-(O19-29)>179,O17="I"),"Indefinite",IF(AND(O7-(O19-29)>90,O7-(O19-29)<180,O17="I"),O19+179,IF(AND(O7-(O19-29)<91,O17="I"),O19+90))))))

To clarify, these 7 equations have covered every scenario--there are no other options. Also, each equation is exclusive--only one can be TRUE at a time.

I sincerely appreciate any help you can offer.

Thanks,

Carrie

 

[Simon4s]

how come you do o7-(o19-29) and not o7 - o19- 29 and you need to clsoe your and commands

 

[AhoyNC]

See if this works:

Excel Workbook
	B
10	N/A
Sheet

@Simon4s
o7-(o19-29) is not the same as o7 - o19- 29
See example below:

Excel Workbook
	A	B
1	100	50
2
3	21
4	79
Sheet

 

[Carrie2525]

Simon, because O19 is a date.

 

[Carrie2525]

Ahoy, your formula didn't return any errors, but the IF statement doesn't override when it is true. I currently have O15 as "N", and the equation cell is still showing "Indefinite." If this were working correctly, it should show "N/A."

Should I restructure the way I've written this thing?

 

[MarcelBeug]

Looks to me like the last condition is not exclusive: apparently one of the previous IF's already evaluated to TRUE.
You can use formula evaluation on the Formulas tab to verify what's actually happening.
The last IF will only be evaluated if all previous IF's evaluate to FALSE.
Perhaps you should evaluate O15 = "N" as the first IF-statement?

By the way I was also wondering if you want "N/A" or NA().

 

[Carrie2525]

Marcel, I meant for the cell to populate with the text "N/A". I never knew about the evaluate formula option. I will try to play around with it and see if I can figure it out.

I think you're right--the last condition is not inclusive. Even if I have "N" in cell O15, as long as I have data in the other cells, the equations will still run. I assumed that if I added the simple IF statement, it would override all others, if it were true. Maybe that's not how it works...does Excel give priority to equations based on the order you list them? For example, if all of these were included in one long formula, would it matter if I put the simple IF statement at the front or the back?

Cell O15 is not referenced in any of the other equations. Perhaps if I make it a part of all the other equations, it will work? (I could add another logic to thee AND condition, whereby AND O15="Y"...)

 

[MarcelBeug]

Carrie, it looks to me that you are a bit mistaking about the IF function.
The syntax is

=IF( < condition > , < value if true > ,[ < value if false > ] )

So it's rather an IF...THEN...ELSE statement then an IF-Statement.

In your formula, all < value if false > parts are new IF functions, except for the last one in which the optional < value if false > part is omitted.

So the conditions will be evaluated in the order in which they appear in the formula. E.g. if you would start with 015 = "N", then the formula evaluation will stop if O15 = "N" indeed and the other parts will never be evaluated,

Hope this is helpful.

 

[Carrie2525]

Marcel, I think this is finally working for me. You were correct -- I did not understand that the order of the statements made a difference, though I was starting to suspect that. I changed my simple IF statement to an IF-AND, and I placed it at the front of the formula. So far, it is working really well.

I need to move this formula to another sheet, but I need it to pull data from the same cells as it does, now. Does Excel have a fancy button that can "hard-code" the equation for me? The $$'s don't work when you move between sheets--only when you stay on the same sheet.

 

[MarcelBeug]

Does Excel have a fancy button that can "hard-code" the equation for me? The $$'s don't work when you move between sheets--only when you stay on the same sheet.

Yes, you can simply drag the cell with your formula down to the desired tab and together with ALT, the formula will move to the other tab with correct cell references.