Forum Discussion
Problem with formula for multiple conditions
I am learning how to do formulas with multiple conditions in Excel, so by no means an expert. I am trying to write a formula that accounts for 5 multiple conditions and I keep getting an error message.
Summary:
- Column A is a date field
- Column C has values of External or Internal
- Column D has values of Application or No Application
- Column E has values of Not Due, Full and Modified
- Columns F, L-O can have date values or be blank, but the presence of a date drives the outcome.
Scenario I'm trying to write the formula for:
- If the date in Column A < today's date AND if C is "Internal" AND If D ="No Application" AND if E ="Modified", AND if Columns F, N and O are not blank, then "Yes" OR
- If the date in Column A > today's date AND if C is "Internal" AND If D ="No Application" AND if E ="Not Due", AND if Columns F, and O are not blank, then "Yes" OR
- If the date in Column A is blank AND if C is "Internal" AND If D ="Application" AND if E ="Full", AND if Columns F, L, M,N and O are not blank, then "Yes" OR
- If the date in Column A > today's date AND if C is "External" AND If D ="No Application" AND if E ="No", AND if Columns F, and O are not blank, then "Yes" OR
- If the date in Column A < today's date or blank AND if C is "External" AND If D ="Application" AND if E ="Full", AND if Columns F,L,M, N and O are not blank, then "Yes"
- Otherwise "No"
This is the formula I wrote that is returning the error
=IF(OR(AND(A14<TODAY(),C14 ="Internal",D14="A-11",E14="Modified Screening Due",F14<>"",N14 <>"",O14<>"")),"Yes",
IF(or(AND(A14>TODAY(), C14="Internal",D14="A-11", E14="No Screening Due",F14<>"", O14<>"")),"Yes",
IF(or(and(ISBLANK(a14), c14="Internal", D14="Application", E14="Full Screening Due", F14<>"", L14<>"", M14<>"", N14<>"", o14<>"")), "Yes",
If(or(and(isblank(a14), c14="External", D14="Application", E14="Full Screening Due", F14<>"",L14<>"", M14<>"", N14<>"", o14<>"")), "Yes",
If(or(and <A14>TODAY(), C14="External", D14="A-11", E14=N "No Screening Due", F14<>"", o14<>"")), "Yes","No"))
Help :-(. I'm Googled and Binged out.
Thank you
=IF(OR(AND(A14<TODAY(), C14="Internal", D14="No Application", E14="Modified", F14<>"", N14<>"", O14<>""), AND(A14>TODAY(), C14="Internal", D14="No Application", E14="Not Due", F14<>"", O14<>""), AND(ISBLANK(A14), C14="Internal", D14="Application", E14="Full", F14<>"", L14<>"", M14<>"", N14<>"", O14<>""), AND(A14>TODAY(), C14="External", D14="No Application", E14="No", F14<>"", O14<>""), AND(OR(A14<TODAY(), ISBLANK(A14)), C14="External", D14="Application", E14="Full", F14<>"", L14<>"", M14<>"", N14<>"", O14<>"")),"Yes","No")
The formula is untested.
My answers are voluntary and without guarantee!
Hope this will help you.
Toward the end of your formula, note "If(or(and <A14>TODAY()…"; the AND function is not properly formed, and I think the OR function should be nested within the AND, not vice versa.
But if you are using Excel 2021 or a later version, I recommend that you not try to put everything into nested IF functions; instead use the LET function to evaluate for each rule more clearly. See the attached workbook.
=IF(OR(AND(A14<TODAY(), C14="Internal", D14="No Application", E14="Modified", F14<>"", N14<>"", O14<>""), AND(A14>TODAY(), C14="Internal", D14="No Application", E14="Not Due", F14<>"", O14<>""), AND(ISBLANK(A14), C14="Internal", D14="Application", E14="Full", F14<>"", L14<>"", M14<>"", N14<>"", O14<>""), AND(A14>TODAY(), C14="External", D14="No Application", E14="No", F14<>"", O14<>""), AND(OR(A14<TODAY(), ISBLANK(A14)), C14="External", D14="Application", E14="Full", F14<>"", L14<>"", M14<>"", N14<>"", O14<>"")),"Yes","No")
The formula is untested.
My answers are voluntary and without guarantee!
Hope this will help you.
NikolinoDE Thank you so much. That worked perfectly. I appreciate it
