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

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

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[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