Averageif for a goal of 90%

tim.curtin

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.

Paul Newcome

Are you able to provide some screenshots for reference?

tim.curtin

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.

Paul Newcome

Ok. How do you determine which projects were completed on time?

The basic structure of what you need is

=number_of_on_time / total

tim.curtin

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.

Paul Newcome

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

tim.curtin

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 66-68%. I just can't figure out the formula formatting.

Paul Newcome

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?