Avg formula % complete based on a specific end date

aureliem

Hello,

I would like to know if it is possible to create a formula that calculate the average % complete for all tasks that are ending at a specific date (important milestone) in my schedule. The schedule I have have a lot of tasks going all the way to 2020 but I want to track only those that must be completed by Nov 2018. 

Is that something possible? I first ran a report to show all tasks due by that date but i can't add a formula in the report (unless I just don't know how to do it).

Any advice / help from the community would be welcome!

Thank you

mwiggins

Could you post what you have for columns? It may help if you have a dedicated date column which can be accessed in a function. 

Paul Newcome

You're going to need a helper column. You can shove it all the way to the right and "Hide" it to keep your sheet looking clean. For this example we will call this column "Month".

In that column you will enter and drag fill for all of your rows:

=MONTH([End Date Column Name]).

You would then use the following to average them:

=SUMIF(Month:Month, 7, [% Complete Column Name]:[% Complete Column Name]) / COUNTIF(Month:Month, 7)

 

What this does is if the month is 7 (or whatever month you want), then it will add your percentages together. Then it will divide it by how many rows have the month of 7, effectively giving you an average.

Just make sure that the cell you are entering the final formula in is formatted as a percentage, and you should be good to go.