Has anyone used a formula which can report a child's percentage of the total rolled-up in its parent without using absolute cell references? I'd like to avoid absolute references because I'd like the formula to be consistent all the way down many parent cells, which will have different absolute references to be used in the child rows.
In the screen capture below, the "Percent of Count" is calculated by dividing "Count" in the current row by the absolute reference to "Count" in the parent row (the formula for [Percent of Count]2 is =Count2 / Count$1, for example). That formula is dragged down to all the children, until the next parent, where the formula needs to be re-created with the new absolute reference of the new parent. This is just a small example using two parents, but I have this formula in mind for a real sheet with many different parents.
If a single formula can be used across all of the "families" then I can make this even more robust by adding logic which will either perform the percentage calculation, or roll-up the total as a parent, based on whether or not the row is a parent row. There are previous discussions on that logic, like the recent one , here.
