Parent with Child – Row Formulas

In today’s Top Tip Tuesday, we’re asking “How can you combine Parent Row calculations and Child Row calculations into 1 formula which can be stored as a column formula?”

Parent rows are expandable and collapsible and are identified from the Primary Column by the little +/- button next to its name. Child rows contain raw data. Parent rows are used to group and summarise the raw data from the child row.

Why would you want to combine formulas though?🤔
Lets say you have an order sheet and are creating a Cost column.
This column would contain this formula on the child rows:
=[Quantity]@row * [Unit Price]@row
So, you need 2 different formulas in the Cost column. Without combining these two different formulas into one, getting the correct formula on each row is tricky to maintain, especially if new rows are added and the hierarchy is altered after putting the formulas into the column.

So, back to the Top Tip of combining the formulas…😀
When combining formulas I usually use a helper column to establish if the row is a parent or not. I’d set up a checkbox column called “Parent” and add the following IF Function to check the box if the row is a parent:
=IF(COUNT(CHILDREN()) > 0, 1,0)
then in the Cost column the COMBINED formula would be:
=IF([Parent]@row=1,SUM(CHILDREN()),[Quantity]@row * [Unit Price]@row)
this reads, if the parent helper column has a tick in the box, then sum the child costs otherwise create a cost from multiplying the quantity with the unit price.
Hey Presto! 🙌 a combined formula.
Lastly, right click the combined formula and select Convert to Column Formula and from that point the formula is protected and will adapt to the parent or the child function depending on the checkbox in the helper column.
Voila – You’re welcome😊

Don’t forget we run lots of training modules where we cover many tips like this one.💡