Formula not averaging all cells

Ashleigh Bonneville
Ashleigh Bonneville ✭✭✭
in Formulas and Functions

Hello,

I have a sheet where we are tracking progress on certain goals. We have some children under the main focus areas (parent rows). Each children row has a harvey ball to show progress. I created a helper row to assign a number to each type of harvey ball. Then in the parent row, I want to take an average of the numbers in the helper for each of the children rows and assign an overall harvey ball. However, it is not including the "full" number in the average.

Here is a snip below. When the harvey ball is full, the helper row returns the number 5. Then the colored parent row takes an average of those below it. So the helper row should be returning a 3, but it is returning a 1.

How can I get the average formula (=IFERROR(ROUND(SUM(CHILDREN()) / (COUNTIF(CHILDREN(), <>0))), " ")) that I have in the parent helper cell to count for the Full/5?

The formula in the helper for the children is =IF([% Complete]@row = "Empty", "", IF([% Complete]@row = "Quarter", 1, IF([% Complete]@row = "Half", 2, IF([% Complete]@row = "Three Quarter", 4, IF([% Complete]@row = "Full", "5"))))) - which seems to be working.

image.png

Thank you,

Ashleigh

· Share on FacebookShare on Twitter

Answers

  • Jenn Hilber
    Jenn Hilber Overachievers Alumni
    edited 07/02/21

    Hi @Ashleigh Bonneville,

    You can use the Average function.

    =AVG(CHILDREN())

    =AVERAGEIF(CHILDREN(), <>0)


    Links to the help articles on these 2 formulas:

    https://help.smartsheet.com/function/avg
    https://help.smartsheet.com/function/averageif

    You shouldn't need the <>0, since the formula you have written doesn't include a zero, but you could include it in the AVERAGEIF function.


    I hope that solves your challenge!


    Jenn Hilber

    Smartsheet Overachievers Alumni

    https://www.linkedin.com/in/jennhilber/

    · Share on FacebookShare on Twitter
  • Ashleigh Bonneville
    Ashleigh Bonneville ✭✭✭

    Thank you, Jenn! Appreciate the formula update :)

    However, when I plug it in, it is still only counting cells 1-3 (quarter - three quarter). Anytime I change the harvey ball to full, it doesn't count the 4 in the average.

    Do you know why it is only counting 3 of the 4 options in the averages?

    Thank you!

    · Share on FacebookShare on Twitter
  • Ashleigh Bonneville
    Ashleigh Bonneville ✭✭✭

    Hi @Jenn Hilber ,

    Wanted to check back. I updated the formula to include a number for each type of Harvey Ball. The harvey balls are determined by a % complete formula.

    Harvey Ball: =IF([% Complete]@row <= 0.24, "Empty", IF([% Complete]@row <= 0.49, "Quarter", IF([% Complete]@row <= 0.74, "Half", IF([% Complete]@row <= 0.99, "Three Quarter", IF([% Complete]@row = 1, "Full")))))

    Then the Harvey Ball helper formula reads: =IF([Harvey Ball]@row = "Empty", 1, IF([Harvey Ball]@row = "Quarter", 2, IF([Harvey Ball]@row = "Half", 3, IF([Harvey Ball]@row = "Three Quarter", 4, IF([Harvey Ball]@row = "Full", "5")))))


    Those two are functioning properly.

    However, when the % complete is 100%, the harvey ball returns as full. This is happening properly.


    The error comes when the parent row for the harvey ball tries to average the number in the helper column. As long as the number is 1-4, it calculates the average. However, it excludes any of the rows that are 5 (100%). In the example below, the average in the parent row should be 2.8, which would include the 5. But it is not calculating the 5 in the formula. Any idea why?


    image.png

    Thank you!!

    · Share on FacebookShare on Twitter
  • Jenn Hilber
    Jenn Hilber Overachievers Alumni

    Hi @Ashleigh Bonneville ,

    Sorry for the delay. I actually thought you were looking at the average on % complete, but when I tried to duplicate your formula I found the answer. Notice in the image that the 5 is left-aligned but the other numbers are right-aligned? It made me think that the 5 is formatted as text rather than a number and thus would not calculate.

    Remove the quotes around the five at the end and that should work:

    =IF([Harvey Ball]@row = "Empty", 1, IF([Harvey Ball]@row = "Quarter", 2, IF([Harvey Ball]@row = "Half", 3, IF([Harvey Ball]@row = "Three Quarter", 4, IF([Harvey Ball]@row = "Full", 5)))))


    image.png



    Jenn Hilber

    Smartsheet Overachievers Alumni

    https://www.linkedin.com/in/jennhilber/

    · Share on FacebookShare on Twitter
  • Paris.TechProjSpec
    Paris.TechProjSpec ✭✭✭

    Hello there, I can't even get past the 1st part of this! I have a formula that is unparseable and I can't figure out what I'm doing wrong:

    =IF([Sum Checkmarks]@row < 3, "Stormy", IF([Sum Checkmarks]@row < 6, "Rainy", IF([Sum Checkmarks]@row < 8, "Cloudy", IF ([Sum Checkmarks]@row <= 10, "Partly Sunny", "Sunny"))))

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    https://community.smartsheet.com/discussion/comment/308733#Comment_308733

    Hi @Paris.TechProjSpec

    This looks correct! The only thing out of place is that you have a space between the very last IF statement and its open parentheses:

    IF (

    versus

    IF(

    Try removing this out and it should work for you:

    =IF([Sum Checkmarks]@row < 3, "Stormy", IF([Sum Checkmarks]@row < 6, "Rainy", IF([Sum Checkmarks]@row < 8, "Cloudy", IF([Sum Checkmarks]@row <= 10, "Partly Sunny", "Sunny"))))

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!