Totaling percentages linked from another sheet

JKprintern

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

Paul Newcome

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?

JKprintern

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!

 

Paul Newcome

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.

Paul Newcome

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.

JKprintern

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!

Paul Newcome

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.

JKprintern

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!

JKprintern

Ah, let me try this, I just noticed your new post.

Paul Newcome

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 + "%"

JKprintern

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. 

Paul Newcome

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

JKprintern

Sorry I was out of the office, returned today, and these formulas are just working perfectly now!! And AVG is working despite some blank values in a few cells, so I just stuck with that.

THANK YOU PAUL, you really helped me out and I appreciate your guidance!!

Paul Newcome

Happy to help!