Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Average Children Rows with RYG

Laurie Bearden
Laurie Bearden ✭✭✭
edited 12/09/19 in Archived 2016 Posts

Hi all.  I'm attempting to calculate an average for RYG balls in the parent row.  I have gotten to the point of calculating all of my colors, but I'm not sure how to get the average.  Here is my calculation thus far:

 

=AVG(COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3)

 

This comes up with a count, but the count ends up the same whether I use the AVG or not (57). 

 

I'd like my end result to be a number between 1 and 3. 

Comments

  • Jamison
    Jamison ✭✭✭✭✭

    It is returning 57 because the only number being passed to the AVG function is 57. So, you are asking it, what is the average of 57, not what is the average of 2, 3, 1, 3, 2 and so on.

     

    So, using what you have, just divide it by thetotal number of children and remove the AVG from the beginning:

     

    =((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3)) / COUNT(CHILDREN())

     

     

    There might be an easier way, but this will work.

     

  • I was able to make it work with the help from a colleague.  It looks a lot different from yours lol. 

     

    =IF(AND(Phase7 = "Stalled", OR([On Time Status]7 = "Early", [On Time Status]7 = "On Time")), "Yellow", IF(AND([On Time Status]7 = "Late", OR(Phase7 = "Not Started", Phase7 = "In Progress", Phase7 = "Stalled", Phase7 = "Unknown")), "Red", "Green"))

     

    This was able to give us the desired result of the average color ball.  Thank you so much for getting back to me.  :)  I know your formula will help me and others in the future, as well.  :)

  • Sorry, that was a formula for a different sheet.  Embarassed 

     

    The formula for the average RYG is:

     

    =IF((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3) / COUNT(CHILDREN()) < 2, "Green", IF((COUNTIF(CHILDREN(), "Green") + COUNTIF(CHILDREN(), "Yellow") * 2 + COUNTIF(CHILDREN(), "Red") * 3) / COUNT(CHILDREN()) < 3, "Red", "Yellow"))

This discussion has been closed.