Forum Discussion
Conditional Formatting a column of a Table when over 100%
I have a large excel file containing projects with their associated propose cost, invoice cost, etc.
I set up a conditional formatting rule, where if the column for "total Percent Used" is greater than 100%, then the column for "Add-Service" of that row should highlight (to point out the fact that we have used up our budget).
I have an issue where when I input new projects, and no time has been put to the project yet, (so it is 0% total % used), the Add-Service column is highlighting. I cannot figure out why this is. If even .25 hours is added to the project, the highlight goes away.
Any idea why this conditional formatting is affecting my 0% the same as if it was 101%? Thanks in advance!
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)
5 Replies
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)
Unfortunately, I cannot share the file due to private information (an unable to replicate the issue). Here are two screen grabs of my current situation with 5 rows that are highlights in the ASR column, even though their total % used is 0%, and the second image is if I manipulate a row to be over 100%, how it highlights as it is supposed to.
I've also attached an image of the formatting rule I have in place, which is actually for >101%, as I thought changing it from 100% might help (it didn't). The formula is =$S7>+101%
Note: Column S is the "Total % Used" column.
