Hierarchy functions can display aggregate information about the values in row hierarchies like sums, counts, or averages.
Hierarchy functions are one of the many function types supported by Spreadsheet.com. They are designed for working with data in row hierarchies. When input in parent cells or child cells, hierarchy functions can return aggregate information about the row hierarchy.
You may use hierarchy functions in a Construction Budget workbook to display a sum of all costs in a specific cost category, or in a Team Task List workbook to return a count of all tasks associated with a single scope of work. Hierarchy functions can turn parent rows into helpful summary rows that contain key information about their child rows.
List of Hierarchy Functions
Spreadsheet.com supports four hierarchy functions:
| Function | Syntax | Description |
| ANCESTORCELLS | =ANCESTORCELLS(cell) |
Returns all of the ancestor cell values above a given cell in the row hierarchy. |
| CHILDCELLS | =CHILDCELLS(cell) |
Returns all of the first-level child cell values below a given parent cell in the row hierarchy. |
| DESCENDANTCELLS | =DESCENDANTCELLS(cell) |
Returns all of the descendant cell values below a given cell in the row hierarchy. |
| PARENTCELL | =PARENTCELL(cell) |
Returns the parent cell value above a given cell in the row hierarchy. |
In the examples below, we’ll take a look at some commonly used hierarchy functions. For documentation on every hierarchy function supported by Spreadsheet.com, reference our section on Hierarchy functions.
When using functions in your workbook, you can input and edit formulas either directly in the cell, or from the formula bar between the worksheet toolbar and the spreadsheet grid. Learn more in our article on Creating and Editing Formulas.
How to Use Hierarchy Functions
For this example, we’ve created a simple worksheet showing budgeted expenses and actual expenses for three different regions and established row hierarchies. The parent rows are highlighted in green for reference.
Our budget has two levels of row hierarchies: our three regions (North America, Europe, and Asia) are child rows of the “International (Total)” row, and our three divisions (Manufacturing, Distribution, and Marketing) are child rows of each region.
With hierarchy functions, we can turn each of our parent rows into summary rows. Let’s start with taking the sum of all values in North America with the DESCENDANTCELLS function.
Here, the DESCENDANTCELLS function is combined with the SUM function to take a sum of all budgeted expenses for North America. We can extend this formula to the next two adjacent cells and find similar sums for “Actual Expenses” and “Delta”.
And we’ll use these same formulas for our other two regions as well.
Instead of remaining as simple organizational rows, each region row is now displaying a subtotal from our budget that reveals actionable information. Now, we can use hierarchy functions in our “International (Total)” parent row to display budget totals.
For this row, we’ll use the CHILDCELLS function instead of DESCENDANTCELLS so that the calculation only includes data from the first level of the hierarchy, our region rows.
We’ll extend this formula to the next two adjacent cells.
We can even collapse our hierarchy rows to show a simplified version of our budget sheet, and all of the hierarchy functions will retain their values.
Notice how the CHILDCELLS formula in our “International (Total)” row is only taking values in our green first-level hierarchy rows into account. Now, we have a complete budget sheet that shows grand totals, subtotals, and component values for each of our regions and divisions.
Hierarchy functions play a key role in project management. Learn more about managing projects with Spreadsheet.com. Or, read on to learn more about other types of functions supported by Spreadsheet.com, including statistical functions, math functions, date and time functions, and more.