Solver

[Soccer boy]

I am currently trying to use Solver to help optimize some water pump usage.
I have three locations where these pumps are located. If the water demand
can be met by two sites alone i would prefer to only use those two sites not
the three.
For instance
Site A - 100gpm
Site B - 100 gpm
Site C - 100gpm
I need 175 gpm so solver would tell me 100% use on Site A, 75% use on Site B
and 0% on site C.
Currently Solver is giving me Site A =58.33%, B=58.33%, and C=58.33%
So is there i way i can do this?

 

[Bernard Liengme]

Great question !
Point 1) when the required amount is <=200, use Solver with only sites A and
B
Point 2) if you start with initial values (guesses) which are equal Solver
gives a solution with equal values.
I solved your problem by starting Site A at 100000% (any very big number), B
at zero; and varied only these two; I got 100% and 75%

A more sophisticated solution would use VBA to set initial values and to
decide how many sites to use for a solution.

Do not try to make a Solver model with IFs or other non-smooth functions

 

[Bernard Liengme]

Here is a non-solver solution
Here is my worksheet
Site A 100 100% need 250
Site B 100 100% sumproduct 250
Site C 100 50%

B1:B3 hold the pump max capacity
C1:C3 hold the percentages
E1 holds the required amount
E2 has the formula =SUMPRODUCT(B1:B3,C1:C3)
This subroutine varied the appropriate pump percentage to make E1=E2
I used steps of 0.01% but you could make it finer

Sub pump()
mygoal = Range("E1")
Range("C1:C3") = 0
If mygoal > 300 Then
MsgBox "Not possible"
Exit Sub
End If
If mygoal <= 100 Then
mypump = 1
ElseIf mygoal <= 200 Then
Range("C1") = 1
mypump = 2
Else
Range("C1:C2") = 1
mypump = 3
End If
Do While Range("E2") < Range("E1")
Cells(mypump, "C") = Cells(mypump, "C") + 0.01
Loop



End Sub

 

[Dana DeLouis]

Hi. For this small problem, note that you do not have any constraints.
Therefore, there are an infinite number of solutions.

One quick solution with 3 equal values (100 gpm) is to assign a penalty
weight for each station. For example, assign a penalty of 1 to #1, 2 to
#2, ..etc

Change the logic to "Minimize the penalty" with the constraint that
Sumproduct(GPM, Percentage) = 175.

Now, you will fill station 1 first, then start using station 2, etc.

Make sure the option for "assume non-negative" is selected. >)

The "General" technique for selecting "2 out of 3" (or whatever) is to
assign 3 cells to hold "Binary" cells (0 or 1 in Solver)

Target:
Sumproduct(GPM, Percentage, Binary) = 175
Subject to:
Binary cells = Bin (Binary)
Sum(Binary) = 2

(I like to use <= 2.1 to account for any rounding issues)

For each, one needs to add the constraint that Percentages <= 1

Good luck.
= = =
HTH
Dana DeLouis