I am struggling with very slow solve times when using solver in excel, this seems to be related to file size and not the complexity of the solve calculation.

Some background for what solver is attempting to achieve is below.

I am using solver to generate parameters for 4 and 5 logistic curve fits.

For a 4 parameter fit the following equation is used:

**y=D+(A-D)/(1+(X/C)^B)**

Where:

X= Conc

Y=Absorbance (oD)

A-D are the fits

Solver will use the above equation to generate new oD for each curve point (usually 6 curve points) by changing the A-D parameters. Where the sum of the squared differences (SUMXMY2) is calculated between the original curve oD and the newly calculated oDs.

Solver has a target of 0 for the SUMXMY2 function - to speed solver up I have made the spreadsheet initially calculate the minim ABS and Max ABS (for a and d) and the median Conc (for c), b is just set to 1.

This is all done using the following macro

NB there is code before and after such as disabling automatic calculations, and the solver function is run twice to improve accuracy.

Range("$D$63:$D$66").Select

Selection.Copy

Range("$D$55").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

SolverReset

SolverOptions Precision:=0.000001, Derivatives:=2, Scaling:=True, RequireBounds:=True

SolverOk SetCell:="$D$59", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$55:$D$58", _

Engine:=1, EngineDesc:="GRG Nonlinear"

SolverAdd cellref:=Range("D$56:D$57"), relation:=3, formulaText:=0.00000001

SolverSolve

SolverOptions Precision:=0.000001, Derivatives:=2, Scaling:=True, RequireBounds:=True

SolverOk SetCell:="$D$59", MaxMinVal:=2, ValueOf:=0, ByChange:="$D$55:$D$58", _

Engine:=1, EngineDesc:="GRG Nonlinear"

SolverAdd cellref:=Range("D$56:D$57"), relation:=3, formulaText:=0.00000001

SolverSolve

SolverFinish

Running this code in a spreadsheet (339kb in size) is smooth and quick taking less than a minuet to solve. Adding the same code to a much larger spreadsheet (4.8mb) slows the same curve fit calculation down and takes >10mins to solve.

The sheets are the same where the calculation is taking place and no additional formulas are running off this in the larger sheet- so they should be the same.

With both sheets open and running the solver in the fast sheet (339kb) it will still take >10mins to solve. Suggesting that the large sheet sitting idle is having an impact on the solver add in despite it not being used.

Looking at my processer usage when solver is being run:

Fast sheet only PC is idle= 3% utilisation and a Clock speed of about 1GHz

When solver is run this jumps to about 40-50% utilisation and a clock speed of 2.8-2.9GHz

Having both sheets open PC is Idle = 6% utilisation and a Clock speed of about 1GHz

When solver is run in the smaller sheet this jumps to about 75-80% utilisation and a clock speed of 2.8-2.9GHz

Is there anything I am doing wrong here- Can I speed up the solver function in the larger sheet, I have tried disabling automatic calculations in the entire sheet and I cannot reduce the size, I also need this to work across the business so I cannot just get a faster PC.

Thanks

Joe