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

Phil Smith
edited 12/09/19 in Archived 2016 Posts



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:


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.




  • Greg Gates
    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))

This discussion has been closed.