Avg formula % complete based on a specific end date

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

Find more posts tagged with

Hospitality

Formulas

Reports

Support

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.