Forum Discussion
IF formula implementation with multiple logic
- Hi Nandhu19940
Try plugging this in:
=IF(AND(IF(AND(A2=1,B2=FALSE,D2="--"),"AN","-")="AN",IF(OR(C2="CEM",C2="NONCAT",C2="INFORMATION",C2="FINANCE",C2="CR",C2="PENSION",C2="UINV",C2="CTRLM",C2="JIPE",C2="CSARC"),"AN","-")="AN"),"AN","-")
Just replace "AN" with "Required action"
BR,
Dear Bran,
Your formula works perfectly and kindly explain me the concept behind that one.
Hi Nandhu19940
I basically did two tests and merged them into one....so,
=IF(AND(A2=1,B2=FALSE,D2="--"),"AN","-")...lets call this TestA, which will either return AN or fail
=IF(OR(C2="CEM",C2="NONCAT",C2="INFORMATION",C2="FINANCE",C2="CR",C2="PENSION",C2="UINV",C2="CTRLM",C2="JIPE",C2="CSARC"),"AN","-")....lets call this TestB whcih will again either return AN or fail...
Finally you combine them:
=IF(AND(TestA ="AN", TestB="AN"), "AN", "-")...meaning both separate test must return AN in order for formula to evaluate.
Hopefully its a bit clearer...I got lost myself trying to dissect it and I wrote it like 2 hours ago 😄
Soon, we will have access to new function "LET" which will help with writing formulas like this.
Best regards,
Branislav1984 Thanks lot for explaining me the concept and now i could do more better in IF conditions.
Lets wait for LET function to come in future.
Nandhu19940 Not that it matters much but just to demonstrate another, more dynamic, approach that does not require you to "hard-code" all the keywords into many nested IF statements. Create a list with keywords and give it a name. Then this will work as well:
=IFERROR(IF(AND($A$2:$A$11=1,$B$2:$B$11=FALSE,$D$2:$D$11="--",MATCH(C2,keywords,0)),"Required Action"),"x")It's demonstrated in the attached workbook. Adopt the ranges to your real situation, give them names or use structured table references to make it really dynamic.
Riny_van_Eekelen Such a wonderful way of minimizing the lengthy formula thanks lot Riny.
🙂
Queries:
1) Here in my count, com-result column values will differ and moreover rows can't be fixed in this section.
2) Dynamic rows will be there and need to implement formula accordingly so i tried below thing.
Formula:
=IFERROR(IF(AND(A2=$H$2,B2=$H$3,D2=$H$4, MATCH(C2,keywords,0)),"Required Action"),"x")
Because in your formula design i could see three column rows range is fixed but that won't workout for me and moreover my result for above formula is quite different because of using IFERROR it throws me FALSE when we not get match for A2, B2 & D2 column values with fixed one.
Performance issue:
Another on thing here is my workbook has many rows (100000 + rows) data, so when i'm implementing the formula it's very slow and taking more time to implement for all rows.
So need to give work around on performance as well and let me know any tips you can avail for performance improvement in macro running.
Issue:
Calculating 2 processors (2%), i'm getting this one while running my macro and taking too long time to finish it.
