Forum Discussion
Complicated formula, need some help!
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.
Conditional formatting rule formula applied to that range
could be
=( ($B2="SPC") + ($B2="CPL") )*($D2>=$B$11) + ( $B2="SGT" )*( $D2>=$D$11 )Just in case, multiplication means logical AND, summation logical OR
How would I go about adding that condition into the true/false?
CPL Jim Donut is a 35M with 800 points and so meets the point requirements and is highlighted green.
SPC Bingo Bongo only has 200 points and does not meet his requirements and is highlighted orange.
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.
- Thank you, this is a good idea. I tried Mr Baklans solution, but I feel that when I move that logic to my main spreadsheet, I will undoubtably mess it up. But still, thank you Mr. Balkan for the work because I know understand the HOW and WHY for what I can doing.
And Mathetes, thank you for that outside the box idea. Because there are actually several more less complicated variables to what I am working on, I think these helper columns will allow me to compartmentalism the different variables and simplify the data.
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) )- This works well, but I am not confident in my ability to reproduce the results effectively in my larger spreadsheet! Thank you for your answers, it taught me the primary info that I was looking for! You are an absolute Excel wizard.