# How to get an average of % Complete.

Options
✭✭✭

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?

• ✭✭✭✭✭✭
Options

Try this:

=AVG(CHILDREN())

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
edited 08/23/22
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!