Formula to check percentage total

Phil Smith

Hi

 

I have a sheet which is pulling through some data from other sheets via cell links. The cells are being pulled from a Project Plan (with rolled up section totals).

 

Based on this data, I want to set some conditional formatting on my sheet.

 

I have:

%COMP      STARTDD        ENDDD

55%             10/08/16         22/08/16

 

I'm using a formula (hidden column) to produce a "0" or a "1" for the date based on date being older than TODAY () but I need to be able to exclude where the %COMP is 100%

 

I have added a second hidden column to produce a "0" or a "1" for the %COMP < 100 but I cannot get the formula to produce anything other than a '"1".

 

The formula for the %COMP check is: =IF(RAG10 < "100%", "1", "0")

 

I have tried the formula with and without the % after the number. 

 

Is there something that I need to add to tell the formula that I am looking at a %?

 

Thanks in advance.

 

Phil

Greg Gates

As long as "RAG10" is the cell with your percentage in it, it looks like that should be fine to me. You might try just using a whole number 1 instead of "100%" to see if it makes a difference.

 

You could also combine both of these formulas into a single cell by doing something like:

 

=IF([END DATE]1 > TODAY(), 0, IF([% Complete]1 >= 1, 0, 1))