Forum Discussion
Difficulty with conditional formatting formula
I have a table, and I'd like to color code the cells in it in a specific manner. When the value in the cell reaches a target value, I want the cell to be green, when it reaches a specified interim value, I'd llike it to be yellow.
Getting the green formatting to work was relatively painless.
The target value is kept in a separate sheet and I can look it up quite easily. This part works, here is the formula I have in my conditional rule:
=A1=Maxima!A1
Now my difficulty/issue is in applying a conditional format to highlight the interim values. . The interim value is approximately 0.806494192 of the target value - give or take one or two to allow for rounding errors
To clarify: assume cell C11 has a target value of 24,007,200 then, if it has a current value of 19,361,668. it meets my criteria. Similarly, if cell G11 has a target value of 14.050,080, and it reaches 11,331,308, it meets my criteria.
Because the numbers are so large, there can be a +1/-1 error in simply multiplying the factor (0.806494192) with the target value to determine if the cell has reached the required interim value.
To resolve this, I'm needing a condition that tests true if the value in the cell is within +1/-1 of the required value. and then color it as such.
If I type this formula in a cell, I get the "true", which is what I need:
=IF(ABS(Maxima!C11*0.806494192-C11)<5, "true","False")
But for the life of me, I can't figure out how to put that into the conditional formatting formula box.
If anyone here can follow my explanation here, I'd really appreciate a helping hand/suggestion.
Thanks!
Michael, you don't need =A1=ABS(..., it's simply = ABS(...
=ABS(Maxima!C11*0.806494192-C11)<5
7 Replies
- SergeiBaklanDiamond Contributor
Hi Michael,
In this case
=ABS(Maxima!C11*0.806494192-C11)<5
You don't need IF, formula returns TRUE of FALSE itself. Just in case, your formula also could work if you use TRUE instead of "true", same for FALSE
- Michael BredenkampCopper Contributor
Thank you for your reply. I omitted, which I know is very frustrating for people trying to help, that I attempted that very same formula.
I've collected a few images to perhaps show more clearly what I have.
This formula works in some cells (those where the approximation is spot-on)
If I change it the way I think it should work, using a formula to allow for a few points variance (the formula you supplied, in the way I could get it to work in the conditional formatting text box, then not even the cells highlighted under the previous example are highlighted.
Perhaps you can spot what I'm doing wrong. Your help is greatly appreciated.
- SergeiBaklanDiamond Contributor
Michael, you don't need =A1=ABS(..., it's simply = ABS(...
=ABS(Maxima!C11*0.806494192-C11)<5