Tree sumation in Excel (with or, if possible, without VBA)

[sandros]

Hello everybody,

I have to model branched water supply network showed in picture attached here

https://goo.gl/jJFhMx.
Node numbers are written in brackets, while pipe numbers are written in red and rectangled. Water demand is defined in nodes and marked with an arrow.

So, input data are pipe number, upstream node number, downstream node number and demand in downstream node as in attached .xls file https://goo.gl/EqA6kz.

Is it possible to get flows in pipes by summation all demands in nodes "downstream" of the specific pipe?

I hope I was clear and thanks in advance!
Sandro

 

[oldbrewer]

what are the rules for calculating flow ?

 

[sandros]

Hi oldbrewer!

There aren't any. I mean the hydraulics is here irrelevant. If you look at sketch I attached you can interpret it as tree with parent-child relationship. I mean, I have only sum of demands, so the pipe No. 1 is "on the bottom of the tree" and it conveys all the dischage or flow, i. e. 80.

 

[shg]

Here's an analogue for electrical demand.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Node​

[/td][td="bgcolor:#F3F3F3"]

Parent Node​

[/td][td="bgcolor:#F3F3F3"]

Direct Load​

[/td][td="bgcolor:#F3F3F3"]

Total Load​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td]

​

[/td][td]

14​

[/td][td="bgcolor:#F3F3F3"]

846​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td]

1​

[/td][td]

10​

[/td][td="bgcolor:#F3F3F3"]

87​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td]

1​

[/td][td]

15​

[/td][td="bgcolor:#F3F3F3"]

629​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4​

[/td][td]

3​

[/td][td]

70​

[/td][td="bgcolor:#F3F3F3"]

448​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

5​

[/td][td]

4​

[/td][td]

83​

[/td][td="bgcolor:#F3F3F3"]

311​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

6​

[/td][td]

1​

[/td][td]

49​

[/td][td="bgcolor:#F3F3F3"]

49​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

7​

[/td][td]

3​

[/td][td]

50​

[/td][td="bgcolor:#F3F3F3"]

50​

[/td][/tr]
[/table]

Node 1 (a big power distribution unit) has a direct load of 14 amps, plus it feeds nodes 2 and 3. Column E calculates the total demand for each node. (The whole network is not shown in the example.)

Yours should work the same way, but I can't figure out the corresponding columns or why you have an extra one.

 

[shg]

Here's a complete example if you want to verify manually. The loads and connections are random, so they change each time the sheet calculates.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Node​

[/td][td="bgcolor:#F3F3F3"]

Parent Node​

[/td][td="bgcolor:#F3F3F3"]

Direct Load​

[/td][td="bgcolor:#F3F3F3"]

Total Load​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td]

​

[/td][td]

14​

[/td][td="bgcolor:#F3F3F3"]

456​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td]

1​

[/td][td]

20​

[/td][td="bgcolor:#F3F3F3"]

442​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td]

2​

[/td][td]

44​

[/td][td="bgcolor:#F3F3F3"]

224​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4​

[/td][td]

2​

[/td][td]

58​

[/td][td="bgcolor:#F3F3F3"]

198​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

5​

[/td][td]

4​

[/td][td]

30​

[/td][td="bgcolor:#F3F3F3"]

140​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

6​

[/td][td]

3​

[/td][td]

10​

[/td][td="bgcolor:#F3F3F3"]

107​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

7​

[/td][td]

5​

[/td][td]

84​

[/td][td="bgcolor:#F3F3F3"]

84​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

8​

[/td][td]

5​

[/td][td]

26​

[/td][td="bgcolor:#F3F3F3"]

26​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

9​

[/td][td]

3​

[/td][td]

73​

[/td][td="bgcolor:#F3F3F3"]

73​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

10​

[/td][td]

6​

[/td][td]

97​

[/td][td="bgcolor:#F3F3F3"]

97​

[/td][/tr]
[/table]

If you can arrange your pipe tree in that fashion, I'll show you the formula.

 

[sandros]

Hi shg, thanks for your reply.

I could arrange my pipe tree:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Node[/TD]
[TD]Parent Node[/TD]
[TD]Direct Load[/TD]
[TD]Total Load[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandros]

Hi shg,

Thank you for your reply. In fact I can arrange my tree network in that fashion! Here it is: https://goo.gl/sr6X4L.

 

[shg]

That's not arranged the same way -- it still has an extra column.

EDIT: In fact, it looks unchanged.

 

[sandros]

Sorry, it is in Sheet 2.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Node​

[/td][td="bgcolor:#F3F3F3"]

Parent Node​

[/td][td="bgcolor:#F3F3F3"]

Direct Load​

[/td][td="bgcolor:#F3F3F3"]

Total Load​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1​

[/td][td]

​

[/td][td][/td][td="bgcolor:#F3F3F3"]

80​

[/td][td]E3: =D3 + SUMIF($C$3:$C$13, $B$3:$B$13, $E$3:$E$13)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

2​

[/td][td]

1​

[/td][td]

10​

[/td][td="bgcolor:#F3F3F3"]

80​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

3​

[/td][td]

2​

[/td][td]

20​

[/td][td="bgcolor:#F3F3F3"]

20​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

4​

[/td][td]

3​

[/td][td][/td][td="bgcolor:#F3F3F3"]

-​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

5​

[/td][td]

2​

[/td][td][/td][td="bgcolor:#F3F3F3"]

50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

6​

[/td][td]

5​

[/td][td]

10​

[/td][td="bgcolor:#F3F3F3"]

50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

7​

[/td][td]

6​

[/td][td]

20​

[/td][td="bgcolor:#F3F3F3"]

20​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

8​

[/td][td]

6​

[/td][td]

5​

[/td][td="bgcolor:#F3F3F3"]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

9​

[/td][td]

6​

[/td][td]

10​

[/td][td="bgcolor:#F3F3F3"]

15​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

10​

[/td][td]

9​

[/td][td][/td][td="bgcolor:#F3F3F3"]

-​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

11​

[/td][td]

9​

[/td][td]

5​

[/td][td="bgcolor:#F3F3F3"]

5​

[/td][td][/td][/tr]
[/table]