Forum Discussion
Complicated formula, need some help!
- Sep 20, 2021
If I were you, I'd create what's called a "helper column" to do the testing, produce a TRUE or FALSE result, and use that as the basis for the conditional formatting, rather than building all of the logic into the formula in the conditional formatting dialog box. It is possible, as Sergei has demonstrated, to build it all into the Conditional Formatting rule, but those are tricky enough to begin with.
In your case, you might even create two or more helper columns, one to test for current position qualification, another to test for sufficient points, and then a third that nets out the result and serves as the basis for the conditional format color.
Using helper columns is a way of making the logic more visible, highlighting where the discrepancy might exist (or an error: somebody you know qualifies yet isn't turning up). It's a truism in computer history--spreadsheet history--that we can start trusting the results ("It's what the computer is telling us!") when in fact there's a subtle error embedded somewhere in a formula. So visibility of the variables, the assumptions, the tests, isn't a bad thing. Once you're confident, having truly tested it, you can hide the helper columns yet still have them there.
So in the attached I created a single helper column to illustrate the process. I may have misunderstood your criteria, but it still can illustrate the process.
That could be
=( ($B2="SPC") + ($B2="CPL") )*($D2>= LOOKUP($C2,$A$11:$A$12,$B$11:$B$12) ) + ( $B2="SGT" )*( $D2>= LOOKUP($C2,$A$11:$A$12,$D$11:$D$12) )