11

I am simply trying to get an average percentage in a parent row from children rows that are linked in from another sheet. Is the fact they are linked what is causing my error?

Currently I'm just using: =AVG(CHILDREN())

Functionality
Industry
Department

Comments

Your formula should be working. Can you click in the cell to "edit" it (but don't actually edit) and take anther screenshot so that we can see the formula in the cell itself?

Hi Paul. Here's with the formula. I'm trying to add this to 2 column totals (Stewardship Calls and Loyalist Calls), both of which are getting percentages linked from another sheet. Thank you!

 

Are you trying to sum or average?

 

Either way I would start by using either a SUMIF or AVERAGEIF (both have the same syntax) to include the condition that only includes cells that contain numbers.

It also looks like the linking is converting things to text which means you would need to convert the data to actual numerical values before they can be used.

Thank you - yes I changed the formula to AverageIF! I understand your point about changing those percentages to a value instead of text and am working on figuring that out!

You could create two new columns. One for each of your percentage columns. You could then use something along the lines of

 

=VALUE(SUBSTITUTE([Percentage Column Name]@row, "%", "")) / 100

 

This will essentially remove the % sign and divide by 100 to give you the appropriate decimal. You could then use your AVERAGEIF on these new columns.

 

From there you would have a few options:

 

1. Reference the new columns in the formula at the top of the old columns.

2. Run the calculation at the top of the new columns and use cell linking to pull the result to over top of the old columns.

3. Convert the new columns to a percentage formatting, move them into the main table, then hide the old columns that are containing the text.

The value function doesn't seem to work either in the source sheet or in the destination sheet. In my source sheet, I have a bunch of checkboxes and the parent row calculates a percentage completed using: 

=("" + ROUND((COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN())), 2) * 100 + "%")    ......(thank you Smartsheet Community)

I then go to a new column and put =VALUE(cell2) and it comes back as #invalid value. 

Clearly I used someone else's brilliance with the formula above and am perplexed by something that is probably simple!

In reply to by JKprintern

Your above formula can actually be simplified a little as well. It just takes out a few unnecessary parenthesis and the extra set of quotes there at the beginning that are also not needed (but really what matters is that you have something that works).

 

=ROUND(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()), 2) * 100 + "%"

Paul, the first half of your solution worked beautifully. In the new columns, I get 1's correlating to 100% shown in the old columns, etc. But in the parent row of the new column when I use =AVERAGEIF(CHILDREN()) the result comes back #incorrect argument set. I will keep trying as the rest of it all makes sense. 

Just use a plain old AVG(CHILDREN()).

 

AVERAGEIF() would be used if you were going to specify to average the children that are numbers.

 

=AVERAGEIF(CHILDREN(), ISNUMBER(@cell))