Total Site % Complete + health

RobNY2

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😀

RobNY2

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

Jeff Reisman

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

RobNY2

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

RobNY2

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