#### 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?action=getinfo&userid=28780

```
 0
11/13/2005 3:10:32 PM
excel.misc 78881 articles. 5 followers.

5 Replies
744 Views

Similar Articles

[PageSpeed] 17

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

```
 0
11/13/2005 3:31:10 PM
```#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?action=getinfo&userid=28780

```
 0
11/13/2005 7:10:41 PM
```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

--
nakedbambo
-----------------------------------------------------------------------
nakedbamboo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2878

```
 0
11/13/2005 8:17:33 PM
```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" <nakedbamboo.1yfy2y_1131894901.5253@excelforum-nospam.com>
wrote in message
news:nakedbamboo.1yfy2y_1131894901.5253@excelforum-nospam.com...
>
> 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?action=getinfo&userid=28780
>

```
 0
11/14/2005 1:09:00 AM
```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.

```
 0
11/14/2005 1:26:44 AM

Similar Artilces:

excel export available
I have a query that I wish to export the results to Excel. However, the Excel button on the Export Data tab is grayed out? It used to work. Any ideas of what makes the Excel Export unavailable? Thanks, Mike ...

Creating an Excel table from Access
Hi, I have an Access database which I use to log downtime for systems. I have a requirement to produce a monthly report based on this data, however, this needs to be exported to excel in a specific format. Down the left side of the report need to go the names of the services, with the days (numerically like: 1, 2, 3 .. 29, 30 etc) across the top. Then I need to count the number of times each service was down on a give day, and insert that information into the necessary cell - so if intranet services had been down twice on 16th March, for example, there would be a 2 in column 16, whi...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

2000 File Export Issue
Running Outlook 2000 (9.0.0.2711) and trying to export a date range to Excel. I'm following the instructions, but all I get are the column headings -- no data. What am I doing wrong? ...

Excel working in with Outlook
I have Office 2007 so obviously Outlook 2007 and Excel 2007. I have made up a newbie's Excel spreadsheet for my business. One thing to open that has my entire financial year's data in it and my car logbook. One thing I am going to add to it is parts. I want to know when parts need to be re-ordered so will set a level where I want Excel to show up basically something like a "reorder now" warning. However, I am often in a hurry, record the event and don't look at the outcome until I have some time, late at night. At that time, reordering becomes a nightmare. Is ther...

Sync Issues Outlook 2003 / Exchange 2000
We've got a number of remote users who up until recently have bene working fine with synchronising mailboxes at home, then suddenly all users end up doing a full sync taking ages instead of a normal. Is there a setting on the serve that could of changed? Paul ...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Add a Word Document as a Tab in an Excel Document
I work on several documents that require both worksheets and written reports - being able to add a word document as a new tab in Excel would be a neat way of integrating the two into one file for storing/printing/emailing. Maybe... http://www.pcmag.com/article2/0,4149,5224,00.asp "Office Binder: Gone but Not Really" PC Magazine article, January 29, 2002 by M. David Stone on using Binder in Office XP Jim Cone San Francisco, USA "GoDamN" <GoDamN@discussions.microsoft.com> wrote in message news:5F814119-2FA1-4BA6-92EB-C524C8C3820C@microsoft.com... > I work on s...

Querying Excel data without another program
Hello, I'm an intermediate user of Excel, but I have lots of experience using Access. If you can give me clues on how to handle the information below using only Excel, I'd be grateful! I have a spreadsheet in Excel 97 in which there's a Worksheet named, "Datasource" with a column "B" containing repetitive data. I'd like to create a new worksheet in the same Excel file which shows a single instance of each item used in Column "B". If I were writing the query in SQL, I'd say "SELECT DISTINCT [Column B] FROM [MySpreadsheet]![Datasource...

Excel is not counting
merry x'mas In excel 2003, i entered a number in a column and dragged down the rows to count the consecutive numbers automatically, but it was just copying the same number instead counting. How to change so as to count? -- Life isa journey not a destination Do the same while holding the CTRL Pressed. Micky "Sherees" wrote: > merry x'mas > In excel 2003, i entered a number in a column and dragged down the rows to > count the consecutive numbers automatically, but it was just copying the same > number instead counting. How to change so as to...

Excel 2003 - VBA
Hi, What is a simple way to check to see if a sheet of a given name is present in a workbook? Thanks, Craig Dim Sht as object set sht = nothing on error resume next set sht = activeworkbook.sheets("somesheetnamehere") on error goto 0 if sht is nothing then msgbox "nope" else msgbox "yep" end if Craig Brandt wrote: > > Hi, > > What is a simple way to check to see if a sheet of a given name is present > in a workbook? > > Thanks, > Craig -- Dave Peterson Dave: Thanks for the quick response. Works like a champ, Craig &q...

Random Sampling in Microsoft Excell 2002
Hi, I'm trying to create a random sample using the Data Analysis tool in Excell. The problem I'm having is that I can't figure out how to make a non-repeating sample. I want all the observations in the sample to be unique. How do I do this without manually deleting the repeat entries on the output worksheet? next to the entries, put in the formula =Rand() then drag fill down the column. Sort the data and this column with this column as the key. Then take the top "n" items. Regards, Tom Ogilvy "EJ Ford" <edseljoe@earthlink.net> wrote in mess...

Import contacts from Excel
I have numerous contact lists with various categories and wondered if i can import them into an email distribution list so that i do not need to hand type each email? thanks "angie" <angie@discussions.microsoft.com> wrote in message news:64042F86-62E0-4D42-B9CE-49C134DB4B7B@microsoft.com... >I have numerous contact lists with various categories and wondered if i can > import them into an email distribution list so that i do not need to hand > type each email? You can't import into a DL. Instead import into your Contacts and assign categories to ...

How to lock a line put on a graph in excell
I am trying to attach a line to a graph with months on the X axis. When I add additional months I want the line to stay in between the original months, however, it always moves as the graph expands. Drawing objects cannot be linked to specific values of chart axes, unless you incorporate them as series in the chart. Try the techniques here: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Dennis" <Dennis@discussions.microsoft.com> wrote in message news:336...

No Smart Tag help: just a blank "MS Excel Help" window
When I invoke "help on this error" on a Smart Tag drop-down, a blank "MS Excel Help" window appears with no content. The general help pane that appears via <F1> etc. is fine - it's just the Smart Tag help option that doesn't show anything, just a blank window. Any suggestions? Win XP Home SP2 Excel 2003 (11.6355.6360) SP1 ...

how do why update my Excel microsoft office
how do why update my Excel microsoft office If you have office 5, you probably would want to update -- Don Guillett SalesAid Software donaldb@281.com "sam" <sam@discussions.microsoft.com> wrote in message news:2D951DBF-43DE-4C65-8CAC-B4E73DC572FD@microsoft.com... > how do why update my Excel microsoft office ...

Email Help: Sending Outlook email from Excel VBA
Hi All, How do I select a specific property in outlook while sending email from excel? There is an option we can set in outlook outgoing emails call "Voting buttons" in that feature there is a custom option that we can select called "Have replies sent to" its a checkbox. I want it checked when i send an email from excel. Hope i made it clear. Thanks in advance If sending through the Outlook Object Model, use the MailItem.VotingOptions property and MailItem.ReplyRecipients collection. -- Dmitry Streblechenko (MVP) http://www.dimastr.com/ OutlookSp...

running excel in background
I'd like to run a program that writes values from one cell to another with a VBA program that is triggered by the clock. I've completed this part but, since I use active cells, you can't run another workbook without the clock activating the cell in the active worksheet. It needs to run in the background all day long. - ideas? will it run in the background if I don't activate cells? Try launching another instance of Excel. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Don" <thedonallen@yahoo.com&g...

How do I export email addresses from excel to outlook?
I am trying to do a mail merge using email via outlook. I have 200+ addresses and I'd like to know how to import the addresses into the contacts section of outlook to do the merge from there. I've tried the help part of out look but it comes up saying that the excel file has no named ranges and that I should use excel to name the range of data to be imported. Any help much appreciated. Thanks Mark In outlook select file/import export/ import from another program or file/ and then follow the instructions from the wizard. Why do you want to do the mailmerge from Outlook? You can...

excel opens up looking like it is in ms dos
how can I get my excell to stop opening up looking like it is in MS-DOS mode. I get an error message that wants to send the info to microsoft, when it hit yes send or no don't send it then shuts down. Hi what exact error message do you get and does this happen all the time for all files? -- Regards Frank Kabel Frankfurt, Germany osborne4 wrote: > how can I get my excell to stop opening up looking like it is in > MS-DOS mode. I get an error message that wants to send the info to > microsoft, when it hit yes send or no don't send it then shuts down. Sounds like you...