Averageif for a goal of 90%
Hello, I am trying to get my averages for monthly totals of projects. The goal is 90% but i am not sure how to word the formula to take that into consideration when calculating the average. Any insight is greatly appreciated.
Answers

Are you able to provide some screenshots for reference?

So the count is for projects completed on time per month. I just need to show out of a goal of 90% what is the current percentage we are on from January to June, when i try to do the formula it gives me a 200% or an error.

Ok. How do you determine which projects were completed on time?
The basic structure of what you need is
=number_of_on_time / total

We have a section in our Portfolio which is marked as On Time by the project manager if the project is completed in the budgeted time on the project plan. From there, there is a formula/link to our metric sheet above that reports to the corresponding month columns showing the count of how many were completed on time for that month.

So are you just trying to sum up the numbers across the columns in the screenshot?

Yes get the some of the projects, which we have 13 completed for the year out of 23 committed for the year, then i need that to show as a percentage out of a goal of 90%. I believe it should be 6668%. I just can't figure out the formula formatting.

So to get the overall percentage you would use
=SUM(January@row:June@row) / 23
Then you need to compare that to 90% and basically say that if the overall percentage is 90% then output 100% and scale down evenly until you hit 0% for 0%?
Why not just show the overall percentage next to the goal?
Help Article Resources
Categories
Check out the Formula Handbook template!