Solver with rounding issues and running very slow with integer constraint

[tropics123]

Hi, I'm trying out Solver and it's very very slow! My test spreadsheet has 25 rows of test data and it ran over 30 minutes and froze all the other Excel screens, so I finally ended the task. It probably would've ran for 2 hours if I let it. Part of the issue is, the constraint is set to integers. If I removed the constraint then it ran faster. But the constrained was needed otherwise, Solver gave answers with a bunch of decimals. I contacted Microsoft Solver support and the person said it was "normal" to let Solver run for hours on her PC. That is not a feasible solution. Does anyone have any tips?

This is a test run because usually we have thousands of rows with rounding issues (multiple files every day and amounts change) and we want a more automated way to add a penny to random rows rather than manually doing that for thousands of rows. It's not a fun task

Here's what I'm doing:

*Last row is the total for each column
1) 1st column - Data from client. Last row shows the client expects bank to withdraw $5093.95. Problem with this is rounding. The client types in amounts on some rows up to four decimal points.
2) 2nd column - Bank only pulls up to two decimal points and the actual amount bank plans to withdraw is $5093.93, not $5093.95 (as client expected - this is amount from first column)
3) 3rd column - Since Solver can't get down to the pennies, Microsoft Solver Support suggested scaling. I took the "Data From Client" and multiplied by 100.
4) 4th column - "Solver Results" is from the Microsoft Solver Support Rep when she ran it with the integer constraint. Without the constraint, the answer would include decimal points.
5) 5th column - take the "Solver Results" and divide by 100


[TABLE="width: 529"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 669"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]Data From Client[/TD]
[TD="align: center"]Actual Data From Client[/TD]
[TD="align: center"]Scaled data for Solver[/TD]
[TD="align: center"]Solver Results[/TD]
[TD="align: center"]Answer[/TD]
[TD="align: center"]Difference [/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]234.0000[/TD]
[TD="align: center"]234.0000[/TD]
[TD="align: center"]23400[/TD]
[TD="align: center"]23400[/TD]
[TD="align: center"]234.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]125.0000[/TD]
[TD="align: center"]125.0000[/TD]
[TD="align: center"]12500[/TD]
[TD="align: center"]12500[/TD]
[TD="align: center"]125.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]500.0000[/TD]
[TD="align: center"]500.0000[/TD]
[TD="align: center"]50000[/TD]
[TD="align: center"]50000[/TD]
[TD="align: center"]500.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]800.0000[/TD]
[TD="align: center"]800.0000[/TD]
[TD="align: center"]80000[/TD]
[TD="align: center"]80000[/TD]
[TD="align: center"]800.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]88.0500[/TD]
[TD="align: center"]88.0500[/TD]
[TD="align: center"]8805[/TD]
[TD="align: center"]8805[/TD]
[TD="align: center"]88.05[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]110.4126[/TD]
[TD="align: center"]110.4100[/TD]
[TD="align: center"]11041[/TD]
[TD="align: center"]11041[/TD]
[TD="align: center"]110.41[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]190.8335[/TD]
[TD="align: center"]190.8300[/TD]
[TD="align: center"]19083[/TD]
[TD="align: center"]19083[/TD]
[TD="align: center"]190.83[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]212.3040[/TD]
[TD="align: center"]212.3000[/TD]
[TD="align: center"]21230[/TD]
[TD="align: center"]21230[/TD]
[TD="align: center"]212.30[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]253.7500[/TD]
[TD="align: center"]253.7500[/TD]
[TD="align: center"]25375[/TD]
[TD="align: center"]25375[/TD]
[TD="align: center"]253.75[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]202.8540[/TD]
[TD="align: center"]202.8500[/TD]
[TD="align: center"]20285[/TD]
[TD="align: center"]20285[/TD]
[TD="align: center"]202.85[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]191.9790[/TD]
[TD="align: center"]191.9800[/TD]
[TD="align: center"]19198[/TD]
[TD="align: center"]19198[/TD]
[TD="align: center"]191.98[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]215.3669[/TD]
[TD="align: center"]215.3700[/TD]
[TD="align: center"]21537[/TD]
[TD="align: center"]21537[/TD]
[TD="align: center"]215.37[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]103.0500[/TD]
[TD="align: center"]103.0500[/TD]
[TD="align: center"]10305[/TD]
[TD="align: center"]10305[/TD]
[TD="align: center"]103.05[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]152.3960[/TD]
[TD="align: center"]152.4000[/TD]
[TD="align: center"]15240[/TD]
[TD="align: center"]15240[/TD]
[TD="align: center"]152.40[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]211.3125[/TD]
[TD="align: center"]211.3100[/TD]
[TD="align: center"]21131[/TD]
[TD="align: center"]21131[/TD]
[TD="align: center"]211.31[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]99.8440[/TD]
[TD="align: center"]99.8400[/TD]
[TD="align: center"]9984[/TD]
[TD="align: center"]9984[/TD]
[TD="align: center"]99.84[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]32.4417[/TD]
[TD="align: center"]32.4400[/TD]
[TD="align: center"]3244[/TD]
[TD="align: center"]3244[/TD]
[TD="align: center"]32.44[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]190.5415[/TD]
[TD="align: center"]190.5400[/TD]
[TD="align: center"]19054[/TD]
[TD="align: center"]19054[/TD]
[TD="align: center"]190.54[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]0.0000[/TD]
[TD="align: center"]0.0000[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]162.5000[/TD]
[TD="align: center"]162.5000[/TD]
[TD="align: center"]16250[/TD]
[TD="align: center"]16250[/TD]
[TD="align: center"]162.50[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]466.8996[/TD]
[TD="align: center"]466.9000[/TD]
[TD="align: center"]46690[/TD]
[TD="align: center"]46691[/TD]
[TD="align: center"]466.91[/TD]
[TD="align: center"]0.01[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]63.6000[/TD]
[TD="align: center"]63.6000[/TD]
[TD="align: center"]6360[/TD]
[TD="align: center"]6360[/TD]
[TD="align: center"]63.60[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]36.1315[/TD]
[TD="align: center"]36.1300[/TD]
[TD="align: center"]3613[/TD]
[TD="align: center"]3613[/TD]
[TD="align: center"]36.13[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]302.0835[/TD]
[TD="align: center"]302.0800[/TD]
[TD="align: center"]30208[/TD]
[TD="align: center"]30208[/TD]
[TD="align: center"]302.08[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]148.6040[/TD]
[TD="align: center"]148.6000[/TD]
[TD="align: center"]14860[/TD]
[TD="align: center"]14861[/TD]
[TD="align: center"]148.61[/TD]
[TD="align: center"]0.01[/TD]
[/TR]
[TR]
[TD="align: center"]Total -->[/TD]
[TD="align: center"]5093.95[/TD]
[TD="align: center"]5093.9300[/TD]
[TD="align: center"]509,393.00[/TD]
[TD="align: center"]509,395.00[/TD]
[TD="align: center"]5,093.95[/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

I am sorry' don't know why its complicated. I tried and i am getting both the results from my end.

Excel 2013/2016
	A	B
1	Data From Client	Result
2	234.00	234.00
3	125.00	125.00
4	500.00	500.00
5	800.00	800.00
6	88.05	88.05
7	110.41	110.41
8	190.83	190.83
9	212.30	212.30
10	253.75	253.75
11	202.85	202.85
12	191.98	191.98
13	215.37	215.37
14	103.05	103.05
15	152.40	152.40
16	211.31	211.31
17	99.84	99.84
18	32.44	32.44
19	190.54	190.54
20	0.00	0.00
21	162.50	162.50
22	466.90	466.90
23	63.60	63.60
24	36.13	36.13
25	302.08	302.08
26	148.60	148.60
27	5093.95	5093.93
Sheet9

Excel 2013/2016
	A	B
1	Data From Client	Result
2	234.00	234.00
27	5093.95	5093.93
Sheet9

Cell Formulas
Range	Formula
B2	=ROUND(A2,2)
B27	=SUM(B2:B26)
A27	=SUM(A2:A26)

 

[Sam_D_Ben]

Also try formatting both the columns.

 

[tropics123]

Hi Sam_D_Ben, the total that the client expects the bank to withdraw is $5093.95, however, after the bank rounds to two decimal places the total is actually $5093.93 (off by two pennies). We've tried asking the client to only give numbers up to two decimal places but they won't listen. I want the Solver to get the total to $5093.95. All the changing variables the Solver returned can only be up to two decimal points. I got it to run before but after clicking on each cell in the changing variable, there were numbers it returned up to nine decimal points. I think you might've swapped the totals around on your example. The total we want Solver to achieve is $5093.95.

 

[Sam_D_Ben]

I am sorry' i understand the problem, I tried but wondering why is that difference.

Excel 2013/2016
	A	B	C	D
1	Data From Client	Rounded figure	Solvers Result	Status
2	466.8996	466.90	466.91	Error
3	63.6000	63.60	63.6
4	36.1315	36.13	36.13
5	302.0835	302.08	302.08
6	148.6040	148.60	148.61	Error
Sheet1