SOLVED

Conditional Formatting a column of a Table when over 100%

Copper Contributor

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!

5 Replies

@JamieGilmore 

Perhaps you may provide small sample file to illustrate which exactly type of rule do you use and which are references in this rule?

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.

@Sergei Baklan 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).

best response confirmed by JamieGilmore (Copper Contributor)
Solution

@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.

1 best response

Accepted Solutions
best response confirmed by JamieGilmore (Copper Contributor)
Solution

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

View solution in original post