Forum Discussion
Solved
IF OR AND Formula returning False value
Hello, reaching out for assistance with this formula. I am expecting a calculated value but I an only getting False. What I would really like to do is have the formula check if the cell contains "Officer" so that I don't have to keep adding in the different possible variations. Ultimately, if the H6 is Officer or First Officer and the D equals Los Angeles, multiply by 1.5, all else, multiply by 2. Any assistance is appreciated.
=IF(OR(H6="Officer",H6="First Officer"), IF(AND(D6="Los Angeles"), 1.5*SUM(K6:L6)+SUM(N6:O6)/100*M6+SUM(N6:O6)/100*2*SUM(K6:L6)+2*R6+SUM(M6,P6,Q6,S6,T6,U6),
2*SUM(K6:L6)+SUM(N6:O6)/100*M6+SUM(N6:O6)/100*2*SUM(K6:L6)+2*R6+SUM(M6,P6,Q6,S6,T6,U6)))
- So what you want is SEARCH() and you are getting FALSE because if H6 doesn't pass the criteria it never gets into the inner IF() statement. Try this:
=IF(AND(ISNUMBER(SEARCH("Officer",H6)),D6="Los Angeles"), 1.5,2) *SUM(K6:L6)+SUM(N6:O6)/100*M6+SUM(N6:O6)/100*2*SUM(K6:L6)+2*R6+SUM(M6,P6,Q6,S6,T6,U6)
notice how I take the rest of the equation outside and only return the 1.5 or 2 and I grouped the 2 conditions using AND
2 Replies
- So what you want is SEARCH() and you are getting FALSE because if H6 doesn't pass the criteria it never gets into the inner IF() statement. Try this:
=IF(AND(ISNUMBER(SEARCH("Officer",H6)),D6="Los Angeles"), 1.5,2) *SUM(K6:L6)+SUM(N6:O6)/100*M6+SUM(N6:O6)/100*2*SUM(K6:L6)+2*R6+SUM(M6,P6,Q6,S6,T6,U6)
notice how I take the rest of the equation outside and only return the 1.5 or 2 and I grouped the 2 conditions using AND
