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

Options

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

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • jdc
    Options

    @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
    Mark.poole ✭✭✭✭✭✭
    edited 06/21/24
    Options

    @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)),””)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    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")

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!