Progress % based on status with cell linking
Hi
I have two sheets, one acts as a master file for top level view only, the other is a more working doc.
On the working doc, I have added a progress column, calculating the percentage of progress of the project based on the progress i.e. =IF([Activity Status *]@row = "Not Started", "0%", IF([Activity Status *]@row = "On Hold", "25%", IF([Activity Status *]@row = "In Progress", "50%", IF([Activity Status *]@row = "Complete", "100%"))))
I have the results linked to the toplevel sheet and that is working fine but the issue I have is that i need to calculate the overall progress of the project based on these results. Normally, team members were manually filling in the % of each task but it wasn't based on the progress status. I had a formula calculating the total percentage divided by the number of tasks to give me the overall project progress %.
Now that I have this new process, which is cell linking to the master file, I can not calculate the overall project progress, I get 0%. I am guessing this is because the formula doesn't see the task results as numbers because they are celllinked, acting as a mirror.
Below is an image of the master file, the result is always 0%. I have tried to change the outcomes from 0% to .0 etc but same result.
Any help??
Best Answer

The issue is actually with your source formula (the nested IFs). When you wrap something in quotes, it becomes a text value which cannot be used in math functions such as AVG, SUM, etc..
To output a usable percentage, you would need to update that formula to output numbers and make sure they are decimals (Smartsheet treats percentages as a portion of 1), so 25% = 0.25, 50% = 0.50, so on and so forth. You can then format the column this formula is in to show as percentages.
Answers

The issue is actually with your source formula (the nested IFs). When you wrap something in quotes, it becomes a text value which cannot be used in math functions such as AVG, SUM, etc..
To output a usable percentage, you would need to update that formula to output numbers and make sure they are decimals (Smartsheet treats percentages as a portion of 1), so 25% = 0.25, 50% = 0.50, so on and so forth. You can then format the column this formula is in to show as percentages.

@Paul Newcome You, as always, are a legend!!!! Thank you so much!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!