Total Site % Complete + health
I need the total site % Complete
Each milestone has % complete for the site project
What is the sample formula to calculate each milestone % complete columns to give the site Total % complete?
Let’s say, Milestone ABCDEF etc
I just want to SUM all the milestone % complete to give me the total project % complete
Then the Project Health column will average all health columns to give me the Project Health color
There colors are red, yellow, green and columns that have Grey color it will not count for the average.
Thank you very much
Rob😀
Best Answer

Jeff
I think I found the solution:
=AVG([Quote % Complete]@row, [Order Validation % Complete]@row, [Circuit % Complete]@row, [Equip % Complete]@row, [Intro & Discovery % Complete]@row, [Docs % Complete]@row, [TT % Complete]@row, [Go/no GO Pre Deployment schedule % Complete]@row, [Go/No Go % Complete]@row, [LAN Migration % Complete]@row)
If this is not right to give me the average for all milestones columns, please let me know
Thanks for your help
Rob
Answers

@RobNY2 To get your overall % Complete, you'll want to either use a separate column or a summary field, formatted for %. Then try this formula:
=SUM([Milestone % Complete]:[[Milestone % Complete]) / COUNT([Milestone % Complete]:[[Milestone % Complete])
If you want to exclude everything that is gray, use SUMIF and COUNTIF instead, to exclude any value under whatever percentage works out to gray:
=SUMIF([Milestone % Complete]:[[Milestone % Complete], @cell >= .25) / COUNTIF([Milestone % Complete]:[[Milestone % Complete], @cell >= .25)
Then for overall project health you would just use your same formula you're using to calculate health for each milestone.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Jeff
What I found out is that I need average of all milestone columns % complete and not a SUM of them. If the cell has "N/A" the formula will skip that cell
Rob

Jeff
I think I found the solution:
=AVG([Quote % Complete]@row, [Order Validation % Complete]@row, [Circuit % Complete]@row, [Equip % Complete]@row, [Intro & Discovery % Complete]@row, [Docs % Complete]@row, [TT % Complete]@row, [Go/no GO Pre Deployment schedule % Complete]@row, [Go/No Go % Complete]@row, [LAN Migration % Complete]@row)
If this is not right to give me the average for all milestones columns, please let me know
Thanks for your help
Rob
Help Article Resources
Categories
Check out the Formula Handbook template!