How to get an average of % Complete.

taylor111220

Im working on a spreadsheet that has tasks for a job status report of whether it is complete, not started or in progress. This feeds into another column called % complete which I used the IF function to turn the status into a percent. How can I get an average of the percentages?

Paul Newcome

Try this:

=AVG(CHILDREN())

Paul Newcome

@taylor111220 I see you marked this as unresolved. What exactly happened when you put the above in your parent rows that didn't work?

taylor111220

It comes back with a #Divide by zero error @Paul Newcome

Paul Newcome

That's odd. How are the child rows being populated in that column and where exactly are you putting the above formula?

taylor111220

The status row is a drop down of complete, in progress, and not started. I then used the IF function for the % complete so that if the status is complete=100%, in progress=50%, not started=0%. I want to get an average % complete for part one of the project. @Paul Newcome

Paul Newcome

Can you copy/paste the exact formula you are using to generate the % Complete?

taylor111220

=IF(Status4 = "Complete", "1", IF(Status4 = "In Progress", ".5", IF(Status4 = "Not Started", "0"))) @Paul Newcome

Andrée Starå

Hi @taylor111220

I hope you're well and safe!

Try something like this.

=IF(Status@row = "Complete", 1, IF(Status@row = "In Progress", .5, IF(Status@row = "Not Started", 0)))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

taylor111220

@Andrée Starå That worked!! Thank you.

Andrée Starå

@taylor111220

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!