How do I set more than 1 target cell using MS Solver? n8wildey 12/31/2008 1:04:01 PM 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.

Re: How do I set more than 1 target cell using MS Solver? Spiky <jeff.thorstad[ at ]gmail.com> 12/31/2008 9:55:51 PM 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?

Re: How do I set more than 1 target cell using MS Solver? Harlan Grove <hrlngrv[ at ]gmail.com> 12/31/2008 10:35:33 PM n8wildey <n8wil...[ at ]discussions.microsoft.com> wrote... [Quoted Text] >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.