Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set more than 1 target cell using MS Solver?
I am trying to build a spreadsheet to adjust a certain flow rate to maintain
a specified tank volume. For instance, I want to maintain a tank volume of 234000gal. I have a water demand that varies from 1400-2400gal/min (gpm), with an average of 1600gpm. The supply flow rate can range from 1200-1800gpm. I want to be able to adjust the supply flow rate to maintain the tank volume. The demand flow rate is a set of values based on historical data. When the tank volume is less than 234000gal, the supply flow rate will max out at 1800. When the the tank volume is less than 234000gal, the supply flow rate will decrease. I can get the solver to work for a single set of data, but I want to use the solver to calculate a cummulative tank volume as the demand and supply flow rates change. I appreciate an help that you can offer. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set more than 1 target cell using MS Solver?
It seems like this should be easy enough to do without Solver, with a
formula. How do you have the data set up and how does it get entered? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set more than 1 target cell using MS Solver?
n8wildey wrote...
I am trying to build a spreadsheet to adjust a certain flow rate to maintain a specified tank volume. *. . . FYI, spreadsheets and most other programming mechanisms that execute discrete statements sequentially can only crudely approximate analog (continuous) systems. You really should use software that handles transfer functions for modeling control of analog systems. . . . For instance, I want to maintain a tank volume of 234000gal. I have a water demand that varies from 1400-2400gal/min (gpm), with an average of 1600gpm. The supply flow rate can range from 1200-1800gpm. . . . So the outflow rate can exceed the inflow rate by 600 gpm. When the outflow rate is less than 1800 gpm, the inflow rate need only match it to maintain the tank's contents. OTOH, when the outflow rate exceeds 1800 gpm, you can't maintain the tank's contents. All you can do is keep the inflow rate at 1800 gpm until the tank is replenished. Unless you're being silent about stresses or costs that vary with the inflow rate, the obvious answer is use only 1800 gpm as the inflow rate. When the outflow rate is less than 1800 gpm, let the tank's contents drop by some discrete amount before replenishing at 1800 gpm. In any event, this doesn't require Solver. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set more than 1 target cell using MS Solver?
I guess to clarify, I want to predict the tank volume and supply flow rate
for a given demand flow. I understand that the volume will drop below the set point of 234,000gal when the demand flow is greater than the maximum supply flow. When the demand flow is less than 1800gpm, the supply flow will remain at 1800gpm until the volume reaches its set point of 234,000gal. My spreadsheet is set up such that column A is the tank volume, column B is the demand, and column C is the supply. As a starting point, A1 = 234,000, B1 = 0 and C1 = 0. The subsequent rows use the following formula: A2 = A1-B2+C2, where B2 is a value based on historical data, thus is a given value for the corresponding date and time. I need to solve for (or predict) the value of C2, with a constraint of 1800gpm. Maybe I don't need to use Solver, but I am struggling with developing a formula to meet my needs. I am working with an extremely large spreadsheet so autofilling a formula is critical. I am using this to design a new water treatment system. I want to minimize the amount of equipment I need to purchase. I appreciate the help, and please let me know if you need more information. Thanks... "Harlan Grove" wrote: n8wildey wrote... I am trying to build a spreadsheet to adjust a certain flow rate to maintain a specified tank volume. . . . FYI, spreadsheets and most other programming mechanisms that execute discrete statements sequentially can only crudely approximate analog (continuous) systems. You really should use software that handles transfer functions for modeling control of analog systems. . . . For instance, I want to maintain a tank volume of 234000gal. I have a water demand that varies from 1400-2400gal/min (gpm), with an average of 1600gpm. The supply flow rate can range from 1200-1800gpm. . . . So the outflow rate can exceed the inflow rate by 600 gpm. When the outflow rate is less than 1800 gpm, the inflow rate need only match it to maintain the tank's contents. OTOH, when the outflow rate exceeds 1800 gpm, you can't maintain the tank's contents. All you can do is keep the inflow rate at 1800 gpm until the tank is replenished. Unless you're being silent about stresses or costs that vary with the inflow rate, the obvious answer is use only 1800 gpm as the inflow rate. When the outflow rate is less than 1800 gpm, let the tank's contents drop by some discrete amount before replenishing at 1800 gpm. In any event, this doesn't require Solver. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set more than 1 target cell using MS Solver?
. . . For instance, I want to maintain a tank volume of
234000gal. I have a water demand that varies from 1400-2400gal/min (gpm), with an average of 1600gpm. The supply flow rate can range from 1200-1800gpm. . . . Starting in C2: =IF($E$1-(A1-B2)=B2,MIN($E$2,$E$1-(A1-B2)),B2) Where E1 is your max volume of 234000. And E2 is the max inflow of 1800. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set more than 1 target cell using MS Solver?
Hi. Just a quick comment...
I want to predict the tank volume I believe you will also need to factor in the time interval in order to predict volume. When demand is greater than supply, the longer this situation lasts, the lower the volume. I am working with an extremely large spreadsheet From a Solver view, keep in mind that Solver can have only 200 changing cells. It sounds like it won't be an option here. Also, the tendency here would be to use IF(), Max(), and Min() functions in your model. Solver won't work well if you use these functions. - - - Dana DeLouis <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Several target cells in the solver | Excel Discussion (Misc queries) | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
In cell drop down menu with seprerate numerical target cell | Excel Discussion (Misc queries) | |||
How to get Solver to target array rather than cell? | Excel Worksheet Functions | |||
Goalseek / Solver Target Value | Excel Worksheet Functions |