I'm Stumped  Need a Percent Average when applicable
I have tried a number of ways to get this to work, and my hunch is I'm needing a percent average from other percent averages (where some may be an iferror result that needs to be unaccounted for).
Attached is an image of the work.
I need the average percentage of projects on design schedule for those with projects in design phase. Therefore, the #unparseable cell should read 100% and exclude Blaine's "N/A" result (which is an iferror result)
The current formula as it stands is: =(IFERROR(SUM([CURRENT WEEK]57, [CURRENT WEEK]54, [CURRENT WEEK]51, [CURRENT WEEK]48, [CURRENT WEEK]45), ""))/(IFERROR(COUNTIF([CURRENT WEEK]57, [CURRENT WEEK]54, [CURRENT WEEK]51, [CURRENT WEEK]48, [CURRENT WEEK]45), NOT("N/A")), "")
Each individuals percentage is the following formula: =IFERROR(SUM([CURRENT WEEK]58 / [CURRENT WEEK]59), "N/A"). This works as it is, but not sure if it's creating an issue.
Best Answer

No worries  formulas take time to master. And even then, some are tricky!
Try this in your parent (dark grey  overall) row:
=SUM(CHILDREN()) / COUNTIF(CHILDREN(), ISNUMBER(@cell))
This will take everyone's percentages, add them up, then divide by the number of cells that are numbers (not N/A). Because it's looking for children, it won't look for the cells that are indented any further  it will only look at its child rows (the percentages).
And in your child (light grey  by person) rows  this one is for row 57:
=IFERROR([current week]58/[current week]59,"N/A")
(of course, you'll be replacing the row numbers in bold with the correct row numbers.) This will take the On schedule cell and divide it by Total Projects. If there is an error (i.e. a divide by zero error) then it will display N/A instead.
Hope this helps clarify a bit. Let me know if it works.
Best,
Heather
Answers

Hi @Tehren ,
Try this in your parent row:
=SUM(CHILDREN()) / COUNTIF(CHILDREN(), ISNUMBER(@cell))@Tehren
It seems to be working on my end  it sums only the numbers, then divides by the number of children rows that are numbers.
You could also revise your light grey formulas to the following, as the SUM is not necessary:
=IFERROR([current week]58/[current week]59,"N/A")
Let me know if it works!
Best,
Heather

I believe it is a step in the right direction. For me, it seems to be counting both children under each person, instead of the average of each person. In my example, its 14 on schedule/14 total in design, but by the suggested formula, it is calculating as 28/10, reading as 280% instead of 100%.

Perhaps there is something of a sumif with the "description column" = "on schedule" and the countif/s is number and "description column"  "on schedule?" Sorry, formulas are not my strong suit.

No worries  formulas take time to master. And even then, some are tricky!
Try this in your parent (dark grey  overall) row:
=SUM(CHILDREN()) / COUNTIF(CHILDREN(), ISNUMBER(@cell))
This will take everyone's percentages, add them up, then divide by the number of cells that are numbers (not N/A). Because it's looking for children, it won't look for the cells that are indented any further  it will only look at its child rows (the percentages).
And in your child (light grey  by person) rows  this one is for row 57:
=IFERROR([current week]58/[current week]59,"N/A")
(of course, you'll be replacing the row numbers in bold with the correct row numbers.) This will take the On schedule cell and divide it by Total Projects. If there is an error (i.e. a divide by zero error) then it will display N/A instead.
Hope this helps clarify a bit. Let me know if it works.
Best,
Heather

Thanks, Heather, I misinterpreted and also filled in the range for Children in the parent row, which included the subchildren. After that, it is now correct. Thanks for the help!

@Tehren Glad it worked for you! :)
Help Article Resources
Categories
Check out the Formula Handbook template!