Forum Discussion

Michael Bredenkamp's avatar
Michael Bredenkamp
Copper Contributor
Sep 21, 2018
Solved

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!

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 21, 2018

    Michael, you don't need =A1=ABS(..., it's simply = ABS(...

    =ABS(Maxima!C11*0.806494192-C11)<5

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond 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 Bredenkamp's avatar
      Michael Bredenkamp
      Copper 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. 

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        Michael, you don't need =A1=ABS(..., it's simply = ABS(...

        =ABS(Maxima!C11*0.806494192-C11)<5

Resources