Parent Child Formulas
Good morning!
I have child rows with column formulas to find the average from other columns. Is it possible to add a parent row formula to find the average of the child rows?
Hard to explain hopefully the image will help. The parent row is in blue. Thank you for your help!
Best Answers
-
Yes. What is your current formula?
-
The learning center has a lot of information.
I also use these two links...
There is also a sheet with interactive formulas in the templates section. If you go to the home tab and click the + at the bottom of the left menu to access the Solution Center, you can type in "Formulas" in the search bar and grab the "Smartsheet Formula Examples" template.
In addition to those 4 resources... A LOT of trial and error. I do a ton of experimenting. Really the majority of my "knowledge" has come from these last two.
Creative thinking helps a lot. Thinking outside of the box.
Patience. LOTS of patience (and plenty of coffee and music too).
Maybe a couple of extra computers for when you toss one across the room.
But in all reality my biggest resource for learning has come from the Community. Answering questions. Picking one that you know is going to be a challenge and then just making it happen. Even if someone answers before you, keep pushing through your solution. Even if a question was already answered, read through it anyway. There are often times a number of ways to accomplish the same outcome, so seeing other perspectives and solutions could give you ideas to use in other areas.
Plus there are always a few tips and tricks to learn along the way that may or may not fit your style. For example... When I am building out a complex formula that has numerous functions nested within other functions, I break them out into different cells to get each piece working individually. Then I use cell references within each of those to link them together. Now that I have been able to evaluate each part and troubleshoot each one individually (instead of trying to figure out a generic error on a monstrous formula) I can copy/paste the functions from cell to cell in place of the cell references to make sure all of my parenthesis and whatnot are in place.
Short version: Break things down into as small of pieces as you can, make sure they all work, then piece the larger solution together.
Long version:
It is a little bit of a longer read, but it is much faster in practice and gets even faster once you have done it a few times. Here is a basic example of this...
We want to take all of the cells in a column and join them together into a text string if the cell contains the letter "A". We then want to substitute that letter "A" with the letter "B".
So we insert a text/number column we can just call "Helper Column" and in row 1 we join all of the cells.
But there is no JOINIF function. So we visit that first link that lists out all of the different functions available, and we see that there is a COLLECT function which allows us to collect data from cells across a range based on a range/criteria set (the collect function basically allows you to add an "if" to a lot of other functions and REALLY comes in handy quite a bit). So let's start with that.
The range we want to collect:
=COLLECT([Text Column]:[Text Column],
Next we need the range to evaluate. In this case it is the same range:
=COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column],
Now we need our criteria. We want to pull only those cells that contain an "A", so we go back to our Functions link. The FIND function and the CONTAINS function can both get the job done. FIND outputs a number, so if it doesn't find "A" it will output a zero, but CONTAINS outputs a true/false value and can be used as a standalone function. It is your preference at this point, but I prefer CONTAINS. NOTE: Find is case sensitive but CONTAINS is not. SO if you only wanted to collect cells that have a capital "A", you would need to use the FIND function.
=COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column], CONTAINS("A", @cell))
Well now we are getting an error, so we go back to the COLLECT function notes to make sure we did it right, and we find out that it has to be nested inside of another function. We know we want to use JOIN, so lets go ahead and throw that into the mix.
=JOIN(range to join, optional delimiter)
We don't need a delimiter for this, so we will leave that out.
=JOIN(range to join)
We already know the range to join. We want to join what was pulled by the COLLECT function. So we wrap the COLLECT function in side of the JOIN function.
=JOIN(COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column], CONTAINS("A", @cell)))
We exit the cell and now we have a text string. Great! Part one is working.
Now we want to substitute all of the occurrences of the letter "A" with the letter "B". We go back to our Functions list and come across the SUBSTITUTE function. Perfect.
Go back to that column we inserted and go to the cell in row 2. This is where we can test to make sure the SUBSTITUTE function is working properly.
=SUBSTITUTE(text to evaluate, old text, new text)
We know we want to evaluate our JOIN/COLLECT there in row 1, so we use a cell reference, and we already know the old text is "A", and the new text is "B".
=SUBSTITUTE([Helper Column]1, "A", "B")
Now we can see both parts are working independently. All we have to do at this point is copy/paste the formula (excluding the initial equals symbol) from the cell [Helper Column]1 into the SUBSTITUTE function where it says "[Helper Column]1".
=SUBSTITUTE([Helper Column]1, "A", "B")
=SUBSTITUTE(JOIN(COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column], CONTAINS("A", @cell))), "A", "B")
And now we have our final formula. We can move that to wherever we need it to be and get rid of that helper column.
I know it was a longer read (at least it took longer than I had expected to type it out hahaha), but it has saved me a lot of trouble and headache over the few years that I have been using Smartsheet.
Answers
-
Yes. What is your current formula?
-
Thank You Paul!!!
My current formulas for the following rows are:
Row Name: Mission and Ability Score
fx= AVG(VALUE(LEFT([Equity Score]@row,1)),VALUE(LEFT([Teamwork Score]@row,1)),VALUE(LEFT([Behavior Score]@row,1)))
Row Name Experience Score
fx=AVG(VALUE(LEFT([Safety Score]@row,1)),VALUE(LEFT([Management Score]@row,1)),VALUE(LEFT([Customer Service Score]@row,1)))
-
Are those rows or columns?
-
They're columns. Gosh sorry about that.
-
NO worries. SO those formulas are in the Child rows, and you want a column formula to either apply those to child rows or average children in parent rows. Correct?
Try wrapping them in an IF statement.
=IF(COUNT(CHILDREN()) = 0, child_row_formula, AVG(CHILDREN()))
=IF(COUNT(CHILDREN()) = 0, AVG(VALUE(LEFT([Equity Score]@row,1)),VALUE(LEFT([Teamwork Score]@row,1)),VALUE(LEFT([Behavior Score]@row,1))), AVG(CHILDREN()))
=IF(COUNT(CHILDREN()) = 0, AVG(VALUE(LEFT([Safety Score]@row,1)),VALUE(LEFT([Management Score]@row,1)),VALUE(LEFT([Customer Service Score]@row,1))), AVG(CHILDREN()))
-
Yes, you were able to understand my confusing question. I'm going to try it now. Thank you so much!
-
Happy to help. 👍️
-
Holy Smokes!!! It works!!! THANK YOU! How did you learn all of this? Do you have any recommendations to help increase my knowledge, e.g. classes, videos . . .
-
The learning center has a lot of information.
I also use these two links...
There is also a sheet with interactive formulas in the templates section. If you go to the home tab and click the + at the bottom of the left menu to access the Solution Center, you can type in "Formulas" in the search bar and grab the "Smartsheet Formula Examples" template.
In addition to those 4 resources... A LOT of trial and error. I do a ton of experimenting. Really the majority of my "knowledge" has come from these last two.
Creative thinking helps a lot. Thinking outside of the box.
Patience. LOTS of patience (and plenty of coffee and music too).
Maybe a couple of extra computers for when you toss one across the room.
But in all reality my biggest resource for learning has come from the Community. Answering questions. Picking one that you know is going to be a challenge and then just making it happen. Even if someone answers before you, keep pushing through your solution. Even if a question was already answered, read through it anyway. There are often times a number of ways to accomplish the same outcome, so seeing other perspectives and solutions could give you ideas to use in other areas.
Plus there are always a few tips and tricks to learn along the way that may or may not fit your style. For example... When I am building out a complex formula that has numerous functions nested within other functions, I break them out into different cells to get each piece working individually. Then I use cell references within each of those to link them together. Now that I have been able to evaluate each part and troubleshoot each one individually (instead of trying to figure out a generic error on a monstrous formula) I can copy/paste the functions from cell to cell in place of the cell references to make sure all of my parenthesis and whatnot are in place.
Short version: Break things down into as small of pieces as you can, make sure they all work, then piece the larger solution together.
Long version:
It is a little bit of a longer read, but it is much faster in practice and gets even faster once you have done it a few times. Here is a basic example of this...
We want to take all of the cells in a column and join them together into a text string if the cell contains the letter "A". We then want to substitute that letter "A" with the letter "B".
So we insert a text/number column we can just call "Helper Column" and in row 1 we join all of the cells.
But there is no JOINIF function. So we visit that first link that lists out all of the different functions available, and we see that there is a COLLECT function which allows us to collect data from cells across a range based on a range/criteria set (the collect function basically allows you to add an "if" to a lot of other functions and REALLY comes in handy quite a bit). So let's start with that.
The range we want to collect:
=COLLECT([Text Column]:[Text Column],
Next we need the range to evaluate. In this case it is the same range:
=COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column],
Now we need our criteria. We want to pull only those cells that contain an "A", so we go back to our Functions link. The FIND function and the CONTAINS function can both get the job done. FIND outputs a number, so if it doesn't find "A" it will output a zero, but CONTAINS outputs a true/false value and can be used as a standalone function. It is your preference at this point, but I prefer CONTAINS. NOTE: Find is case sensitive but CONTAINS is not. SO if you only wanted to collect cells that have a capital "A", you would need to use the FIND function.
=COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column], CONTAINS("A", @cell))
Well now we are getting an error, so we go back to the COLLECT function notes to make sure we did it right, and we find out that it has to be nested inside of another function. We know we want to use JOIN, so lets go ahead and throw that into the mix.
=JOIN(range to join, optional delimiter)
We don't need a delimiter for this, so we will leave that out.
=JOIN(range to join)
We already know the range to join. We want to join what was pulled by the COLLECT function. So we wrap the COLLECT function in side of the JOIN function.
=JOIN(COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column], CONTAINS("A", @cell)))
We exit the cell and now we have a text string. Great! Part one is working.
Now we want to substitute all of the occurrences of the letter "A" with the letter "B". We go back to our Functions list and come across the SUBSTITUTE function. Perfect.
Go back to that column we inserted and go to the cell in row 2. This is where we can test to make sure the SUBSTITUTE function is working properly.
=SUBSTITUTE(text to evaluate, old text, new text)
We know we want to evaluate our JOIN/COLLECT there in row 1, so we use a cell reference, and we already know the old text is "A", and the new text is "B".
=SUBSTITUTE([Helper Column]1, "A", "B")
Now we can see both parts are working independently. All we have to do at this point is copy/paste the formula (excluding the initial equals symbol) from the cell [Helper Column]1 into the SUBSTITUTE function where it says "[Helper Column]1".
=SUBSTITUTE([Helper Column]1, "A", "B")
=SUBSTITUTE(JOIN(COLLECT([Text Column]:[Text Column], [Text Column]:[Text Column], CONTAINS("A", @cell))), "A", "B")
And now we have our final formula. We can move that to wherever we need it to be and get rid of that helper column.
I know it was a longer read (at least it took longer than I had expected to type it out hahaha), but it has saved me a lot of trouble and headache over the few years that I have been using Smartsheet.
-
Paul! Thank you so much for the thoughtful reply. This is fascinating. I'm excited to rereading this reply over and over again (so much information to learn). I find it so interesting and appreciate all the help and mentoring. Today has been a crazy busy day and the formula you helped me with saved me so much time!!
Can't thank you enough! Have a great weekend.
-
Always happy to help!
Help Article Resources
Categories
Check out the Formula Handbook template!
