Calculating Project Level Percent Complete in Smartsheet | Smartsheet Tutorial

[@junedshaikh2964]

Is there a video for planned vs actual graph(S-Curve Graph)?

[@SchoolofSheets]

Hello! Not at the moment. If you can share an example of how you would like the graph to look and what data would be used to populate it perhaps I could make one.

[@carrierachon4430]

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.

[@SchoolofSheets]

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.

[@mbamindpodcast]

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.

[@SchoolofSheets]

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.

[@toddcharlton8935]

How do I get the status colors to show in the Gantt Chart instead of the light blue?

[@SchoolofSheets]

Use conditional formatting and modify the progress bar.

[@sgd0083]

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?

[@SchoolofSheets]

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.

[@eshmais]

@@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

[@eshmais]

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")))))

[@SchoolofSheets]

@@eshmais You mention wanting "Yellow" to appear if % Complete is both between 50-70% as well as less than 50% which would be captured by simply being less than 70%. Did you want a different color for one of these options? Anyways, as requested this would work: =IF([% Complete]@row = 0 , "Red", IF([% Complete]@row < .7, "Yellow", IF([% Complete]@row >= .7, "Green")))

[@MrSilviocelula]

Why do you explain very fast? We can't see how you type the formulas. 😳

[@SchoolofSheets]

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 :)

[@MrSilviocelula]

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!