Using solver and getting 1004 run time error

Your file is .xltx which cannot contain any VBA code, so I'm afraid we can do nothing to help debug this.

My first guess (because it is a common mistake) is to check your VBA project and make sure you have set a reference to the Solver add-in in VBA https://peltiertech.com/Excel/SolverVBA.html If you fail to set a reference, then your VBA project cannot find and use any of Solver's functions/procedures.

This one is an .xlsx file, which also cannot contain VBA code. Have you tried stepping through the code so you can identify the statement that is triggering the error?

I think the confusion comes from having this thread in the VBA forum where I assumed you were trying to call Solver from VBA.

When I try your existing Solver model, I get an error in model warning and nothing changes. Looking at what your model is trying to do (maximize D3 by changing E2), when I explore the behavior of D3, I see that it asymptotically approaches 1 as the maximum value as E2 approaches infinity. My guess is that, at some point early in its run, Solver tries a large number for E2, its calculation for D3 "underflows", which generates an error inside of Solver.

I feel like I am misunderstanding something in your optimization model, because there doesn't seem to be a "good" solution to be found. Put your largest valid value in E2 and you will get the largest value in D3 -- problem solved. What am I missing?

Originally Posted by MrShorty

I think the confusion comes from having this thread in the VBA forum where I assumed you were trying to call Solver from VBA

You might have also come to that "foolish" conclusion (wink) because DRTFlo wrote initially: ``Every time I try to run this it gives me an error "Run-time error '1004': Application-defined or object-defined error``, and the title (DRTFlo's?) of the thread is ``Using solver and getting 1004 run time error``.

Of course, that is a VBA error. I do not believe that Solver itself displays errors of that form; and I'm quite sure that Solver is not implemented using VBA.

GIGO!

It looks like there is a lot of confusion all around and misunderstanding all around. Maybe start over?

ON sheet 2 HCA I have my inputs Set Objective $D$3 To Value of (my win %) 0.43 By changing variable in cells: $E$2.

I don't know why Excel's Solver is having trouble with this. However, if I open your file in post #3 in a different spreadsheet (Gnumeric) and use it's Goal Seek tool, it quickly solves this problem and finds that a value of -5.26 in E2 results in a value of 0.43 in D3. Does that seem like a correct solution?

I suspect the worksheet is (was, hopefully) corrupted.

OpenSolver shows an empty constraint that does not appear in the Solver mask. After deleting it, the error message disappears, and Solver will happily find a solution. When it doesn't, change your starting point (value in E2).

The value of -5.26 can be found only after unchecking the option (on by default) "Make Unconstrained Variables Non-Negative".

HTH,

Francesco

Originally Posted by DRTFlo

I need to run calculations using solver. ON sheet 2 HCA I have my inputs Set Objective $D$3 To Value of (my win %) 0.43 By changing variable in cells: $E$2

I'm a little confused, because the Solver set-up in the file attached to your posting #3 seeks to maximize D3, not set D3 to a specific value.

In either case, E2 can be calculated directly. There is no need to use Solver at all.

See the worksheet '2. HCA (2)' in my attachment.

The formula in E2 is:

=C2 - B3 - 43*LOG(1/D3 - 1)

Enter the desired value into D3.

With D3 = 43%, E2 is -5.26347520399491 (-8.88E-16).

With max D3 = 1-2^-52, E2 is 673.103070304662.

Of course, that depends on the values in B2 and B3; currently, both are 1500.

-----

Possible TMI....

It seems to me that the formula in D2 could be simply: =1-D3 or =SUM(1,-D3).

The latter formula is more accurate when D3 = 1-2^-52 (max D3), because it avoids dubious tricks that Excel plays, which cause D2 to be exact zero (0.00E+00) even though D3 is not exactly 100%.

-----

Possible TMI....

Algebraic derivation of the formula in E2 based on the original formula in D3....

D3 = 1 / ( 1 + 10^((C2-C3)/43) )

where C3 = B3+E2. So....

1 + 10^((C2-B3-E2)/43) = 1/D3

10^((C2-B3-E2)/43) = 1/D3 - 1

log( 10^((C2-B3-E2)/43) ) = log(1/D3 - 1)

(C2-B3-E2)/43 = log(1/D3 - 1)

C2-B3-E2 = 43*log(1/D3 - 1)

E2 = C2 - B3 - 43*log(1/D3 - 1)

PS.... Since C2 is =B2, I don't know why we don't use B2 instead of C2 in the original formula in D3, which would propagate into the derivation of E2.

Errata.... At about 1:30am(?), I made some corrections to the formula in E2 and the (TMI) math derivation in posting #11 (changed B3-C2 to C2-B3 in E2; change references to D3 to B3 in the derivation). I also replaced the Excel file attachment just now (6:00am). It was removed earlier, but inadvertently not replaced.