#### Interesting Solver problem (Solver encounters an error)

Here's an interesting problem, I wonder if anyone has any thoughts o
this.  Recognize that my real problem is very complex (severa
intermediate calculation including some iterative steps), but th
problem I'm having seems similar (conceptually anyway) to this simpl
problem.

Given a data set:

x,y
10,3.9
8,3.2
7,2.8
6,2.2
5,1.4
4.5,0.8
4,0.01
3.8,-0.4
3.6,-1
3.5,-1.4
3.4,-1.8
3.3,-2.4
3.2,-3.2
3.1,-4.6
3.05,-6

One could look at the data and say, "that looks like the curve y=ln(x)
but with a different asymptote other than the y-axis and possibly
scaling factor."  So we choose a function of the form y=b*ln(x-a) t
correlate the data.  So we add a third column =r1c5*ln(rc1-r1c6) wher
r1c5 and r1c6 will hold our parameters b and a, then pu
=sumxmy2(r2c3:r16c3,r2c2:r16c2) at the bottom of column 3.  Then set u
solver to minimize r18c3 by changing r1c5:r1c6.

Now we pull initial guess for b and a out of a hat, and Solver run
into an error.  Because on the 2nd or 3rd iteration, solver is going t
try a value for a >3.05 and the LN function will return an error.  W
try to improve the initial guesses, but, in this case, we would need t
be pretty close.  I could get b=1.9, a=2.9 to converge, but b=1.8,a=2.
wouldn't.

We iterate on each parameter individually, back and forth between b an
a, but this becomes tedious, especially if it takes several tries t
manually locate an initial a that will not generate an error.

For this simple model, one can add a constraint that a<=3.049999 an
thus avoid the error.  However, in my real problem, the value for
that generates an error isn't obvious.  Also, it appears that th
optimum a value is essentially largest value that won't generate a
error.  So I end up manually bisecting the interval between the lowes
value that generates an error and the highest value that doesn't unti
I obtain the desired accuracy in a.  Not the most efficient way to d
it, especially when I want to optimize b at the same time.

I don't know how much you'll be able to help, but it seems like a
interesting problem.  I don't readily see an option that will tel
Solver to use those error values as part of the optimization algorithm
even though the error values do contain useable information in thi
case.  All this exercise might do is show the importance of choosin
appropriate initial guesses for Solver, or that Solver isn't suitabl
for solving all of the world's problems.

Any thoughts?

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2218

 0
12/21/2005 6:51:15 PM
excel.misc 78881 articles. 5 followers.

3 Replies
526 Views

Similar Articles

[PageSpeed] 47

Hi,

Yes, when I analyzed your data starting with initial guess values of 'a'=2.8
and 'b'=1.8, parameter 'a' becomes 3.08 in the second iteration step (which
is slightly greater than the smallest value in the x-range, i.e., 3.05), and
the Solver stops and returns an error (due to the attempted calculation of
the logarithm of a negative value).

However, I could get around this problem with a slight modification as
follows;

I placed your x- and y- data in A2:A16, and B2:B16 respectively.  I created
a dummy parameter (let's call it 'a prime') in D2, and the parameters 'a' and
'b' in E2 and F2 respectively.
In E2 (corresponding to 'a') I entered the formula,
=MIN(A2:A16)*0.999999-D2.  I placed initial guess values, 1 for aprime (i.e.,
D2), and 1 for b (in F2).
In C2, I entered the formula =\$F\$2*LN(A2-\$E\$2), and autofilled the formula
down to C16; and placed the SSR in G2 with the formula
=SUMXMY2(B2:B16,C2:C16).  The SSR was about 94.2 at this point.

I invoked the Solver, to minimize the SSR (G2), by changing 'aprime' and 'b'
(i.e., \$D\$2, \$F\$2), and under "Options" checked "Assume Non-Negative".
Solver didn't have any problem and returned the optimized values,
'aprime'=0.050096784 (corresponding to 'a'=2.999900166) and b=1.99943891, and
the minimized SSR was 0.005916092.

Now comes the interesting part: I tried the guess values, 'aprime' = -100
and 'b' = 100.  This corresponds to 'a' = slightly less than 103.05, and it
returns error (#NUM) to the entire range C2:C16, vis-a-vis to SSR.
Surprisingly, Solver handled even this situation and returned the same
optimized values as before (it needed a little more than the default 100
iterations; of course I had turned off "Show Iteration Results" for this)!

I think, the bottom-line is, it is safer to use a dummy parameter to
incorporate a constraint to the actual parameter, and optimize the dummy
parameter (with the added constraint to disallow negative values for
'aprime', i.e., 'a' will never become greater than any x-value).

Regards,
B. R. Ramachandran

"MrShorty" wrote:

>
> Here's an interesting problem, I wonder if anyone has any thoughts on
> this.  Recognize that my real problem is very complex (several
> intermediate calculation including some iterative steps), but the
> problem I'm having seems similar (conceptually anyway) to this simple
> problem.
>
> Given a data set:
>
> x,y
> 10,3.9
> 8,3.2
> 7,2.8
> 6,2.2
> 5,1.4
> 4.5,0.8
> 4,0.01
> 3.8,-0.4
> 3.6,-1
> 3.5,-1.4
> 3.4,-1.8
> 3.3,-2.4
> 3.2,-3.2
> 3.1,-4.6
> 3.05,-6
>
> One could look at the data and say, "that looks like the curve y=ln(x),
> but with a different asymptote other than the y-axis and possibly a
> scaling factor."  So we choose a function of the form y=b*ln(x-a) to
> correlate the data.  So we add a third column =r1c5*ln(rc1-r1c6) where
> r1c5 and r1c6 will hold our parameters b and a, then put
> =sumxmy2(r2c3:r16c3,r2c2:r16c2) at the bottom of column 3.  Then set up
> solver to minimize r18c3 by changing r1c5:r1c6.
>
> Now we pull initial guess for b and a out of a hat, and Solver runs
> into an error.  Because on the 2nd or 3rd iteration, solver is going to
> try a value for a >3.05 and the LN function will return an error.  We
> try to improve the initial guesses, but, in this case, we would need to
> be pretty close.  I could get b=1.9, a=2.9 to converge, but b=1.8,a=2.8
> wouldn't.
>
> We iterate on each parameter individually, back and forth between b and
> a, but this becomes tedious, especially if it takes several tries to
> manually locate an initial a that will not generate an error.
>
> For this simple model, one can add a constraint that a<=3.049999 and
> thus avoid the error.  However, in my real problem, the value for a
> that generates an error isn't obvious.  Also, it appears that the
> optimum a value is essentially largest value that won't generate an
> error.  So I end up manually bisecting the interval between the lowest
> value that generates an error and the highest value that doesn't until
> I obtain the desired accuracy in a.  Not the most efficient way to do
> it, especially when I want to optimize b at the same time.
>
> I don't know how much you'll be able to help, but it seems like an
> interesting problem.  I don't readily see an option that will tell
> Solver to use those error values as part of the optimization algorithm,
> even though the error values do contain useable information in this
> case.  All this exercise might do is show the importance of choosing
> appropriate initial guesses for Solver, or that Solver isn't suitable
> for solving all of the world's problems.
>
> Any thoughts??
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22181
>
>
 0
12/22/2005 5:59:02 AM
Thanks,

That's an interesting idea, to build the conditions into the
spreadsheet model rather than into the solver model.  One thing I
non-negative" option, because it applies to both parameters.  A
different data set may require b to be less than 0, but your particular
solver model wouldn't find it.  You would have to either add a
bprime=-b, or alter the formulas in column 3.  Neither of which is a
bad solution, but I would probably rather have a single constraint
aprime>=0 rather than the dual constraint aprime>=0 and b>=0.  Of
course, at that point, there's not a lot of difference between a single
constraint a<=3.049999 and aprime>=0.  On the other hand, we have to
remember that we are building a Solver model to solve the problem at
hand, and solve future problems when we come to them.

--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22181

 0
12/22/2005 6:51:59 PM
Hi,

Thanks for your feedback.  You are absolutely right.  I too didn't like the
"Assume non-negative" option since its constrains the other parameter as
well.   But then for the particular problem in question that constraint was
necessary.  As you correctly put it, I think, we have to build a Solver model
for a problem at hand, instead of trying to build a model that would be
global.

Another idea occurred to me for analyzing your data.  Even though I
generally prefer to analyze data as-is and not use linear (or other)
transformations as for as possible, here I think a linear transformation
works well.

In column C, I calculated y values using the formula =\$F\$2*LN(A2-\$E\$2),
where E2 and F2 contain 'a' and 'b' respectively.  Here, I didn't use the
dummy parameter 'aprime'.  For calculating the SSR however, I used the
following formula [corresponding to the linear transform of y = b*ln(x-a) ,
i.e., (x-a) = exp(y/b)].

=SUMXMY2(A22:A36-\$E\$19,EXP(B22:B36/\$F\$19))

I started with the guess values a=1 and b=1 (SSR = 2108).  When Solver is
invoked (with no constraints), the following result was obtained:

a=2.996056603,	b=2.001589552, 	SSR=0.00621783

Of course, one has to expect slight differences in the values of the
parameters obtained from nonlinear and linear analyses of real-life data (due
to different error-distributions in the raw and transformed data, which is
not taken into account in these optimizations).

Regards,
B. R. Ramachandran

"MrShorty" wrote:

>
> Thanks,
>
> That's an interesting idea, to build the conditions into the
> spreadsheet model rather than into the solver model.  One thing I
> didn't like about your approach for this problem is the "assume
> non-negative" option, because it applies to both parameters.  A
> different data set may require b to be less than 0, but your particular
> solver model wouldn't find it.  You would have to either add a
> bprime=-b, or alter the formulas in column 3.  Neither of which is a
> bad solution, but I would probably rather have a single constraint
> aprime>=0 rather than the dual constraint aprime>=0 and b>=0.  Of
> course, at that point, there's not a lot of difference between a single
> constraint a<=3.049999 and aprime>=0.  On the other hand, we have to
> remember that we are building a Solver model to solve the problem at
> hand, and solve future problems when we come to them.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22181
>
>
 0
12/22/2005 10:52:02 PM

Similar Artilces:

Problem with pop3 code
Hi guys, I know this isn't my code but there is a problem that is driving me nuts. The code at http://www.codeproject.com/internet/win32_pop3.asp is one of the best POP implementations I have seen so far. The problem I am having is that when using a fast connection (ADSL or faster, especially a localhost server) data loss occurs when doing 2 commands in semi-quick succession, especially if the second is a 'top' command. I don't get this problem on a 56k connection. I have been pulling hair out at this one, my best assumption is it is something to do with a buffer getti...

ListBox MultiSelection Problem
Hello, I am building a form which will play with all selected lines in ListBox. I have ListBox filled like that: Option1 Option2 Option3 and I select by mouse Option1 and Option2. The problem is as follows: How to get these selected elements (I mean strings like 'Option1', 'Option3', not indexes). My code: For i = 0 To MyList.ListCount - 1 If MyList.Selected(i) Then MyString = MyString & MyList.??? // what to put here??? cause ItemData return index, not string value End If Next Regards, Jan MyString = MyString & MyList.column...

Possible Exchange 2003 Problem
Hello all , I am running Windows 2003 with Exchange server 2003. This morning I added an additional machine the ability to relay through my exchange server through exchange server manage / servers / <my server> / Protocols / SMTP / Default Virtual SMTP server. Since then I cannot receive email from my isp, but I can send email to external sources, and I can send/receive internally, but I just cannot receive email from external sources. I was wondering if adding another machine to the relay allow list would have caused this? Or is it probably through my ISP. I have tried contact...

MX record problem exchange2003
Hi I have an Exchange 2003 server running on a windows 2003 server. When I try to send mail to a domain with more than one MX record and it cannot reach the first server it doesn't try any other MXservers. It just try to send mail to the first MX server for tree days. Is there any configuration on the exchange server for this? RO wrote: > Hi > > > > I have an Exchange 2003 server running on a windows 2003 server. > > When I try to send mail to a domain with more than one MX record and > it cannot reach the first server it doesn't try any other MXservers...

extrange problems after moving mailboxes.
Hi, We are working on an exchange 2003 sp2 organisation, with w2k3 sp2. Reccently we needed to move mailboxes from some servers to another. Now some users need to click send/receive button to receive their emails. Occassionaly some receive a message after sending email out of our organisation, saying they have no permission to send email to such recipients. This last problem occur at the same time to everybody and some minutes later works fine. Any idea? thank you very much Comments inline below. -- Ed Crowley MVP "There are seldom good technological solutions ...

CRM4 problem with custom workflow activity
I have created a custom workflow activity (CWA) based on an example I found on the net - this has been compiled and installed and works fine. I then created another CWA and have revised it several times - reloading each time. However when I run a workflow which uses this second CWA, it goes into Waiting state and indicates an error has occurred. I have even resorted to having no parameters and nothing but the return statement in the code. Still with the same result. Is there something I've missed here? Should I just start again? Should I unload the CWA between updates? (I already ...

Font problem and Office X
I am using OS X version 10.3.3 and Office version 10.1.5. Here is my problem. When I try to use the font drop down in the formatting tool bar, the formatting palette and the font drop down in the menu bar the font maps to the wrong font. When I click on say tahoma, the selected font switches to verdena not tahoma. This does not happen when I use the format drop down from the menu bar and select fonts. Any suggestions to fix this problem. I have reinstalled Office and repaired permissions with no change in this problem. Thanks In article <e9f25299.0404201508.4e19f73@posting.google.co...

Problems when saving worksheet into tab-delimited text file
I save a spreadsheet into a tab-delimited text file for later import into an Access database. One column contains text strings in which "comma values" can be found. Once save to the text file, those records that have commas in the Excel cells now contain double quotes (".....") at the beginning and end of the text string. As a result, during the data import into Access (and later comparison), the records are considered new and add records to tables. During testing procedures, I realized that replacing the comma with a semicolon would not add the double quotes when savin...

Problems saving Excel & MS Office 2000 files.
We are seeing issues when saving files to user directory on a windows 2000 server. The users are all using Windows 2000 pro systems & Office 2000 Pro. The original files will disappear & a new file with a random 8 character name will appear in their directory. The file will have no security permissions associated with it. It is not happening all of the time, just random. The servers are all Dell 1500sc or 1600sc w/1.2 gig of ram and 60 gig of hd space on the shared directory. The servers are also acting as a print server as well as running Norton Anti-Virus Corp Edition...

how to get rid of errors? #2
Thanks for the input, but actually I was more concerned about gettin rid of all error messages when the file loads up. It looks like yo gave me a cool utility, but I am not too concerned about fixing th links. Is there a way to ignore all messages that pop up when i load my exce sheet? regardless of what it is? Thank -- Jon ----------------------------------------------------------------------- Jonx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1317 View this thread: http://www.excelforum.com/showthread.php?threadid=26148 Think deleting all the "pha...

If you have been getting regular Send / Recieve error messages...eg. "can't connect to server" etc. in XP...and have been bashing your head against a wall tinkering with NAV and other possible fixes, try this simple solution... add a period (dot) to the end of your mail server names in the account settings for each mail account you have. For example: change mail.servername.com to mail.servername.com. I read this on another forum, tried it, and I have not had an error message in the last 5-6 Send/Receive cycles (touch wood), when I was getting them each time prior to th...

outloor error when first open outlook
when I open Outlook I get this error... Ther ewas a problem reading one or more of your reminders. some reminders may not appear. cannot locate reccurance information for this appointment. please HELP! *CS lpgtweety@yahoo.com ...

I have Excel spreadsheets which I link certain sections to various Publisher files. When I open a few (not all) of the Publisher files and the update links begins, I receive an error: "A document with the name ??????? (Excel spreadsheet name is shown) is already open. You can not open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that is currently open, or rename one of the documents." This error occurs even though there is only one occurence of the Excel document open. I can cl...

Frx: linked data worksheet summing error
We use linked data worksheets for some of our reporting. The column format displays both current period and YTD. The YTD column is not calculating the correct amounts from the worksheet. For example, if we were in period 12, it would sum the YTD column as follows. period 1: add 12x period 2: add 11x period 3: add 10x period 4: add 9x ....and so forth period 12: add 1x Obviously, this gives us a huge error in the ytd column. Anyone have an idea why this is occurring? We are using the /cpo format for the linked worksheet. Thanks ...

Update 873262 Problem
The latest performance enhancement for the Outlook Sales client is 873262. The install instructions say to install the update on the client . . . while on the downloads page for this update, it only shows a server file. So which one is right? -- Brandon Smith IT Director Presentations Direct - Specialized Office Equipment & Supplies http://www.presentationsdirect.com I should have included the links: The download page is: http://www.microsoft.com/downloads/details.aspx?familyid=133a85d0-e3e3-4356-908d-9fb6e4fa3501&displaylang=en The KB page (w/ instructions) is: http://supp...

Error Delegating Administrator Rights & Removing SID
I have recently taken over for a NetAdmin who I believe deleted the account of the previous NetAdmin rather than disabling it. Consequently ... When attempting to use the Exchange Administration delegation wizard to add or remove a group or a user to the Organization level, I receive the following error: Failed to grant permission from company\Exchange_administrators on this object:/dc=net/dc=company/cn=configuration Subsequently clicking the [NEXT] button yields: The delegation wizard could not grant/change permissions for company\exchange_administrators I would like to remov...

Outlook 2000 calander problems
I can't open any of the scheduled events on my calendar, the outlook = 2000 crashes and I receive the following error Description: Faulting application outlook.exe, version 9.0.0.6604, faulting module = omint.dll, version 5.5.3201.0, fault address 0x0001a36f. When I generate new user account the problem correct itself, but I don't = want to make a new account and loose all my formatting. Please help, Jack. ...

M07 Import Handler Problem
I download directly from the bank's website. Money Import Handler doesn't open QIF file until I reboot and restart Money. Huh? Running Vista Ultimate 64 Koka In microsoft.public.money, Koka wrote: >I download directly from the bank's website. Money Import Handler doesn't >open QIF file until I reboot and restart Money. Huh? Running Vista >Ultimate 64 I don't know if this will fix it, but let's make sure some things are covered: Be sure that your Money and backup files are moved to a spot that you will have full privileges to, such as Documents/...

Outlook 2003 Focus Problem
I've noticed a few other people here with similar problems and I'm wondering if anyone's come up with a solution yet: It used to be that when I awoke Outlook 2003 from its nap in my system tray and typed Alt+3 or clicked the "Contacts" button, the focus used to shift immediately to the right-hand pane whereupon I could begin typing the name of the contact I was seeking and OL would jump to the entry. That no longer happens, however. Now when I maximize OL and select "Contacts", neither left nor right pane is focused. I have to hit F6 once or click with my mous...

I just upgraded to Access 2007. I opened my Front End database and the Switchboard is blank. I seem to have lost all my custom button links. The database is in 2000-2003 format. Any idea? -- FL Make sure that the Switchboard Items table is there. It's probably in the BE so make sure that the link to it works. I once had a problem with the Switchboard and putting that table in the FE fixed it. However that was a long time before 2007. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "FL" wrote...

Big Error
An update query updated a large number of field values in the wrong field. It added the value YED-14- to the left of the existing values. Is there a way to remove the YED-14- without doing it manually or requesting IT to restore the database from backups, and return the values to there original values. This will really save us embarrasement. Thank you ever-so-much. On Sat, 8 Mar 2008 09:26:00 -0800, PA <PA@discussions.microsoft.com> wrote: >An update query updated a large number of field values in the wrong field. >It added the value YED-14- to the left of the existing value...

SendMessage Form Refresh Problem
Hi all, I am currently working on the following scenario: 1) User check on the checkbox in the form 2) My program perform a SendMessage to click on the form OK button 3) A message box then pop up. The above resulted in the checkbox not having the check display. Anyone has any idea? Regards cleohm Sounds like you are running code when the control gets clicked. Are you allowing default processing to occur? -- Jonathan Wood SoftCircuits Programming http://www.softcircuits.com "cleohm" <cleohm@discussions.microsoft.com> wrote in message news:34378C29-6334-4134-B0C7-CB...

how do I stop error message
each time I open any Ms doc I receive an error box saying office application version does not match Does this article help? Error message when you start a 2007 Office program on a computer that is running Outlook 2007 with Business Contact Manager: "Office application version does not match" http://support.microsoft.com/kb/935569/ -- Mary Sauer http://msauer.mvps.org/ "Christine" <Christine@discussions.microsoft.com> wrote in message news:9D6F5DEB-26B1-4801-B160-BA8B9A6B2312@microsoft.com... > each time I open any Ms doc I receive an error box saying office...

"Correct Version" error b.s.
I have the Teacher and Student version of 2004. When I install, the Microsoft Component Plugin info shows V11.2.2 (the applications all show 11.2.0--see end of msg). Autoupdating downloads the update 11.2.5 and I get the "can't find correct version..." you all know the bull. This is incredibly frustrating that security on so vulnerable a product is so impossible to achieve! I've followed the instructions on the mvp site as advised for removing and cleaning prior to a clean reinstall, and didn't make one iota of difference. I followed each step religiously, emptying the...

Item Search Error
Hi I'm running MS POS, when I type / scan an item in the serch box provided at the top, it keeps searching by Customer name and returns " the customer was not found". I really want it to search by Item #. Is there something I am doing wrong or is it a bug in the system? Thanks In MS POS (not RMS) a scan and a search will behave a bit differently. If your scanner isn't setup properly you could get some mixed results. First, about scanners... Your scanner needs to be configured as a device for the register. Unlike RMS even a keyboard wedge device needs to be setu...