How do I set more than 1 target cell using MS Solver?

[n8wildey]

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.

 

[Spiky]

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?

 

[Harlan Grove]

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.

 

[n8wildey]

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...

 

[Spiky]

. . . For instance, I want to maintain a tank volume of
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.

 

[Dana DeLouis]

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>