Jan 15 2021 01:33 PM
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!
Jan 15 2021 02:19 PM
Perhaps you may provide small sample file to illustrate which exactly type of rule do you use and which are references in this rule?
Jan 15 2021 02:33 PM
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.
Jan 15 2021 02:50 PM
@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).
Jan 15 2021 08:06 PM
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)
Jan 26 2021 04:56 PM
@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.
Jan 15 2021 08:06 PM
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)