Defining the problem for Solver

[Christian]

Hi NG,

I'm not sure if this belongs here or in
microsoft.public.excel.worksheetfunctions, but this NG has never failed to
give me very good suggestions in the past so here goes...

I need to setup a sheet that uses the solver tool to optimize various
factors

The problem looks like this

Column A: OldValue

Column B: StartValue

Column C: factor_A

Column D: factor_1

Column E: NewValue (= Column B * C * D)

Column F: Diff (= Column E - A)

So example would be like this

Column A: 100

Column B: 120

Column C: 80%

Column D: 105%

Column E: 100.80 (120 * 80% * 105%)

Column F: 0.80 (100.80 - 100)

I have several rows of data and I need to find the best possible match of
factor_A and factor_1, i.e. minimize the difference between the old value
and new value by changing values in column C and D.

There are several factors. So if I have 100 rows of data I will have 1
factor in column C but 10 factors in column D.

I have tried to find the best match of factors that would allow me to
minimize the difference between the old and new value by allowing to change
the values of the factors and minimizing a cell that contain VARP() of the
differences listed in column F.

So my question is this - is there a better way to setup the problem than
using VARP()? I want to obtain the best possible match between Column A and
Column E by changing values for the factors in column C and column D.

Any suggestions will be greatly appreciated

- Christian

[merjet]

I don't believe you defined the problem very clearly. You lost me when you
wrote:

>There are several factors. So if I have 100 rows of data I will have 1
>factor in column C but 10 factors in column D.

Merjet

[Christian]

Hi Merjet,

let me try to explain the task this way. Let's say that I have 10 rows of
data.

It could like this
Row 1: OldValue StartValue factor_A factor_1 NewValue Diff
Row 2: OldValue StartValue factor_A factor_1 NewValue Diff
Row 3: OldValue StartValue factor_A factor_2 NewValue Diff
Row 4: OldValue StartValue factor_A factor_2 NewValue Diff
Row 5: OldValue StartValue factor_A factor_2 NewValue Diff
Row 6: OldValue StartValue factor_A factor_3 NewValue Diff
Row 7: OldValue StartValue factor_A factor_4 NewValue Diff
Row 8: OldValue StartValue factor_A factor_4 NewValue Diff
Row 9: OldValue StartValue factor_A factor_5 NewValue Diff
Row 10: OldValue StartValue factor_A factor_5 NewValue Diff

OldValue and StartValue could be anything in the above table. These values
are entered.
NewValue is calculated with StartValue * Factor A * Factor_1 (or Factor_2,
3, 4 or 5 depending on the row).
The Diff is always NewValue - Old Value.

If I change the value of factor_A it is going to affect all NewValues in all
rows.
If I change the value of factor_3 it will only affect NewValue in row 6.

The question is how do I best describe for the solver that I want to get
little difference between NewValue and OldValue? What formula in a cell
would best enable solver to make a suggestion.

Cheers
- Christian

"merjet" <mer...@attbi.com> wrote in message
news:877U7.8957$NM4.3052390@rwcrnsc53...

[merjet]

Hi Christian,

I believe you need to set up your data a little differently for Solver to
handle it. Define factor_A, factor_1, ..., factor_n (uniquely) elsewhere
(the "factor table") and make Columns C and D reference it. Then you can use
the "factor table" as Solver's adjustable cells.

As for Solver's task, it seems you want to minimize the sum of the squares
of all the values in Column F. Is that correct?

Merjet

"Christian" <christian...@mobilixnet.dk> wrote in message
news:OkioNJNiBHA.2264@tkmsftngp05...

[David J. Braden]

Christian,
I'm with Merjet as far as setting up a separate table. But as for an
appropriate objective, there's a lot to consider.

Is each *factor* equally important, but you have a different number of
observations for each? In that case, you would want to *average* the
metric for each of the factors before combining it into the objective,
or use an equivalent weighting scheme (if you have 10 factors with 100
observations, factor 3 would get a weight of 1/10, each line for factor
1 a weight of 1/20, each one for factor 2 a weight of 1/30, etc.)

As for an objective, gazillions come to mind. Merjet suggested the sum
of squares; you could also use the sum of absolute valuies, or any
number of approaches to take into account prior information you have on
the things you are solving for. In any case, be sure to re-run Solver
several times in a row, with high precision, to tighten up your
estimates, and follow that with a graphic check to make sure you
actually have a minimum (Solver will sometimes stop at a saddle point
or local extremum).

Dave Braden

In article <OkioNJNiBHA.2264@tkmsftngp05>, Christian
<christian...@mobilixnet.dk> wrote:

--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz

[Christian]

Hi David & Merjet,

Thanks for both your comments. All the factors are setup in separate table.
So far I have tried to optimize with VARP() and unsuccesfully tried to use a
combination of min() max(). All on the range of the difference column.

Both "sum of squares" and "sum of absolute" sounds like very good ideas -
I'll try them out and see how it works.
To me it seems that all factors are equally important. Value can be anything
and the I want to minimize the difference in each row as much as possible.

>> be sure to re-run the solver.
What would the best approach? Should I try to create a small macro that
would start the solver, copy the result to a separate sheet with factor
values and value of the optimized factor? Or can I get solver to do that
without writing VBA?

>>follow that with a graphic check

Don't get this one. How would I implement a graphic check, any pointers
would be appreciated

My real problem is a bit larger. I will have one factorA and 200 factors
like factor_1 (2, 3 etc). There will be approx. 50.000 rows to calculate the
optimum combination of factors for.

Thanks for all your help
- Christian

[David J. Braden]

Christian,
I don't see where the max() comes in. Aren't you interested in
minimizing the difference(s)?

BTW, are these observations of data, or what? A bit more info about
your problem could help get a sensible objective. But for starters: If
observations are in rows 2:101, then follow Merjet's suggestion so that
in column D every instance of factor_1 is a _reference_ to a single
cell, say, G1; every instance of factor_2 refers to G2, etc.
Similarly, have every instance in column C of factor_A refer to a
single cell.

Now in H1, type in your objective, which might be
=SUM(F2:F101^2)
array-entered (ctrl-shift-Enter on Windows). Or you could use
=SUM(ABS(F2:F101))
array-entered.

Now start Solver, tell it where the objective is, where the "cells to
change" are, specify that you are minimizing, and go for it. Just do
it. No reason to be tentative here, assuming you want to solve the
darn thing.

More inline---

In article <eEMtyuRiBHA.2008@tkmsftngp05>, Christian
<christian...@mobilixnet.dk> wrote:

> Hi David & Merjet,
>
> Thanks for both your comments. All the factors are setup in separate table.
> So far I have tried to optimize with VARP() and unsuccesfully tried to use a
> combination of min() max(). All on the range of the difference column.
>
> Both "sum of squares" and "sum of absolute" sounds like very good ideas -
> I'll try them out and see how it works.
> To me it seems that all factors are equally important. Value can be anything
> and the I want to minimize the difference in each row as much as possible.

This is a different objective than what you first hinted at. Do you
mean you want to minimize the maximal deviation? What Merjet and I
suggested would minimize an average deviation, but not necessarily not
the maximal deviation. For that, your objective would be
=MAX(ABS(F2:F101)). Up to you to know what you are trying to achieve.

>
> >> be sure to re-run the solver.
> What would the best approach?

Just run it from the current solution a few times, saving the values
each round.

> Should I try to create a small macro that
> would start the solver, copy the result to a separate sheet with factor
> values and value of the optimized factor? Or can I get solver to do that
> without writing VBA?

Nah, you don't need VBA. At the very least, sounds like you are too
tentative with this. Just do it.

> >>follow that with a graphic check
> Don't get this one. How would I implement a graphic check, any pointers
> would be appreciated

Depends on your objective, but you can graph it as a function of each
of your factors; just come up with a feasible range for Factor_1, say,
and graph your objective as a function of that, checking to see that
the "optimum" is in fact optimizing.

> My real problem is a bit larger. I will have one factorA and 200 factors
> like factor_1 (2, 3 etc). There will be approx. 50.000 rows to calculate the
> optimum combination of factors for.

No problem, though the graphic check might be tedious.

>
> Thanks for all your help
> - Christian

No problem. Just jump in there and do it, and let us know how it
worked out.

Dave Braden
MVP - Excel

--
FAQ and other goodies: http://www.cpearson.com/excel/newposte.htm
More info and links: http://j-walk.com/ss/

[Dana DeLouis]

Just keep in mind that the limit of only 200 changing cells in Excel's
Solver.
Also, Functions like Min, Max, and ABS do not work in Solver. Solver will
quit immediately once it detects functions like these.
Not knowing your model, it would appear that adding some constraints might
help.

--
Dana DeLouis Windows Me & Office XP

<snip>

[Christian]

Thanks Dana, Merjet and Dave,

The 'Sum of Squares' seems to give me the best solutions. There seems to be
several possible solutions but they all render the same result for 'Sum of
Squares'.

The business case behind is that I'm switching from one pricing system to
another. The final price should be as close as possible to the original
price when we take the new ERP system into use.
Today I have Product and its Price.
In the future I will have: List Price for Product * Customer% *
ProductGroup% = CustomerPriceList

The solver seem to be able to find a acceptable combination for most of the
products.

Thanks for all the good suggestions and comments
Have a Merry Christmas
- Christian

[David J. Braden]

Dana,
What do you mean? I can seek the min of the sum of Absolute values just
fine on the Mac, and assume such is true on Windows. Perhaps the
problem you ran into with ABS was due to something else? Certainly not
the ABS function in my experience.

Regards,
Dave Braden

In article <O5yVpNeiBHA.2196@tkmsftngp07>, Dana DeLouis
<ng_...@hotmail.com> wrote:

--

[David J. Braden]

Thanks for the feedback, Christian. It's pretty rare, and I gotta tell
you, it's a weird feeling tossing stuff out into the void, not knowing
(in cases like this) if things worked out.

All the best to you, Merjet and Dana.
Dave Braden

In article <uHhNdseiBHA.2212@tkmsftngp04>, Christian
<christian...@mobilixnet.dk> wrote:

--

[Tushar Mehta]

Solver does a small perturbation to the starting values to check what
happens to the Objective Function. If nothing happens, Solver stops.

I've run into this problem creating an optimization willy-nilly (using
IFs, MINs, MAXs, etc.) Here's a trivial example:

Minimize MIN(MAX(150,A1),A2)

subject to A1,A2>=0

By simple inspection the optimum is A2=0 and an objective value of 0.

Start with A1=100, A2=200 (actually any starting combination with A1<150
and A2>150, such as A1=149,A2=151). Solver won't budge.

Even with a starting value of (A1>150, A2=200), Solver will find the
solution (A1=0, A2=200) with an objective value of 150.

Another problem, as you already know, is the use of such functions
introduces non-linear (and/or non-smooth) functions into the model and
makes them that much more difficult to solve.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--

In <211220011727063446%no...@ugotta.bekidding.com>, David J. Braden
<no...@ugotta.bekidding.com> wrote

[Dana DeLouis]

Hi Dave. You may find these article interesting...

Standard Excel Solver - Problems with Nonsmooth and Discontinuous Functions
http://www.solver.com/suppstdnonsmooth.htm

Discontinuous and Non-Smooth Functions
http://www.frontsys.com/dscontin.htm

Another related topic to the above is the personal observation that if
Solver adjusts any cells, and nothing changes, then Solver just gives up
immediately. No questions asked!
It is not set up to handle anything too complicated.
What I mean is the following observation. If one uses something like...
=If(A1<100,1, 500), or =Min(A1, 500)

(The following is just a general idea) As an example, Solver will guess
that A1 should be 1. It then figures out the result of the above formula.
It then tries setting A1 to 2. The results of the above functions do not
change.
Solver was expecting different answers in order to make an educated guess
for the next value of A1.
Since nothing changed, Solver just quits! Done!

In very general terms, suppose one really wanted to use an idea of..
=Min(A1, 500)

If I am not mistaken, the way one works around this is to introduce Binary
variables.
Suppose Cell B1 is set up as a Binary variable. Then I believe one would
use something similar to the following...
=A1 * (1-B1) + 500 * B1

If Solver uses 1, then the returned value is 500.
If Solver uses 0, then the returned value is A1.
Solver is set up to use Binary constraints in this fashion, but is not
really set up to work with Discontinuous functions.

Hope I said this right. It is a little confusing.

--
Dana DeLouis Windows Me & Office XP

"David J. Braden" <no...@ugotta.bekidding.com> wrote in message
news:211220011727063446%no...@ugotta.bekidding.com...