using Solver add-in

[rosborne]

Are there certain general guidlines to follow when using Solver? - I
am trying to set up a model and keep getting "Solver could not find a
feasible solution." I have reduced my model to the very basics and
keep getting the error, so I believe I am trying to do something
Solver does not like. The general scenario: I work at a hospital and
have twenty patients. Each patient could receive service "1" or
service "2." Depending on the insurance of the patient, I will
receive a different payment. I can provide four "2s" in the amount of
time it takes to perform one "1.".
The model:
Column B is patient (30 rows)
Column C is insurance
Column D is service - either a 1 or 2.
Column I is the amount of payment, depending on the values of columns
C and D
I want to maximize cell I30 (sum of column) subject to constraints on
column D of
D1231 = integer
D1231 >= 2
D1231 <= 1
E10 <= 10 (this cell is the total time based on the number of "1s" and
"2s" in column D)

I can key in different values in column D to determine the max, but
cannot get Solver to do it. Column I has formulas using vlookup to
values in other columns that are dependant on values in columns C and
D. Perhaps that is my problem? Are there things Solver does not
like? Sorry to post so "general" a question, and a long one at that.

Thanks
Rick

 

[Dana DeLouis]

Each patient could receive service "1" or

service "2."

D1231 = integer
D1231 >= 2
D1231 <= 1

Just guessing of course. In the above, I believe you are excluding the
interval 1-2.
Did you mean the following?

D1231 = integer
D1231 >= 1
D1231 <= 2


As a side note...If you need a choice of 1 or 2, perhaps use a binary
changing cell (meaning 0 or 1). Then use a function to add +1 to this.
Therefore, you get either 1 or 2. This uses 1 constraint instead of
your 3 constraints.

= = = = = = =
HTH
Dana DeLouis

 

[Rick Osborn]

I mis-typed in my message - I had "<=2" and ">=1" in my model. I changed to
binary, though, to reduce to one line, and still get the "unable to solve"
message. Any other ideas?

 

[Dana DeLouis]

Column C is insurance

Hi. Without knowing the model, I would be just guessing here.
I understand that you are trying to fill in a "1 or 2" into column D,
and trying to maximize profit.
The '1 or 2' in turn, determine the amount in Column I.

My best guess is that you are using a lookup table to fill in the
amounts in Column I. Solver does not work well when you use Lookup, IF,
Max, Min,...etc because these cause "jumps" Solve needs to calculate a
derivative using finite differences, and these types of functions just
don't work.

Is your model using "IF" functions? This is a common problem also.

Don't know what to suggest here. You may have to rethink your model.

For example, I'll just throw this out in case you are using a table.
It appears each person can have either of two payments based on 1 or 2.
Enter these two payments, and have another column set up to calculate
the payment to you. We will use a binary column of changing cells to
pick which of the two.
The equation might be something like...
= pay1 + Bin * (Pay2 - Pay1)

When Solver uses 0, you get Pay1, and when it picks 1, you get Pay2.

This would avoid a Lookup.
Of course, I'm just guessing here for your case.
= = = = =
HTH ;>)
Dana DeLouis

 

[rosborne]

Dana - Aha - I think you got it. Your guessing - which must be
instinct - was right. The payment column was taking the inputs and
referencing a table (with IF, AND, MATCH...). I see what you mean
about the 'jumps.' Thank you for the find.

 

[Dana DeLouis]

Hi. Glad to help. Unfortunately, Solver doesn't give error messages
from these types of errors. It just gives up pretty quickly. :>(

Just to add. You may find this interesting.
The equation between the two payments is really the following equation,
just rewritten. In general, this is how to select between two values in
Solver.

= Pay1*(1-Bin) + Pay2*(Bin)

When Bin is zero, you get Pay1, and when Bin is 1, you get Pay2.
Solver can keep track of the reason for this particular jump because it
is keeping track of the Bin value internally.

May I make another guess?

I can provide four "2s" in the amount of

time it takes to perform one "1.".

E10 <= 10

I may have the logic wrong here, so this is just being "general."

Your general equation is probably:
1.0 * Type1 + 0.25 * Type2 <= 10

Mathematically, let's multiply by 4.

4 * Type1 + 1 * Type2 <= 40

(This is my personal preference, and from habit from classes in OR.)

We can use the Binary variable to simplify.
What we want is a linear equation to transform
0 -> 4, and 1 -> 1

= 4 - 3*Bin

When Bin is 0, we get 4, and when Bin =1 we get 1.

So, the new constraint is that the total "time" <= 40.

= = = = = = = =
Anyway, I may be wrong, so just throwing it out if it helps.
Dana DeLouis

 

[helene and gabor]

Hello,

I think your dilemma is that solver is not set up to decide line by line whether service 1 or 2 shall be performed. It works with a mixture of options which is not your case.
If you have 10 hours available, and service one takes a given time and service two takes one fourth that much time, then you almost know how many patients shall be served service 1 and 2.
Then the question is which patients shall have which service.

Gabor