Sign in to join the conversation:
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())
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!
Ah, let me try this, I just noticed your new post.
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))
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!!
Happy to help!
Hi there, I've created a sheet to where my team is tracking information received. In one column, we are logging the date and time information came in (ie: November 21, 2025 8:30 AM). I would like to add a checkbox column, with a formula specifying that the box be checked if the logged time is AFTER 8:30 AM, and left…
I have a list of properties that I'm keeping track of. I have each building divided into suites (children) and each suite has a cell with its square footage. The total square footage rolls up to the building name. I also have a drop down column that shows if the suite is occupied or vacant. I need a to calculate the…
Hi! I'm fairly new to SmartSheets but have been trying a number of things that havent yet worked. I want to be able to show for each parent row, the number of child rows completed as a %. So for the parent row Process Overview there are 7 tasks and 6 tasks are complete so I want it to show as 90%. Can anyone help me?
