Excel Solver Issue
 

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.


--
nakedbamboo
------------------------------------------------------------------------
nakedbamboo's Profile: http://www.excelforum.com/member.php...o&userid=28780
View this thread: http://www.excelforum.com/showthread...hreadid=484665

Excel Solver Issue
 

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 wrote:

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.

Excel Solver Issue
 

#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
------------------------------------------------------------------------
nakedbamboo's Profile: http://www.excelforum.com/member.php...o&userid=28780
View this thread: http://www.excelforum.com/showthread...hreadid=484665

Excel Solver Issue
 

Is there anyway to set the initial conditions for the excel solver in
VBA? For example, instead of starting with a blank cell, can I tell it
to use the data from the previous cell to begin its calculations? If it
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.


--
nakedbamboo
------------------------------------------------------------------------
nakedbamboo's Profile: http://www.excelforum.com/member.php...o&userid=28780
View this thread: http://www.excelforum.com/showthread...hreadid=484665

Excel Solver Issue
 

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"
wrote in message
...

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)

------------------------------------------------------------------------
nakedbamboo's Profile:
http://www.excelforum.com/member.php...o&userid=28780
View this thread: http://www.excelforum.com/showthread...hreadid=484665

Excel Solver Issue
 

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

nakedbamboo wrote:

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