Excel for Simple Pipe Network; Finding Largest Draw-off (Tap / Faucet) Served via Each Flow Pipe 1

[briand2]

I am carrying out some analysis of pipe networks. Although I have over the years developed an increasingly sophisticated (some might say complex and cumbersome!) Excel spreadsheet for this, there is one particular aspect that I have been struggling with recently.
Let me explain...
The attached is a very cut-down version of the spreadsheet, together with the simple pipe network that it represents. I have also pasted the formulae (from the yellow cells).
There are a total of 9 pipes serving 4 taps / faucets. There are 10 nodes (4 of which are taps / faucets).
My formulae successfully calculate, for each pipe, its flow rate, being the total of all pipes served from its End Point. These flow rates are marked in green for each pipe on the diagram.
I would also like to calculate for each Flow pipe the single largest draw-off that it serves. These are shown in red for each pipe in the diagram. So, for instance, the total flow rate through Pipe 5 is 15, being the sum of Pipe 6 (7), Pipe 7 (3) and Pipe 9 (5). However, the maximum individual draw-off served via Pipe 5 is the maximum of these three draw-offs, i.e. 7.
Even right up at highest level, the source, Pipe 1, this value for maximum draw-off served remains at 7 (with the total being 17).
I have tried in the Max Draw-off Served via Flow Pipe column various formulae, including sumif, sumifs and even array formulae, but I just can't manage to get the calculation to work.
I would be grateful if anyone can suggest an appoach!
Thank you,
Brian

 

[SkipVought]

Hi,

This is a classic Parent-Child table problem. I encountered it in multi-level bills of material.

I used a recursive procedure in VBA, where starting at any node (your start or end point, my part ID) the procedure would find all the children and recursively use the child as the parent in the procedure until the bottom of the structure is encountered (ie the node as a parent has no child)

Don't believe that you can achieve this objective with formulas.

BTW, you can observe this better if you were to switch the 5 and 7 draws and make a structure of more than two pipes in that leg of the structure. You've got to chain upward to the f node to discover the max of 3, 5 & 7 in pipe 5.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

so here's a VBA user defined function that you use like a formula in column H Max draw...

Function MaxFlow(rStart As Range) As Integer
    Dim rST As Range, rEnd As Range, iDraw As Integer

    iDraw = Intersect(rStart.EntireRow, [draw_off_flow]).Value

    Select Case iDraw
        Case 0
            Set rEnd = Intersect(rStart.EntireRow, [end_point])
        
            For Each rST In Range(rStart, rStart.End(xlDown))
                If rST.Value = rEnd.Value Then
                    If MaxFlow < Intersect(rST.EntireRow, [draw_off_flow]).Value Then
                        MaxFlow = Intersect(rST.EntireRow, [draw_off_flow]).Value
                    Else
                        If MaxFlow < MaxFlow(rST) Then
                            MaxFlow = MaxFlow(rST)
                        End If
                    End If
                Else
                End If
            Next
        Case Else
            MaxFlow = iDraw
    End Select
End Function

It is recursive, as the function calls itself under a certain condition.

COPY this function and PASTE it into a MODULE in your VBA Project.

Then in the Max Draw column...
[tt]
H4: =MaxFlow(C4)
[/tt]
...and Copy/Paste down.

You must then SaveAs and change the SaveAs Type to an Excel Macro-Enabled Workbook (*.xlsm) and Enable Macros each time you OPEN this workbook.

VOLA, y'all!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[briand2]

Skip,
Thank you for your help.
I had hoped (over-optimistically) to achieve it without resorting to VBA, but I agree with you that it's just not possible (although I spent quite a few hours going round in circles trying to achieve it!).
I used to have some of my own VBA including UDF's in this Workbook, but I managed to redevelop it to use only Worksheet formulae (as some of the people I give it to don't like / trust VBA). Anyway, I might as well bite the bullet and reintroduce VBA after all, which will make my life easier!
Brian

 

[SkipVought]

but I managed to redevelop it to use only Worksheet formulae

Perhaps you created formulas for levels of your structure?

Plz share your solution for the benefit of all.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[briand2]

Skip,
Sorry for delayed reply - Bank Holiday long Easter weekend in UK (although I've been busy on this as described below!).

I didn't mean that I had managed to carry out this particular task without VBA, I just meant that over the years I'd managed to supersede other VBA routines in the workbook by use of formulae.

Anyway, I thought I'd have one last attempt at carrying out this particular task without VBA and I have been successful (but it is not very pretty and not very fast - relying indeed, as you pointed out, on different formulae for different levels of the structure and combining the results). If my structure extended much further (they do in practice), the extra formula-entry would be somewhat tedious, to say the least.

I have also slightly altered your excellent MaxFlow code to make it MaxFlowAlt, the changes being:

a) making it application.volatile (as otherwise it 'missed' changes to draw-offs on other rows
b) always starting the loop at the top-most row (as, in practice, the pipe data is not entered in the neat way that I previously presented it - sometimes a downstream pipe may be listed on a row above the pipe under consideration)

Function MaxFlowAlt(rStart As Range) As Integer
'BDD 14/apr/17  'calling parameter rStart is address of start point of my pipe
Application.Volatile 'ensure recalculation when any cell on worksheet changes

Dim rST As Range 'local storage
Dim rEnd As Range 'address of end of pipe
Dim iDraw As Integer 'draw-off flow
Dim rTopPipeRowStart As Range 'start point cell of top pipe row (may not be Pipe 1 if I eventually allow sorting)

iDraw = Intersect(rStart.EntireRow, [draw_off_flow]).Value 'value of draw_off_flow on my pipe row
Set rTopPipeRowStart = Intersect([top_pipe_row], [start_point])
'Debug.Print rTopPipeRowStart.Address, rTopPipeRowStart.Value

'Debug.Print "pause..."

Select Case iDraw
    Case 0
        Set rEnd = Intersect(rStart.EntireRow, [end_point])
        For Each rST In Range(rTopPipeRowStart, rTopPipeRowStart.End(xlDown)) 'now starts from top-most pipe row start_point
                If rST.Value = rEnd.Value Then 'this pipe is served by my pipe
                    If MaxFlowAlt < Intersect(rST.EntireRow, [draw_off_flow]).Value Then
                        MaxFlowAlt = Intersect(rST.EntireRow, [draw_off_flow]).Value
                    Else
                        If MaxFlowAlt < MaxFlowAlt(rST) Then
                            MaxFlowAlt = MaxFlowAlt(rST)
                        End If
                    End If
                Else
                End If
        Next
    Case Else
        MaxFlowAlt = iDraw
    End Select

End Function

I shall upload three versions of my workbook, each named to show approach, i.e. without UDF, with UDF MaxFlow and with UDF MaxFlow Alt. In each case, entering the Draw-off values in the cells to the right of the pipe layout diagram should result in the green and red values on the pipe layout diagram changing to show the results (except for MaxFlow, which is not application.volatile and is also confused by deliberately 'untidy' data entry). It seems I can only upload one file per post (?), so I shall do two further posts with the second and third files attached, which I hope does not break the forum rules...)

Regards,

Brian

 

[briand2]

Second approach...using MaxFlow UDF.

 

[briand2]

Third approach...using MaxFlowAlt UDF.