Is there a way to calculate a total score (percentage) for each children row associate with a parent

jdc

I am trying to calculate a score for each office - based on the correct/incorrect values under "correct/incorrect product column". If the answer is correct this is equal to 1 if is incorrect then the value is 0 = but i need to total it as a percentage on each parent row w/o having to enter a formula manually every time ..

Mark.poole

@jdc

=IF(COUNT(ANCESTORS()) = 0, AVG(CHILDREN(Score@row)))

This would have to go in a separate column so you can still manually enter the 1 and 0

Mark.poole

@jdc

=IF(COUNT(ANCESTORS()) = 0, AVG(CHILDREN(Score@row)))

This would have to go in a separate column so you can still manually enter the 1 and 0

jdc

@Mark.poole thank you so much, this is great. I am still learning but i am working a little project at work. I do have one additional question, if i may - is there a way to hide the 0% under each total score (95%). This is going to be a continuous working sheet and i was hoping i could make this formula a column formula so i wouldn't have to add the formula for each parent row ( is this even possible ?)

Mark.poole

@jdc it should automatically hide the 0s as their count ancestors would be 1. If it isn’t automatically hiding the 0%. Then

=IF(COUNT(ANCESTORS()) = 0, AVG(CHILDREN(Score@row)),””)

SoS | Dan Palenchar

Hello @jdc!

You can do these with a logical formula that first evaluates the hierarchy of the row to determine if it is a parent or a child and then applies a respective formula. The Solution is directly below followed by a more detailed Explanation.

Solution

Input the below formula into your Score column and make it a Column Formula.

=IFERROR(IF(Children@row > 0, ROUND(SUM(CHILDREN()) / COUNT(CHILDREN()), 2) * 100 + "%", IF([Correct/ Incorrect Product]@row = "Correct", 1, IF([Correct/ Incorrect Product]@row = "Incorrect", 0))), "n/a")

Find a published Sheet here

Explanation

First, you will need a column in your Sheet that counts the Child rows. I use this all the time and just call it Children. In this column input the formula:

=COUNT(CHILDREN())

This will give a number for each row. Any children rows will have a 0 value and any parent row will have a value greater than 0.

Then, you need to build two layers of formula for children vs. parents. Children will get:

=IF([Correct/incorrect Product]="Correct", 1, IF([Correct/incorrect Product]="Incorrect", 0))

Parents will get:

=SUM(CHILDREN()) / COUNT(CHILDREN())

Next, these formulas need to be combined and applied correctly based on hierarchy. To do this, we will put them together as part of an IF() function that evaluates the hierarchy of the row and then applies the correct formula (parent or child). The formula contextually works like this:

1. Start by checking if the row is a parent row by using =IF(Children@row>0…
2. If yes, apply the Parent formula =SUM(CHILDREN()) / COUNT(CHILDREN())
3. If no, apply the Child formula =SUM(CHILDREN()) / COUNT(CHILDREN())

Putting it together you get this formula:

=IF(Children@row > 0, SUM(CHILDREN()) / COUNT(CHILDREN()), IF([Correct/ Incorrect Product]@row = "Correct", 1, IF([Correct/ Incorrect Product]@row = "Incorrect", 0)))

You could use this formula as is but it has a couple limitations:

1. The parent formula will throw a #DIVIDE BY ZERO error if you have a group of children with no Score. We can circumvent this with =IFERROR()
2. You will need to choose to format the entire column as a % (meaning the parents will have the % you want but children will have 100% or 0%) or as a number (meaning parents will have a decimal and children will have 1 or 0). We can circumvent this by converting the Parent decimal value into a text value and forcing a % sign after. However doing this means the parent value will be interpreted as text and not a number and therefore you cannot perform mathematical computations on it. This is fine if you simply need it for the visual element, but if you wanted to do use these parent scores in any equation you would have to keep the value as a number.

Anyhow, making the above changes gets the final formula pasted above:

=IFERROR(IF(Children@row > 0, ROUND(SUM(CHILDREN()) / COUNT(CHILDREN()), 2) * 100 + "%", IF([Correct/ Incorrect Product]@row = "Correct", 1, IF([Correct/ Incorrect Product]@row = "Incorrect", 0))), "n/a")

If you want the version that leaves the parent as a number in decimal format use:

=IFERROR(IF(Children@row > 0, SUM(CHILDREN()) / COUNT(CHILDREN()), IF([Correct/ Incorrect Product]@row = "Correct", 1, IF([Correct/ Incorrect Product]@row = "Incorrect", 0))), "n/a")