-
Formula will function in Excel but not SmartSheet
For some reason I am having trouble getting this formula to function in SmartSheet (even though it works in Excel)... I am wanting to populate a "Y" in 'Valid Approval?' column only when one of the following 2 situations occur: 1) 'Primary Approval' says "Approved" AND 'Secondary Approval' says "Approved" 2) 'Primary…
-
Harvey balls based on range of values in % completion
I have a formula to use based on % completion of a project. I would like Harvey balls to show the status based on a range of values. It's not often that completion is exactly 25%, 50%, 75%, 100% This is my formula but how do I figure the ranges? =IF([% Complete]31 = 0, "Empty", IF([% Complete]31 < 0.25, "Quarter", IF([%…
-
Count Children in adjacent column
How do you write a formula to count certain values of an adjacent column from where you want to the count value displayed. In my sheet I need to count open positions and filled positions based on the status. My sheet is very large so I have to replicate this many times with varying row #s so looking for a quick way to tell…
-
Parent RYG(B) status balls automated based on Child status
Hello, I have been scanning the community and have found some bits for what I am trying to do but am struggling to put it altogether and was wondering if anyone could help? I have a list of tasks with headers (parents) which then have sub tasks (children) and some of them also have sub tasks (children again i believe). The…
-
Percentages
Is it possible to have .5% as a percentage. Whenever I put .05 it rounds to 1% and .04 rounds to 0%. Thank you
-
How to convert dates to Long Form Date format?
I understand that date formatting is normally set in "Personal Settings" but in order for the SmartSheet Merge to work for a problem I'm trying to solve, I need to convert the date to the long form (February 11th, 2016 instead of 11/02/16. Is there a script or IF statement that I could use to make the change? Thanks, SRC
-
Adding together multiple COUNTIFS?
Hey everyone. I'm trying to implement a formula that will allow me to reference two ranges from one sheet and follow the specified criteria that is given to them. My most recent attempt; =COUNTIFS({Unit_Type}, "CP2 - Double", {ACCS Overall}, [T2]26) + COUNTIFS({Unit_Type}, "CP4 - Double", {ACCS Overall}, [T2]26) +…
-
Remaining Budget
Good evening: I have broken down my Project Budget and spent a total of 13% of my Budget. I have used the formula to get the total sum (13%). However, I am challenged with adding to the formula to get the remaining budget of 87%. Sum Formula: =SUM(CHILDREN()) Task A: 10% Task B: 1% Task C: 2% Task D: 0% Thanks for the help…
-
Displaying a date in a non-date column
I am trying to display the earliest date in the "Start" column and the latest date in the "Finish" column, thereby getting the start and end dates of the whole project. I can get these, and display them in a third column formatted as a "date" column. Now, I want to include this text (the date) in a forth column formatted…
-
Working with Time Values in Cells
Hi all, perhaps someone can help me with a workaround. I have a sheet that captures time values in a column. As far as I know, Smartsheet doesn't recognize time or have any formulas more granular than "Date" formulas. My question is, does anyone know a way to convert time values into numeric values? For example, if the…