# Need Help Using Excel Solver Tool

#### tab10us

##### New Member
I am trying to run solver, but I am having problems:

2 of my constraints have the reference cell listed as #DIV/0! obviously because I am dividing by 0. That is because the cell that is 0 is the sum of the amount in the variable cells that are to be changed. The cell won't change from 0 until solver is run, but solver won't run because of the #DIV/0! error in one of my constraints.

One of the formulas that is giving me the error is:
SUMPRODUCT(variable cells, cells with data)/sum of variable cells
Therefore, since the variable cells are currently blank, the sum is 0 and is producing the error.
The constraint is the answer from the sum product >=27

I was told that I somehow need to re arrange the formula, but I can't figure out what I can change around to get rid of the error.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### tusharm

##### MrExcel MVP
One obvious approach would be to populate one of the variable cells with a value of, say, 1.

Another would be to modify the formula along the lines of IF(SUM(variable cells)=0, a very large number, current formula)

I am trying to run solver, but I am having problems:

2 of my constraints have the reference cell listed as #DIV/0! obviously because I am dividing by 0. That is because the cell that is 0 is the sum of the amount in the variable cells that are to be changed. The cell won't change from 0 until solver is run, but solver won't run because of the #DIV/0! error in one of my constraints.

One of the formulas that is giving me the error is:
SUMPRODUCT(variable cells, cells with data)/sum of variable cells
Therefore, since the variable cells are currently blank, the sum is 0 and is producing the error.
The constraint is the answer from the sum product >=27

I was told that I somehow need to re arrange the formula, but I can't figure out what I can change around to get rid of the error.

Replies
0
Views
51
Replies
1
Views
45
Replies
1
Views
121
Replies
0
Views
441
Replies
1
Views
596

1,137,120
Messages
5,679,723
Members
419,853
Latest member
hc9587

### 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.

### Which adblocker are you using?

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

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