Extracting split values from table based on dynamic input

[WaterMan40]

Hello,

I am performing a hydraulic study on a water treatment plant and I have run into something I don't know how to do. This plant has four clarifiers (two different sizes) fed by a splitter box that does not distribute the flow evenly. I need to take the calculated flow and put that into a head loss formula, there is a shared piping network so I need the individual flows when more than on clarifier is in service. All of my other calculations are for units that receive equal flow and on the summary tab I have a slider bar to allow for the user to select the flow of interest and then every calculation takes this as an input. I still want to use that input here.

I have made a table that shows the flow to each clarifier.

What I want to do is reference the input cell where the selected flow is,

• if it is 8 or less then display the actual value - (easy, I got this)
• If the flow is 16 or less, sum the balanced flows between the two, ie. show half the flow for each - (easy, can do this too)
• If the flow is 21 or less, I need to take the partial flow each clarifier is receiving, eg. for a flow of 20 the rounded values are 7.7,7.7 and 4.5, I need to use each of these values in different places
• The same is needed for values over 21. ( ex. Flow = 28.5, the individual flows are 5.3, 5.3, 9.0 and 9.0)

What formula combination can I use to select the value of interest based on the varying input and subject to the constraints of when the units are put into service?

I have tried nested IF(AND(VLOOKUP and MATCH(VLOOKUP variations to no success. I have not tried VBA.

Attached is an image of my table, the green boxes are just for reference when each clarifier is put into service and their ideal operating range.

Thanks for your input.

 

[Rijnsent]

Hi Waterman,
what I would do:
one cell with a formula like: =MATCH(E1,IF(E1<8,$B$6:$B$57,IF(E1<16,$G$6:$G$57,IF(E1<21,$K$6:$K$57,$Q$6:$Q$57))),1) (E1 is the cell in this example with your total flow). That should give the row with the answer. Next, you'd only need a couple of OFFSET formulas to find the 4 flow-values.
Hope that helps.
Koen