Excel Solver Issue

[nakedbamboo]

I am trying to use the excel solver and am running into some issues. The
solver is giving me results that I know are wrong and I cannot get it to
give me the correct results. I am using it to calculate a linearity
which is based off of the STDEV and AVERAGE of a group of numbers. The
formula I am using is:

=TRUNC(SQRT(SUM(($B$2-AVERAGE($B$2:$B$6))^2,($B$3-AVERAGE($B$2:$B$6))^2,($B$4-AVERAGE($B$2:$B$6))^2,($B$5-AVERAGE($B$2:$B$6))^2,($B$6-AVERAGE($B$2:$B$6))^2)/4),0)/AVERAGE($B$2:$B$6)

This is basically the longhand version of STDEV/AVERAGE which I have
found gives the actual number I am looking for. For some reason, if I
take out the TRUNC or just use STDEV/AVERAGE, the number is a little
off. What I am using solver for, is to minimize the linearity by
changing the last number in the series.

For example, if all the numbers are the same, solver should give me
that number again. for a linearity of 0. It will not do this. If I try,
for example, all 1500s, solver will give 1639.

Any suggestions would be appreciated.

 

[Jerry W. Lewis]

Your calculations are numerically identical to the much simpler
=TRUNC(SQRT(DEVSQ($B2:$B6)/4),0)/AVERAGE($B2:$B6)
unless some cells in B2:B6 are not numeric.

Under the same conditions in Excel 2003, it is also numerically identical to
=TRUNC(STDEV($B2:$B6),0)/AVERAGE($B2:$B6)

Under the same conditions in earlier versions, there should only be an
appreciable difference from
=TRUNC(STDEV($B2:$B6),0)/AVERAGE($B2:$B6)
if your CV (RSD) is <<0.01%

What is COUNT($B2:$B6)? What are the values in $B2:$B6?

Jerry

 

[nakedbamboo]

#s My Formula STDEV/AVERAGE TRUNC(STDEV)/AVERAGE

1449 NA
1422 NA
1231 8.63% 8.69% 8.63%
1521 8.80% 8.80% 8.75%
1362 7.73% 7.80% 7.73%
1450 7.04% 7.10% 7.04%


This is an example of what I am using. You can see there is a slight
difference between the three versions. The first column is the exact
same numbers that the company's software returns. It does appear that
the third formula is nearly identicle except for one number, and I
could use this. However, I still run into the issue of having to run
solver twice to get the correct answer. Is there anyway around this as
I am trying to use solver in a VBA macro? Thanks.

 

[nakedbamboo]

Is there anyway to set the initial conditions for the excel solver i
VBA? For example, instead of starting with a blank cell, can I tell i
to use the data from the previous cell to begin its calculations? If i
starts from that data point, it only has to run the solver once.
I suppose I could copy and paste that value into the cell then run it
but is there a more efficient way? Thanks

 

[David Adamson]

My suggestion would be to simplify the objective function.

I have had Solver get confused if I make the objective function to complex.

I would break the formula into components (i.e B2- Average(B2:B6)^2 in a
Cell, etc) and then combine the cells latter.
It has something to do with the fast set up mechanism in solver

"Note that a SUM of decision variables is a linear function where all
the coefficents are 1. To be recognised as a fast set up problem, your
fomrula must consist only of = sum(cells) (with no constants) where every
cell referenced is a decision varaible".
Premium Solver Platform User Guide, p99



"nakedbamboo" <[email protected]>
wrote in message

 

[Jerry W. Lewis]

What are the actual formulas that you are using in the "My Formula"
column? The formula you posted does not adapt to varying numbers of
observations, and the obvious extension does not give 8.80% for the
second numerical result with the posted data.

Are the values in the "#s" column actually integers or just formatted to
not show decimal places that are really there? What is returned by
=IF((B2-INT(B2)),"not integer","integer")
for each of the values in the "#s" column?

Once we have established what this calculation really is, then we can
turn attention to your use of Solver. It is not clear what you want
Solver to do for you. Can you be more specific about that too.

Jerry