multivariable optimization (cost and output pressure)

[stkyle]

Hi there,


I'm working on a rather large optimization program. I really should be using Matlab, but my company doesn't have it, so here I am. Anywho, I'm working to optimize pump cost efficiency. I have about 30 different pump combinations, with 15 drag reducer concentrations at one pumping station, and then 15 more drag reducer concentrations at another (sounds like one of those horrible grade school word problems huh). What I need to do is take the best final pressure outputs (between 50 and 65), cross-reference with the corresponding costs and select the minimum cost. What I'm going for is a user input flow rate which then instantly outputs best pump combination, both drag reducer concentrations, and final cost.

I was messing around with conditional formatting and I could get the pressure outputs I wanted a very pretty green color, but that's about it.

Not only can I not disclose the data, it's seriously huge, with 7000 combinations of pump and drag reducer. I could make an example file given some time though if you need to see what I'm talking about.

Any help would be much appreciated.

 

[Steve=True]

Tough without data.

I would use a SumProduct or Index and Match functions since it is a multivariable equation to search the data to return the appropriate minimum cost.

http://www.contextures.com/xlFunctions01.html

http://www.ozgrid.com/forum/showthread.php?t=80634&page=1

Here are a couple of URLs with samples.

 

[stkyle]

Thanks steve, your name made me chuckle by the way. I used match and index in a similar program, was hoping to avoid it this time around as it gets confusing fast, but I think I'll have to just dig in. Sumproduct seems a step in the right direction, no need for the sum though. I was thinking some combination of logicals might help, I'll have to do some experimenting

I threw together this basic example so you guys could have a look if you're interested... Just saw that attachments can't be posted without downloading the html maker, and I can't download anything atm. So if you want a copy, email st.kyle@hotmail.com

I'm using 2007 btw (unfortunately)

 

[Steve=True]

SumProduct is a great function that can be treated as a boolean operator for count, average, sum, sumproduct, etc.

Very powerful. Don't let the function name mislead you. But my name should mislead

 

[stkyle]

Just in case anyone out there had the same problem, here's how i solved it:

Final output from my pressure equations into one large matrix, from that another matrix with the corresponding costs, and a third matrix consisting of =IF(AND(M6>50, M6<65),AE6) that way only the prices for the appropriate pressure values are displayed, all else are FALSE. Then it's simply a matter of using MIN to find the smallest cost and combinations of MATCH and LOOKUP to find the corresponding concentration and pump combinations.

There you have it. Inputs --Magic--> optimized!