-
How to create a formula column that calculates the difference between rows?
I have a sheet1 with columns labeled "Metric Name," "Plan-Actual," and monthly columns where we record the plan and actual numbers consecutively for the same metric name. Metric Name Plan - Actual Last FY - Jun Last FY - Jul Last FY - Jaug Test-01 Plan 90 89 21% Test-01 Actual 90 79 78 Test-02 Plan 90 100 120 Test-02…
-
Why didn't my original CountIF formula work?
I am trying to count the number of projects that began since Jan 1, 2024. I typed in the formula =COUNTIF([Start Date]:[Start Date], ">= Jan 1, 2024"). Basically I wanted to count the number of projects with a start date that were equal to Jan 1, 2024 or greater. However that formula gave the blatantly incorrect answer.…
-
Reoccurring Due Date Formula
Hi there, Is there a formula that would do the following: 1.) If Timeframe="Monthly" and Status ="Complete" then update Due Date to the same day next month. 2.) If Timeframe="Quarterly" and Status ="Complete" then update Due Date to the same day in 3 months. (Ex: if something is due 6/30/24 and is complete, then update due…
-
Why is 'DAY' Value from a Date being populated incorrectly?
I have a 'Formatted Date' column with a column formula (below) that formats the date from the row's date timestamp, which I renamed to 'Training Completion Date', into DD-MMM-YYYY format. All rows but one is getting the correct day from the DATE. I cannot figure out why 10/08/24 returns 9-Oct-2024. Curious to know if…
-
Counting items in a cell separated by comma
Hello, Thank you in advance. Need help getting the count of the values in a cell that are separated by a comma. Example in a Cell I could have three values: Blue, Green, Yellow. Need to return the value 3 but I can't seem to find that anywhere in the community. Thank you again!
-
Formula Help
Hello, I have a formula to determine the days between two cells: =IF(OR([Status]@row = "CLOSED", ISDATE([Closure Date]@row) = 1), [Closure Date]@row - [Date Initiated]@row, TODAY() - [Date Initiated]@row) However, I need to add in to return "N/A" to the days open if the value within Date Initiated is N/A. I have a similar…
-
Reverse engineering a cell reference formula
Hi, Smartsheet developed a solution for my current employer before I started here. I'm an Excel power user and in the process of becoming a SS resource for this employer. That said, I feel really silly but cannot figure out what's going on in this cell. It looks like a cell or column reference but the column doesn't appear…
-
SUM Formula
I have written a formula to convert a word into a numeric value. When I try to add all the columns of the converted number to get the sum, it does not recognize the cell as a number so it returns a Zero. I feel like I need to convert the formula's conversion into a number instead of Text? Please help!
-
Counting Active Projects
Hey! I am wanting to count the number of projects in each department (e.g., Health Action) that are marked as active. I am currently using this formula where I manually enter how many inactive project to subtract from the total but I would like to have this imbedded in a formula so it updates automatically. Thank you!
-
Formula for automatically populating a cell within a column based on value from another column
Hi, I'm trying to populate column "A" with a formula only where column "B" equals "Defect" from a dropdown list. The formula I am trying to populate in column "A" is the following IF statement: =IFERROR(IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "Past Due") > 0, "Past Due", IF(COUNTIF(CHILDREN(), "At Risk") > 0,…
