-
Formula to calculate % Complete (numeric value) from Status (not started, in progress, complete)
I want to calculate the following and have tried IF/Then formulas - but get "unparceable" every time. not started = 0% in progress = 50% complete = 100% The columns I have (Task, Phase, Milestone, Start date, End Date, Responsible, Status, % Complete, Health, Notes. The Health column auto-calculates based on the Status…
-
AVG number of tickets per day
I am wandering the best way to create an average number of tickets entered per day, but only if at least one ticket was entered that day, for a rolling 12 months based upon a creation date. I am so new to Smartsheet that this has me all twisted up like a pretzel.
-
How do i write a formula to calculate YTD totals of a given category
Here is the formula i started with. It gives me a #unparceable response =SUMIFS(Amount:Amount, [Expense Category]:[Expense Category], Description@row), Date:Date, YEAR(@cell) = YEAR(TODAY())) Ive included a screen shot
-
Workspace - adding a new sheet to a folder - report is not picking up that new sheet
I have a question about workspace and folders and reports. I have a company workspace with 3 folders in it; Active jobs, cancelled jobs and completed jobs. I have a report that pulls from the active jobs folders, but when I create a new sheet that report does not automatically pull in that new sheet. My question is, what…
-
IFError
Good Day Smartsheet Community: Can I add IFError to this Formula? I tried several times but I keep getting an incorrect or Unparseable =ROUND(AVGW(CHILDREN([% Complete (Planned)]3), CHILDREN(Duration3)), 2) Thanks
-
Can I have blank fields treated as zero in functions
Hi, I am trying to average a range of cells. Two questions: Most important - in some cases the cell is empty. That should be treated as a zero but the AVG function is ignoring the blanks and only taking the average of the populated cells. If there happens to be text in the field (there should not be), then the field can be…
-
Calculating Days Between Estimated and Actual Start Dates (including projects which haven't started)
Hi! I've got a project tracking sheet that has the following columns: Estimated Start Date Actual Start Date I need to calculate how long a project has been waiting to start. Some projects don't have an actual start date, though. So the formula needs to know that blank dates in the actual start column should be counted as…
-
SUMIF statement based on dates
I would like to create a SUMIF equation to only sum data (column F) based on a date range (column B) . It works in excel with the following equation for reference. =SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)-SUMIF($B$9:$B$2000, ">=9/30/2018", $F$9:$F$2000) However, when I import it to smartsheets, I get an error. My…
-
Count of dropdown options (like you can with a pivot table?)
I've created a quick Form to help my HR team collect votes from staff on 4 key areas. I've used the "Dropdown (multi select)" column type, and the Form asks staff to select the 2 options they rank as most important from these lists. Is there an easy way to then see how many votes have been allocated to each option? Through…
-
COUNTIFS
I am trying to count number of lines from project master list that are in a certain region and when the Forecast $ is greater than the Actual $. Looking for projects that were under the Forecast $. Simple formula but will not work. Please help. =COUNTIFS({Region}, =Label$3, {Forecast $}, >"0", {Forecast $}, >{Actual $})
