Forum Discussion
Conditional Formatting a column of a Table when over 100%
JamieGilmore The problem is that your formula in column R for Total % Used is:
=IF(P3=0,"0%",P3/O3)
which mean your 0% is not a number but rather text. Why don't you just use =P3/O3
If you can't for some reason (and I don't know why so tell me because maybe that is something else that should be fixed), then in the conditional formatting you could add a check ISNUMBER($R3) so it would look like:
=($R3>=101%)*ISNUMBER($R3)
Perhaps you may provide small sample file to illustrate which exactly type of rule do you use and which are references in this rule?
SergeiBaklan Scratch that - I was able to replicate it. In the attached sample spreadsheet, you can see the Sample 2 & Sample 3 lines are acting properly per the conditional formatting. Sample 1 is highlighting the ASR column, even though the total % used is 0% (ie: no hours are on this project yet).
JamieGilmore The problem is that your formula in column R for Total % Used is:
=IF(P3=0,"0%",P3/O3)
which mean your 0% is not a number but rather text. Why don't you just use =P3/O3
If you can't for some reason (and I don't know why so tell me because maybe that is something else that should be fixed), then in the conditional formatting you could add a check ISNUMBER($R3) so it would look like:
=($R3>=101%)*ISNUMBER($R3)
mtarler Looks like I never responded when I thought I had! Thank you for that explanation. I did not create the original file, and it is a 5+ year long compilation of projects. I am unsure why it was decided to use that formula but that definitely explains the highlight! Thank you and I will look into what I can do about this.