CODEWITHSUNDEEP
excelexcel-formula
Andre Chenier
Andre Chenier

Reputation: 1186

excel formula function - parent cell vallue calculation from its children

my issue is: I don't know which excel function(s) should I utilize to achieve my aim without macro.

my aim is: as you see in image below, I have a tree or BoM (Bill of Meterial) structure. If a cell has its children, then I need to make calculation for parent cell's proper value.

In the image, yellow bg function means that if the cell has children then value should be calculated with the children's values.

This document will be a living doc., updated as project advances so I know the max # of possible children but I don't know how much of max I will be using at the end of project.

For example: D3 (Has 2 children initially) may have 5 children 1 year later, and 8 children 4 years later, and 13 children at the end of the project. But can not exceed max (let's say 25)

I require a structure that after all formula template is construct, if I add a new row as a child of D3, then excel must again use new child also with the old children

example: method: sum children values initially: D3 = D4 + D5

after a while a new child added as D6, D3 = D4 + D5 + D6

I shouldn't do manual settings. My real excel will have thousands of values, and statistical complex calculations

enter image description here

Upvotes: 0

Views: 4865

Answers (2)

petercat
petercat

Reputation: 1

I tried ForkandBeard's answer, and it works when every parent has at least one child. If that is not always the case, the formula can be generalized as follows:

=IF(C3=1,"Parent",If(C3<>C4,"End of Children"),"").
=IF(F3="Parent", IF(C4<>"Parent",MATCH("End of Children",F4:F901,0),""),"")
=IF(F3="Parent",SUM(INDIRECT("D"&ROW(D3)+1&":D"&ROW(D3)+G3)),"")

Upvotes: 0

ForkandBeard
ForkandBeard

Reputation: 884

Ok, here goes, you could:

In column F add the following (in row 3): (Determine which are parents, and which is the last child)

=IF(C3<>C4,IF(C3=1,"Parent","End of Children"),"")

In column G add the following (in row 3): (Find next occurrence of last child)

=IF(F3="Parent", MATCH("End of Children",F4:F901, 0),"")

In column E add the following (in row 3): (Calculate function based on next occurrence of last child)

IF(F3="Parent",SUM(INDIRECT("D"&ROW(D3)+1&":D"&ROW(D3)+G3)),"")

Now drag the values in E3, F3, G3 up and down your dataset.

Column E will have your values.

Hopefully this is a start.

Screenshot of data

Upvotes: 1

Related Questions