-
COUNTIFS Percent Range Question
Is there a formula I can use to reference separate sheets and get a range of items within a set percentage? Such as all items between 0 and 25%, 26% and 50%, 51% and 75% and 76%-100%? I have tried the below formula but it does not report correctly. What am I missing?: COUNTIFS({Completed Strategy Updates (Strategy Prima…
-
RYG formula
Can anyone help with a formula that uses an RYG (red, yellow, green) to set the status of a task based on its percent complete: If a task is less than 50% complete, turn the RYG light "Red" If a task is between 50-99% complete, turn the RYG light "Yellow" If a task is 100% complete, turn the RYG light "Green" Thanks
-
Count of Children Incorrect
Smartsheet Community: My Formula Is the Incorrect total number of Children. I am sure it is a Minor Tweak but I cannot seem to find where I need to correct my formula. Current Formula: ="Site Execution: " + COUNTIF(CHILDREN([% Complete (Actual)]@row), 1) + " of " + ((COUNTIF(CHILDREN([% Complete (Actual)]@row), 1) +…
-
How to populate cell(s) with content from other cell(s) if value of helper column is yes
I am building a form right now that asks for the user's delivery address and their legal address. Instead of filling out the address twice (if it is the same), can I set it up where if they are the same, the cell(s) for the Delivery address populate the cell(s) for the Legal address? My idea was to add a field beneath the…
-
Using completion percentage and a if statement.
Hello, I am trying to do a if statement to subtract a value if my completion percentage is 100%. Here is my formula. =SUM(Duration5, Duration7, Duration10, Duration12, Duration15, Duration17, Duration20, Duration22, Duration25, Duration27, Duration30, Duration32, Duration35, Duration37, Duration40, Duration42) - IF([%…
-
Combine Child | Parent formulas into Column formula
I'm trying to redo our the Status formula for our project plan into a Column formula. Parent row has this formula: =IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN([Phase Status]@row)), "Blue",…
-
Is it possible to use a multi-select cell as the criterion for a COUNTIF formula?
I have one sheet with training items for different positions (All, BD, CTL, etc.) As these items are trained they are marked as "Complete" from a list of "Not Started" "In Progress" "Complete". I have a separate sheet that I would like to use to track training progress for the employee. It gets complicated because an…
-
Locking Values in Row
I have a column on Sheet 1 that has a formula that references a value on Sheet 2. If the value on Sheet 2 changes, obviously the cell in Sheet 1 updates automatically. I have another cell in Sheet 1 that is a dropdown. One value in the dropdown is "Completed". The values in Sheet 2 may change once a year, as we adjust…
-
I am wanting to use RGB dots for elapsed time
I am wanting to see if there is a way to utilize the RGB dots to represent elapsed time when a status drop down is changed. Would like GREEN if less than 24 hrs Yellow if over 24 hours but less than 48hrs Red if over 48 hours. Is this possible in SmartSheet?
-
Add Not Blank to formula and display word
I need to add another element to the following formula. The below formula works beautifully, however now I need to look at another column to see if there is a date displayed and if so (not blank) then display the word "Termed". Current formula: =IF(AND([Trailing 24M ratio]@row < 1, [Trailing 12M ratio]@row >= 0.9), "Yellow…