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!
In my job as a scheduler a common question is "which of the predecessors for this activity is driving the date?" Currently I have to rely on my own knowledge of my schedules or, worst case, I have to look at every predecessor until I find the one I am looking for. Microsoft Project is nice because it has the "inspect"…
Hi! I am trying to create DVs from this sheet that will do the following: In this screenshot example - Elizabeth is the roadmap owner, but Deb is the Calendar invite owner. So in Elizabeth's DV, I still need her to be able to see this row, but not be able to edit the Calendar Update Complete column. In Deb's DV, I need her…
Hi all, Is it possible to display some sort of graphic like this within a dashboard? I'd be taking data from a form and gathering total numbers of of "yes" and "non" questions to get the numerator and denominator for each row or column in the picture. I think I could do the metric sheet part of this task no problem, but…
