SOLVED

Conditional Formatting a column of a Table when over 100%

%3CLINGO-SUB%20id%3D%22lingo-sub-2065908%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20a%20column%20of%20a%20Table%20when%20over%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065908%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20excel%20file%20containing%20projects%20with%20their%20associated%20propose%20cost%2C%20invoice%20cost%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20set%20up%20a%20conditional%20formatting%20rule%2C%20where%20if%20the%20column%20for%20%22total%20Percent%20Used%22%20is%20greater%20than%20100%25%2C%20then%20the%20column%20for%20%22Add-Service%22%20of%20that%20row%20should%20highlight%20(to%20point%20out%20the%20fact%20that%20we%20have%20used%20up%20our%20budget).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20issue%20where%20when%20I%20input%20new%20projects%2C%20and%20no%20time%20has%20been%20put%20to%20the%20project%20yet%2C%20(so%20it%20is%200%25%20total%20%25%20used)%2C%20the%20Add-Service%20column%20is%20highlighting.%20I%20cannot%20figure%20out%20why%20this%20is.%20If%20even%20.25%20hours%20is%20added%20to%20the%20project%2C%20the%20highlight%20goes%20away.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20why%20this%20conditional%20formatting%20is%20affecting%20my%200%25%20the%20same%20as%20if%20it%20was%20101%25%3F%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2065908%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2065967%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20column%20of%20a%20Table%20when%20over%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931057%22%20target%3D%22_blank%22%3E%40JamieGilmore%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20may%20provide%20small%20sample%20file%20to%20illustrate%20which%20exactly%20type%20of%20rule%20do%20you%20use%20and%20which%20are%20references%20in%20this%20rule%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2066015%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20a%20column%20of%20a%20Table%20when%20over%20100%25%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2066015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BScratch%20that%20-%20I%20was%20able%20to%20replicate%20it.%20In%20the%20attached%20sample%20spreadsheet%2C%20you%20can%20see%20the%20Sample%202%20%26amp%3B%20Sample%203%20lines%20are%20acting%20properly%20per%20the%20conditional%20formatting.%20Sample%201%20is%20highlighting%20the%20ASR%20column%2C%20even%20though%20the%20total%20%25%20used%20is%200%25%20(ie%3A%20no%20hours%20are%20on%20this%20project%20yet).%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.