Excel Slover function runs slow in larger sheets, despite no change to the actual calculation

[Joe00]

Hello

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

 

[FracinDean]

Possibly your larger sheet is re-calculating all the formulas in the sheet for each recalculation done by solver. Try setting calculation to manual, and then re-run solver and see if that helps.

 

[Joe00]

Possibly your larger sheet is re-calculating all the formulas in the sheet for each recalculation done by solver. Try setting calculation to manual, and then re-run solver and see if that helps.

I have set it to manual - no help. The solver will run slower when I have any large sheet open, even ones that don't use solver. It seems to be some limitation of excel its self.