Need Help Using Excel Solver Tool

tab10us

New Member
Joined
Sep 25, 2012
Messages
1
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.

PLEASE HELP!!!
 

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
Joined
May 28, 2002
Messages
11,029
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.

PLEASE HELP!!!
 

Forum statistics

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