Calculating Project Level Percent Complete in Smartsheet | Smartsheet Tutorial
Vložit
- čas přidán 30. 07. 2024
- In this video, Dan from School of Sheets shows you different ways to determine the percent complete of your Projects at the project level using hierarchy, formulas, and sheet summary fields.
💻 FUNCTIONS USED
➣ =AVG(CHILDREN())
➣ =AVG([% Complete]:[% Complete])
➣ As a bonus, MIN() and MAX() are used to pull in the earliest start date and latest end date for the project via =MIN(CHILDREN()) and =MAX(CHILDREN())
🏢 ABOUT SCHOOL OF SHEETS
➔ We are a bespoke consulting firm specializing in developing custom Smartsheet Solutions
➔ We are a Smartsheet Aligned Gold partner with a team of certified consultants and software developers.
➔ We work with businesses of all sizes and industries.
➔ Learn more at www.schoolofsheets.com
🤝 WANT TO WORK WITH US?
Fill out our new client interest form at www.schoolofsheets.com/WorkWi...
📚 KEY RESOURCES
➔ Try Smartsheet free for 30-days www.schoolofsheets.com/TrySmar...
🔔 SUBSCRIBE FOR MORE GREAT CONTENT
👉 / @schoolofsheets
⌚ TIMESTAMPS
00:00 - Intro
00:20 - Example Sheet and Data
00:58 - Using Hierarchy
04:27 - Using Smartsheet Project Settings
07:10 - Using Sheet Summary Fields without Hierarchy
08:45 - Summary
09:20 - Outro
#smartsheet #formula #automation #projectmanagement
Is there a way that the Gantt chart "sub bars" can use the % complete based on a formula instead of manual? My issues is I keep putting in a formula to calculate the % complete but then when I use the process tracker on the gantt (sub bar), it changes it back to manual.
Unfortunately it is a built in feature and must use the project settings. You could color code the gantt bar with conditional formatting based on % complete tho.
I have children task and %complete turned on in the project settings. This is working for some parents but not others and I can’t figure out how fix the issue.
Check to see you have % complete enabled within the project settings mapped to your appropriate column.
With project settings, the summary values will be automated.
You can bypass this by omitting % complete from project settings and using a custom formula based on the changes made in your Sheet.
How do I get the status colors to show in the Gantt Chart instead of the light blue?
Use conditional formatting and modify the progress bar.
Hi, i saw that when u changed the % of completion, the symbol on the Status column changed colour. May i know how to do this?
You can use an IF() formula to change symbol columns based on values in other columns. For example, something like the below formula will turn the RYGB symbol column Blue if $ Complete is 100, Green if it is greater than 0, and Yellow if it is 0:
=IF([% Complete]@row=1, "Blue", IF([% Complete]@row > 0, "Green", IF([% Complete]@row = 0, "Yellow")))
This is a simple example but you can make it change based on a number of conditions and control for things like bad data, blanks, etc.
@@SchoolofSheets thank you for this formula. How can I modify it to accept ranges like you said in your video clip? for example if % Complete between 50% and 70% I want to show the yellow symbol and if greater than 70% green, and less than 50% yellow, and if zero show red
I did this formula but I get (#UNPAREABLE) =IF(Progress@row = 1, "Blue", IF(Progress@row >= 0.5, "Green", IF(And(Progress@row < 0.5, Progress@row > 0), "Yellow"), IF(Progress@row = 0, "Red")))))
Why do you explain very fast? We can't see how you type the formulas. 😳
Sorry if it's fast, I personally prefer videos that are efficient and to the point so I try to do this in the videos.
You can always slow the video down in the settings, but I will do the next one a little slower!
Thanks for the feedback :)
Thanks. Isn’t the velocity it’s self, but we can’t see some information. For example, you didn’t show the formula or how you click and select multiple rows automatically. Basic questions, sorry, but useful to beginners like me. Btw, thanks for prepare this video! I have sure that helped a lot of people!