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)
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)