CODEWITHSUNDEEP
google-sheets
Adam Milner
Adam Milner

Reputation: 61

Working with hierarchical data in Google Sheets

If I have hierarchical data in a Google Sheet as in columns A and B in the example below, how can I write a formula that will fill the corresponding cells in column C with the product of the "parent" value in A1, and the "child" values in column B. That is, The formula in C8 for example, will search upward in column A until it finds the value 5 in A6, then multiplies it by 8, the value in B8.

Example Sheet

Obviously I'm trying to avoid having to put the "parent" value in every row in column A.

Upvotes: 4

Views: 9562

Answers (2)

Adam Milner
Adam Milner

Reputation: 61

After a fiddling around and jogging my memory on ArrayFormulas I figured out how to get the result I wanted. Here's the formula I'm using:

=ArrayFormula(index($B$2:$B2,MAX(IF(ISNUMBER($B$2:$B2),ROW($B$2:$B2)))-1,1))

(Note: Row 1 contains headings)

Edit: Explanation of how I arrived at this solution:

Ordinarily, IF(ISNUMBER(cell)), and ROW(cell) would return TRUE/FALSE, or a row number respectively. When used in an array formula and a range of cells as input instead, what you get is a list of TRUE/FALSE values, and row numbers evaluated for each cell in the input range. Wrap MAX around that and it will return the position in those two lists where IF(ISNUMBER()) is TRUE and the row number returned by ROW() is the highest, effectively searching from the bottom of the range.

The absolute reference for the first element in the range to be searched (in this case $B$2) keeps the range anchored to the same starting location when dragging the formula into other cells, while the lower bound of the range (in this case $B2) to be searched grows vertically.

Finally, the INDEX takes the input range, row number returned by the above (-1 row because of the header) and column position (1, since there is only one column) in order to return the desired value.

Upvotes: 2

shim
shim

Reputation: 10116

One way you could do this would be to associate each value in column B with a category or key that can be used to lookup the value for A in a separate table. This abstracts it somewhat, so you can change the values for the A column without having to have them in every row, but there's no empty cells.

i.e.

screenshot

and lookup table:

screenshot

In column C

= VLOOKUP($A1, <range for category lookup table>, 2, 0) * $B1

(And then this formula can be filled down)

More on VLOOKUP in Google Sheets here

Alternatively I suppose you could use a formula to find the last non-empty row in column A, or something along those lines, but this is more of a hack than a proper way to structure your data. Tables aren't really designed to be used in a hierarchical fashion like what you've shown. But they can easily represent hierarchical data using techniques like what I've suggested.

Upvotes: 1

Related Questions