Conditional formatting not working on formula

I have 2 cells, e.g A1 and B1. A1 contains the text Total, B1 the value but B1 isnt a typed number, it is result of a function and even formatted as %.

| Total | 10 | where the B2 10 is with function of C1, =20/C1

I want to make different color conditions based on teh result but it seems for the program result != value, so if I typed e.g 10 (let’s say t his is the result) conditional formatting will work but when function gives this result it won’t work, style won’t apply…

I want both the cell A1 with text Total and B1 with the formula to change based on B1’s result of the formula. So if im at 100 I can set some green style if 0 some red, is it even doable?

Edit: Seems it won’t accept value displayed as percentage or the value in the condition. As temp solution I had to Format As Number, no decimals. Still don’t know how to make the text Total also change based on it

if 0 some red,

How will you ever get 0 if your formula in cell B1 is =20/C1 ? Please provide the most important thing: How does your Conditional Formatting look like?


please check the following file: TotalConditional.ods

Made the following sample conditions:

Condition 1:$B1 >= 100: green background
Condition 2:$B1 >= 10: yellow background
Condition 3:$B1 < 10: red background
Cell Range: A1:B1

and B1 contains formula: =20/C1 (just play with data input in cell C1)

Note Of course you may need to adapt the cell styles (i.e. styles BGGreen, BGYellow and BGRed) to fit your preferences.

Hope that helps.

Yes that works thanks. I use multicondition like AND($D89>10,$D89<=20) so I apply to the two cells I want like that and the color changes for both the cell with the formula and the name cell. But when I paste new values from those cells that take part in the formula and I get like less than 100%, the color format clears. Never mind redid them and it worked