combining three "if" formulas in addition to "else" statement"-CodePudding

I have three conditions:

=IF(AND(D2 = "Senior executive",C2=3),"Happy","Very Happy")

=IF(AND(D2 = "Junior executive",C2<=5),"Happy","Very Happy")

=IF(AND(D2="Salesman",C2<=3,2<=7000),"happy","very happy")

in case any of them is true, the cell should be "happy" or "Very happy" according to the conditions. if none of them is true, it should be "unhappy". I'm currently stuck on how to combine the three. maybe I'm thinking about it wrong? any help will be greatly appreciated!

CodePudding user response：

This would be easy in a programming language with stateful variables to "remember" conditions, or with intermediate "helper" tables. But to do it in one formula means having to repeat some of the tests. I'll show it in two steps so it's easier to follow the logic, but it will end up being one formula:

Step one is this:

=IF(OR(D2="Senior Executive",D2="Junior Executive",D2="Salesman"),"Maybe","Unhappy")

So we're part way there. Now we just replace the "Maybe" condition with another set of tests that are (or(and(),and(),and()):

=IF(OR(AND(D2="Senior Executive",C2=3),AND(D2="Junior Executive",C2<=5),AND(D2="Salesman",C2<=3)),"Happy","Very Happy")

See where I'm headed with this? You replace "Maybe" in the first snippet with that second snippet. And you get:

=IF(OR(D2="Senior Executive",D2="Junior Executive",D2="Salesman"),IF(OR(AND(D2="Senior Executive",C2=3),AND(D2="Junior Executive",C2<=5),AND(D2="Salesman",C2<=3)),"Happy","Very Happy"),"Unhappy")

Good luck!

CodePudding user response：

Based on your criteria then this works:

IF(IFERROR(VLOOKUP(D2,G2:G4,1,0),0)>0,IF(AND(D2="Senior executive",C2=3),"Happy",IF(AND(D2="Junior executive",C2<=5),"Happy",IF(AND(D2="Salesman",C2<=3,2<=7000),"happy","very happy"))),"Unhappy")

For the vlookup, cells G2:G4 have Senior executive, Junior Executive and Salesman in them.

Left the condition of testing that 2 is less than 7000, but you can add the real condition.