Progress % based on status with cell linking

Lisa B 2022

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 top-level 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 cell-linked, 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??

Paul Newcome

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

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.

Lisa B 2022

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

Paul Newcome

Happy to help. 👍️