# 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 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??

Tags:

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!