I am working on a hierarchy excel sheet and I need some help. In the columns A-F is depicted an organization hierarchy. I need a function that provides values for column 'Expected count'.
Here I have manually filled in what values I would like to get in column H:
I summarize the logic, how numbers in column H are calculated:
Now let's look at the last org unit in each row:
If that given cell has no value in the cell down to the right (practically the org unit has no subordinate units) than 'Expected count' = 'Team headcount'-1. [eg. C4 has no value in the cell down to the right (D5 is empty)it means practically that 'Suborg A of Org 1.' has no subordinate units. H4 = G4-1 = 8
If that given cell has values in the cells down to the right (practically the unit has subordinate unit or units) than 'Expected count' = 'Team headcount'-1 + number of different subordinate units x 2 [eg. C5 has 4 different values in the cells down to the right )it means practically that 'Suborg B of Org 1.' has 4 different subordinate units. H5 = G5-1+4x2= 16
If the value in column G is 0 than 'Expected count' = number of different subordinate units x 2 [eg. H2 = 1x2 =2]
The function has to work in the lower levels as well. It is only a sample of the hierarchy. There would be thousands of units and possibly 8 levels.
This formula returns the intended result in my sheet. The formula is filled down from cell J2.
=IFERROR(IF(G2=0,
ROWS(UNIQUE(FILTER(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1),(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1))=
XLOOKUP(TRUE,NOT(ISBLANK(A2:F2)),A2:F2,,,-1))*(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1)<>""))))*2,G2-
1+ROWS(UNIQUE(FILTER(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1),(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1))=
XLOOKUP(TRUE,NOT(ISBLANK(A2:F2)),A2:F2,,,-1))*(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1)<>""))))*2),G2-1)