Multi-Solving

  • Thread starter Andrew Mackenzie
  • Start date
A

Andrew Mackenzie

I would appreciate it if someone could tell me whether it is possible to use
the Solver on several cells in a sheet at once and if so how to do it.

E.g. I want to set cells and D5, E5 and F5 to zero by changing cells D1, E1
and F1.

Thanks inn advance.
 
J

Jerry W. Lewis

Use
=MAX(ABS(D5),ABS(E5),ABS(F5))
in another cell, and ask Excel to solve for a value of zero in that cell.

Jerry
 
A

Andrew Mackenzie

Cheers Jerry - worked just fine.
Jerry W. Lewis said:
Use
=MAX(ABS(D5),ABS(E5),ABS(F5))
in another cell, and ask Excel to solve for a value of zero in that cell.

Jerry
Click to expand...
 
D

Dana DeLouis

Just to share. I'm glad it worked, but "usually" Solver will not work with
either "Max" or "Abs." Solver does not have enough logic to work out a
discontinuous jump with Max, or to figure out what happened with a reversal
using Abs.
Just a thought, but I'm glad it worked. :>0

Dana DeLouis
 
D

Dana DeLouis

D6 =SUM(D5:F5)?

Just to mention. In D5:F5, Solver may try 2,3,-5. This sums to zero, but
would not be a solution. Actually, there are many possible
combinations(1,-1,0), etc...
One common solution that sometimes works is setting the following to zero.
=SUMSQ(...)
Another option sets all cells equal, so to get all zeros you need to add
that one of the cells is zero.
=DEVSQ(...)

These don't work in all models, so it's just something to try.
HTH
Dana DeLouis
 
A

Amedee Van Gasse

Andrew said:
I would appreciate it if someone could tell me whether it is possible
to use the Solver on several cells in a sheet at once and if so how
to do it.

E.g. I want to set cells and D5, E5 and F5 to zero by changing cells
D1, E1 and F1.

Thanks inn advance.
Click to expand...

Does this work?

=AND(IF(D5:F5=0;TRUE();FALSE()))
Enter in H5 as a matrix formula
And in G5:
=IF(H5;0;1)
Solve this to zero.
 
Top