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

Joe00

New Member
Joined
Jul 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,584
Messages
5,597,026
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top