-
[
reference]
-
reference—[optional]The cell for which you want the ancestors. If no cell is specified, the function references the ancestors of the current cell.
This example references the following sheet information:
Row # |
Project |
LEVEL |
Item Number |
Cost Per Unit |
Due Date |
---|---|---|---|---|---|
1 |
- Men's Formal Button Down |
0 |
MF01 |
$38.75 |
02/27/19 |
2 |
MF01 - Design Phase |
1 |
DP |
$25.00 |
02/19/19 |
3 |
-- MF01 - Assembly |
1 |
A0 |
$13.75 |
02/27/19 |
4 |
Cutout Shirts |
2 |
A001 |
$10.00 |
02/22/19 |
5 |
Sew Shirt |
2 |
A002 |
$2.50 |
02/26/19 |
6 |
Add Buttons |
2 |
A003 |
$1.25 |
02/26/19 |
Given the table above, here are some examples of using ANCESTOR in a sheet:
Formula |
Description |
Result(s) |
---|---|---|
=COUNT(ANCESTORS()) |
Count the amount of Ancestors for the cell containing the formula. This formula is within each row of the “Level” column. |
Row one: 0 Row two & three: 1 Row four through six: 2 |
=SUM(ANCESTORS([Cost Per Unit]5)) |
Sum the Ancestors for row 5 in the “Cost Per Unit” column. It has two Ancestor rows (row 3 & 1). |
$52.50 |
=INDEX(ANCESTORS([Item Number]2), 2) + " - " + "Design Phase" |
Return the Ancestor of the value in the “Item Number” column, row 2. Then add the values “- Design Phase” to the result. |
MF01 - Design Phase |