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
Comments
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!