excelexcel-formulahierarchy

Calculating expected count based on hierarchy structure


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:

sample table

I summarize the logic, how numbers in column H are calculated:

Now let's look at the last org unit in each row:

  1. 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

  2. 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

  3. 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.


Solution

  • 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)
    

    Calculating expected count based on hierarchy structure