I'm Stumped - Need a Percent Average when applicable

Tehren · 05/11/21

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.

Heather Duff · 05/12/21

@Tehren

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

Heather Duff · 05/11/21

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

Tehren · 05/11/21

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%.

Tehren · 05/11/21

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.

Heather Duff · 05/12/21

@Tehren

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

Tehren · 05/12/21

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

Heather Duff · 05/12/21

@Tehren Glad it worked for you! :)

I'm Stumped - Need a Percent Average when applicable

Best Answer

Answers

Help Article Resources

Categories