Great video - thank you. A question though: Is there a way to create a count of how many time a status appears for someone/row? As an example, how many times they completed a task, did not submit a task, submited late, attended an event, etc. I already have a column for the overall completion of any/all tasks, I just want to find out the incidents of the different categories of the tasks. Thanks.
Thank you for this! What if I need to add another custom status choice to this formula such as "In Review" and have it auto populate at 99%? Appreciate your help.
In general you can chain as many IF() statements as you want (until you hit the character limit for formulas) and whichever criteria is met first will kick in. So you would add something like the below within the formula wherever it makes sense to do so: > IF([Status Column]@row = "In Review", .99, {Next IF() statement goes here....}
I am trying to create a status formula that takes scheduled vs actual progress into account meaning “behind schedule” would mean the start date is today or prior and the % complete is less that it should be. I can do this in ms project but trying to sort out if smartsheet can do it. Thanks! This is ms project formula IIf([Scheduled Duration] = 0, (IIf([% Complete] = 100, 5, IIf([Scheduled Finish] < Now(), 4, IIf([Scheduled Finish] > Now() And [Scheduled Start] < Now() + 14, 6, 1)))), IIf([% Complete] = 100, 5, IIf([% Complete] >= 100 * (Abs(ProjDateDiff([Scheduled Start], Now()) / ProjDateDiff([Scheduled Start], [Scheduled Finish]))), 2, IIf(([Scheduled Start] > Now() And [Scheduled Start] < Now() + 14), 6, (IIf([Scheduled Finish] > Now(), IIf([Scheduled Start] > Now(), 1, 3), 4))))))
David - you could do this a few ways, the most sensible in my opinion would be: Option 1: Use hierarchy to indent all rows underneath a single header row (row 1). In this first row, the % complete could be automatically calculated via Smartsheet project settings. If you don't want to use the project settings, you could use the formula =AVG(CHILDREN()) in the % complete cell in row 1. Option 2: Add a sheet summary field and use the formula =AVG([% Complete]:[% Complete]) Option 3: If you have an external sheet for metrics, you can use a cross sheet formula of =AVG({% Complete}). Sometimes these types of formulas will throw an error, so you can make it a little more foolproof using =AVERAGEIF({% Complete}, ISNUMBER(@cell)) I'll make this my next video I do. Thanks for watching and commenting!
School of Sheets i’ll try that one. i am also having issues with forms. I created a form with a customized look but the format is not showing once i click “open update from” from the email i recieve.
@@jaiofalltrades2032 I just made a video for project level % complete, see it at ruclips.net/video/oFs-GFD6vPg/видео.html. It sounds like you're talking about an Update Request. The formatting on these can be modified with some back end helper columns, but in general, automation has limited formatting. Learn more about update requests at help.smartsheet.com/learning-track/smartsheet-intermediate/update-requests. If you're interested in a custom solution we do offer Smartsheet consulting services. Best, Dan
Great video - thank you. A question though: Is there a way to create a count of how many time a status appears for someone/row? As an example, how many times they completed a task, did not submit a task, submited late, attended an event, etc. I already have a column for the overall completion of any/all tasks, I just want to find out the incidents of the different categories of the tasks. Thanks.
Thank you! Yes, you can use COUNTIFS() to count the number of rows that meet multiple conditions.
Thank you for this! What if I need to add another custom status choice to this formula such as "In Review" and have it auto populate at 99%? Appreciate your help.
In general you can chain as many IF() statements as you want (until you hit the character limit for formulas) and whichever criteria is met first will kick in.
So you would add something like the below within the formula wherever it makes sense to do so:
> IF([Status Column]@row = "In Review", .99, {Next IF() statement goes here....}
I am trying to create a status formula that takes scheduled vs actual progress into account meaning “behind schedule” would mean the start date is today or prior and the % complete is less that it should be. I can do this in ms project but trying to sort out if smartsheet can do it. Thanks!
This is ms project formula
IIf([Scheduled Duration] = 0, (IIf([% Complete] = 100, 5, IIf([Scheduled Finish] < Now(), 4, IIf([Scheduled Finish] > Now() And [Scheduled Start] < Now() + 14, 6, 1)))), IIf([% Complete] = 100, 5, IIf([% Complete] >= 100 * (Abs(ProjDateDiff([Scheduled Start], Now()) / ProjDateDiff([Scheduled Start], [Scheduled Finish]))), 2, IIf(([Scheduled Start] > Now() And [Scheduled Start] < Now() + 14), 6, (IIf([Scheduled Finish] > Now(), IIf([Scheduled Start] > Now(), 1, 3), 4))))))
Yes this is something you could do! Just need to write it to evaluate the logic as you need.
How about a status based on the % complete for the whole column?
David - you could do this a few ways, the most sensible in my opinion would be:
Option 1: Use hierarchy to indent all rows underneath a single header row (row 1). In this first row, the % complete could be automatically calculated via Smartsheet project settings. If you don't want to use the project settings, you could use the formula =AVG(CHILDREN()) in the % complete cell in row 1.
Option 2: Add a sheet summary field and use the formula =AVG([% Complete]:[% Complete])
Option 3: If you have an external sheet for metrics, you can use a cross sheet formula of =AVG({% Complete}). Sometimes these types of formulas will throw an error, so you can make it a little more foolproof using =AVERAGEIF({% Complete}, ISNUMBER(@cell))
I'll make this my next video I do.
Thanks for watching and commenting!
School of Sheets i’ll try that one. i am also having issues with forms. I created a form with a customized look but the format is not showing once i click “open update from” from the email i recieve.
@@jaiofalltrades2032 I just made a video for project level % complete, see it at ruclips.net/video/oFs-GFD6vPg/видео.html.
It sounds like you're talking about an Update Request. The formatting on these can be modified with some back end helper columns, but in general, automation has limited formatting. Learn more about update requests at help.smartsheet.com/learning-track/smartsheet-intermediate/update-requests. If you're interested in a custom solution we do offer Smartsheet consulting services. Best, Dan