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

#### Carrie2525

##### New Member
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.

Thanks,

Carrie

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
how come you do o7-(o19-29) and not o7 - o19- 29 and you need to clsoe your and commands

See if this works:
Excel Workbook
B
10N/A
Sheet

@Simon4s
o7-(o19-29) is not the same as o7 - o19- 29
See example below:
Excel Workbook
AB
110050
2
321
479
Sheet

• Carrie2525
Simon, because O19 is a date.

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?

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

Carrie, it looks to me that you are a bit mistaking about the IF function.
The syntax is
Code:
``=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,

Last edited:
• 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.

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.

• Carrie2525

Replies
1
Views
169
Replies
5
Views
570
Replies
4
Views
392
Replies
7
Views
210
Replies
4
Views
671

### Forum statistics

1,196,265
Messages
6,014,324
Members
441,816
Latest member
Klingon1960 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back