Formula not averaging all cells

Ashleigh Bonneville

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.

Thank you,

Ashleigh

Jenn Hilber

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!

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!

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?

Thank you!!

Jenn Hilber

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

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

Genevieve P.

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