Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula to check percentage total
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
Comments

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