# [R] Excel can do what R can't?????

Michael Rennie mrennie at utm.utoronto.ca
Thu Jul 17 01:30:59 CEST 2003

```Hi, Reid and Spencer-

I think I've figured something out pretty critical to the problem.

Loking at my 'solver' options, I have a condition added that 'Hgtmod = Hgt'.
Without this conditional statement, I have to run solver 3-4 times before I get
a final solution. MEANING- solver and R, when left to their own devices, suck
equally at finding a solution with similar starting points.  BUT, given a
conditional statement that demands Hgt = Hgtmod, it gives it somewhere to look
withing the given parameter space.

So, the millon dollar quesiton: Is there any way of setting up a contitional
statement like this in 'optim', to specify a solution such that Hgtmod = Hgt?
Or, write it into function f?  The control statements 'fnscale' and 'parscale'
look like candidates, but will only help me if I build Hgtmod into the
optimization statement- can I do that?  How do you specify multiple
parameters?  Or should I specify two functions to optimize- one, fucntion f,
and a second, something like

g<- (Hgt = Hgtmod)^2

Can I do that? If this is all I need to do, I am off to the races, and owe you
both a beer!  I'm going to try some stuff. All is not lost! If you have any
ideas, I'd love to hear them.

THanks again for everything.....

Quoting "Huntsinger, Reid" <reid_huntsinger at merck.com>:

> I don't understand how you can take the loop out of the function and still
> get
> values for the final timepoint. And whether the optimal parameter values
> agree wasn't
> my question. First I'd like to determine whether Hg and W (in your code)
> have the same
> value in R as they do in Excel, for a few possible values of the parameter
> q.

Yes- this was what I was trying to communicate (albeit unsucessfully) in my
last e-mail.  values of 1,2 or 1.5,1.5 or whatever I try, I get the same
answers for f, Wtmod, Hgtmod in Excel as I do in R.  Understand, though, that
in order to determine the agreement for these values, I'm not using an
optimization function of any sort- just plugging in numbers, and seeing what I
get back.  THat's what I meant about taking the 365-day loop out of the
optimization function; even though I am still calculating a value for f, I am
no longer defining it at the top of the loop with 'f<-function(q)'; simply
performing the calculation

f <- 1000000000*((((Wt-Wtmod)^2)/Wt) + (((Hgt-Hgtmod)^2)/Hgt)) ; f.

In order to find out if the value of f is the same in excel and in R given the
same starting parameters of p, ACT. Sorry- I didn't mean to be unclear.

Then,
> if so, whether the surface over which you're minimizing is complicated or
> not. As I said, if it is you can't expect local optimizers to work very
> well
> without good starting values,
> and they really don't explore the whole parameter space--that would be too
> slow for their
> usual applications. Optimization approaches like grid search or simulated
> annealing do try
> to cover the parameter space and may be better suited to your use. I would
> certainly try plotting and grid search just to see what's happening since
> that's clearly possible with
> two parameters.

This is next on the list- I suspect that this is where I may be running into
problems. But, if this is were I am running into problems, then does that mean
that Excel is using a better optimization process than R?  Since they are
recognizing the same parameter space over p, ACT, as evidenced that they offer
identical solutions for the same values of p, ACT, then shouldn't they be
reaching the same solution?

AH! Ureka moment- see top of e-mail........

Thanks again for your suggestions.

Mike

>
> Reid Huntsinger
>
> -----Original Message-----
> From: Spencer Graves [mailto:spencer.graves at PDF.COM]
> Sent: Wednesday, July 16, 2003 5:20 PM
> To: Michael Rennie
> Cc: Huntsinger, Reid; R-Help
> Subject: Re: [R] Excel can do what R can't?????
>
>
> I'm confused:
>
> 	  I've done this type of thing by programming the same objective
> function in R (or S-Plus)  and Excel.  After the answers from my
> objective function in R match the answers in Excel, then I pass that
> objective function to something like "optim", which then finds the same
> answers as "solver" in Excel.  Your latest description makes me wonder
> if the function you pass to "optim" tries to do some of the optimization
> that "optim" is supposed to do. ???
>
> hope this helps.  spencer graves
>
> Michael Rennie wrote:
> > Hi, Reid
> >
> >> Do the values of W and Hg over time for a given q agree between R and
> >> Excel?
> >> Not the optimal value of q, just the trajectories for fixed q (trying
> >> several values for q).
> >
> >
> > If I take the iterative loop out of the function, and ask for values of
> > Hgmod, Hgtmod, and f, then I get EXACTLY what I get out of Excel.  It's
> > the optimization that seems to be the problem.  If I trace the
> > solutions, R isn't even exploring the full parameter space I tell it to
> > look in.  SO, the iterative loop is correct, and doing what it's
> > supposed to, since values of p, ACT match exactly what they do in excel-
> > it's something about how R is examining the possibilities in  the
> > optimization process that is giving me different answers between the two.
> >
> > I dunno- I'm going to tinker with it some more tonight.
> >
> > Mike
> >
> >> Reid
> >>
> >> -----Original Message-----
> >> From: Michael Rennie [mailto:mrennie at utm.utoronto.ca]
> >> Sent: Wednesday, July 16, 2003 2:47 PM
> >> To: Huntsinger, Reid
> >> Subject: RE: [R] Excel can do what R can't?????
> >>
> >>
> >>
> >> Hi, Reid
> >>
> >> At 02:09 PM 7/16/03 -0400, you wrote:
> >> >R is good at automating specific kinds of complex loops, namely those
> >> that
> >> >can be vectorized, or that can be written to draw on otherwise built-in
> >> >facilities. It's usually reasonable for other kinds of loops but not
> >> >spectacularly fast. You can write this part in C, though, quite
> >> easily, and
> >> >R provides very convenient utilities for this.
> >> >
> >> >As for your code: You seem to have a system of equations that relates
> >> W and
> >> >Hg to their one-period-ago values. It might clarify things if you coded
> >> this
> >> >as a function: input time t values and q, output time t + 1 values.
> (You
> >> >wouldn't need any arrays.) Then f would just iterate this function and
> >> >calculate the criterion.
> >>
> >> Wouldn't I still need to loop this function to get it through 365
> >> days?  Is
> >> there a big difference, then, between this and what I've got?
> >>
> >> >Does the trajectory of (W, Hg) for given q in R seem correct? Does it
> >> agree
> >> >with Excel? What does the criterion function look like? You could
> >> plot it
> >> in
> >> >R and perhaps see if the surface is complicated, in which case a simple
> >> grid
> >> >search might work for you.
> >>
> >> When I give R the values that I get in excel for p, ACT, the function
> >> solution is actually more precise than what I get in Excel; the values
> >> for
> >> p, ACT come back identical (then again, they are exactly what I
> >> assigned..)  But, if I leave R on it's own to find the solution, it
> keeps
> >> getting jammed in a particular region.  I've never done any function
> >> plotting in R, but it would help if I could see what kind of surface I
> >> get
> >> for f as a function of p, ACT- this would at least force R to examine
> the
> >> full range of values specified by the upper and lower limits I've set
> >> (which it isn't doing under the 'optim' command).
> >>
> >> Mike
> >>
> >>
> >> >Reid Huntsinger
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >-----Original Message-----
> >> >From: Michael Rennie [mailto:mrennie at utm.utoronto.ca]
> >> >Sent: Wednesday, July 16, 2003 11:18 AM
> >> >To: Spencer Graves
> >> >Cc: R-Help; M.Kondrin
> >> >Subject: Re: [R] Excel can do what R can't?????
> >> >
> >> >
> >> >
> >> >Hi, Spencer
> >> >
> >> >I know I submitted a beastly ammount of code, but I'm not sure how to
> >> >simplify
> >> >it much further, and still sucessfully address the problem that i am
> >> having.
> >> >
> >> >The reason being is that the funciton begins
> >> >
> >> >f<- function (q)
> >> >
> >> >At the top of the iterative loop.  This is what takes q and generates
> >> Wtmod,
> >> >
> >> >Hgtmod at the end of the iterative loop. the assignment to f occurs
> >> at the
> >> >bottom of the iterative loop. So, yes, the call to f is performing an
> >> >immediate
> >> >computation, but based on arguments that are coming out of the
> iterative
> >> >loop
> >> >above it, arguments which depend on q<-(p, ACT).  Maybe this is the
> >> problem;
> >> >
> >> >I've got too much going on between my function defenition and it's
> >> >assignment,
> >> >but I don't know how to get around it.
> >> >
> >> >So, I'm not sure if your example will work- the output from the
> >> iterative
> >> >process is Wtmod, Hgtmod, and I want to minimize the difference between
> >> them
> >> >
> >> >and my observed endpoints (Wt, Hgt).  The numbers I am varying to reach
> >> this
> >> >
> >> >optimization are in the iterative loop (p, ACT), so re-defining these
> >> >outputs
> >> >as x's and getting it to vary these doesn't do me much good unless
> >> they are
> >> >directly linked to the output of the iterative loop above it.
> >> >
> >> >Last, it's not even that I'm getting error messages anymore- I just
> >> can't
> >> >get
> >> >the solution that I get from Excel.  If I try to let R find the
> >> solution,
> >> >and
> >> >give it starting values of c(1,2), it gives me an optimization
> sulution,
> >> but
> >> >an
> >> >extremely poor one.  However, if I give it the answer I got from
> >> excel, it
> >> >comes right back with the same answer and solutions I get from excel.
> >> >
> >> >Using the 'trace' function, I can see that R gets stuck in a specific
> >> region
> >> >of
> >> >parameter space in looking for the optimization and just appears to
> give
> >> up.
> >> >
> >> >Even when it re-set itself, it keeps going back to this region, and
> thus
> >> >doesn't even try a full range of the parameter space I've defined
> >> before it
> >> >stops and gives me the wrong answer.
> >> >
> >> >I can try cleaning up the code and see if I can re-submit it, but
> >> what I am
> >> >trying to program is so parameter heavy that 90% of it is just defining
> >> >these
> >> >at the top of the file.
> >> >
> >> >Thank you for the suggestions,
> >> >
> >> >Mike
> >> >
> >> >
> >> >Quoting Spencer Graves <spencer.graves at PDF.COM>:
> >> >
> >> > > The phrase:
> >> > >
> >> > >     f <- 1000000000*(((((Wt-Wtmod)^2)/Wt) +
> >> (((Hgt-Hgtmod)^2)/Hgt))2) ;
> >> f
> >> > >
> >> > > is an immediate computation, not a function.  If you want a
> function,
> >> > > try something like the following:
> >> > >
> >> > >     f <- function(x){
> >> > >         Wt <- x[1]
> >> > >         Wtmod <- x[2]
> >> > >         Hgt <- x[3]
> >> > >         Hgtmod <- x[4]
> >> > >       1000000000*(((((Wt-Wtmod)^2)/Wt) + (((Hgt-Hgtmod)^2)/Hgt))2)
> >> > >     }
> >> > >
> >> > > OR
> >> > >
> >> > >     f <- function(x, X){
> >> > >         Wt <- X[,1]
> >> > >         Hgt <- X[,2]
> >> > >         Wtmod <- x[1]
> >> > >         Hgtmod <- x[2]
> >> > >       1000000000*(((((Wt-Wtmod)^2)/Wt) + (((Hgt-Hgtmod)^2)/Hgt))2)
> >> > >     }
> >> > >
> >> > > "par" in "optim" is the starting values for "x".  Pass "X" to "f"
> via
> >> > > "..." in the call to "optim".
> >> > >
> >> > >         If you can't make this work, please submit a toy example
> with
> >> the
> >> > > code and error messages.  Please limit your example to 3
> >> observations,
> >> > > preferably whole numbers so someone else can read your question in
> >> > > seconds.  If it is any longer than that, it should be ignored.
> >> > >
> >> > > hope this helps.
> >> > > Spencer Graves
> >> > >
> >> > > M.Kondrin wrote:
> >> > > >  >?optim
> >> > > >
> >> > > > optim(par, fn, gr = NULL,
> >> > > >            method = c("Nelder-Mead", "BFGS", "CG", "L-BFGS-B",
> >> "SANN"),
> >> > > >            lower = -Inf, upper = Inf,
> >> > > >            control = list(), hessian = FALSE, ...)
> >> > > >
> >> > > > .....
> >> > > >       fn: A function to be minimized (or maximized), with first
> >> > > >           argument the vector of parameters over which
> >> minimization is
> >> > > >           to take place. It should return a scalar result.
> >> > > >
> >> > > > Your fn defined as:
> >> > > > f <- 1000000000*(((((Wt-Wtmod)^2)/Wt) +
> >> (((Hgt-Hgtmod)^2)/Hgt))2) ; f
> >> > > > What is its first argument I wonder?
> >> > > > I think you have just an ill-defined R function (although for
> >> Excel it
> >> > > > may be OK - do not know) and optim just chokes on it.
> >> > > >
> >> > > > ______________________________________________
> >> > > > R-help at stat.math.ethz.ch mailing list
> >> > > > https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> >> > >
> >> > >
> >> >
> >> >
> >> >--
> >> >Michael Rennie
> >> >M.Sc. Candidate
> >> >University of Toronto at Mississauga
> >> >3359 Mississauga Rd. N.
> >> >Mississauga ON  L5L 1C6
> >> >Ph: 905-828-5452  Fax: 905-828-3792
> >> >
> >> >______________________________________________
> >> >R-help at stat.math.ethz.ch mailing list
> >> >https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> >> >
> >>
> >---------------------------------------------------------------------------
>
> >>
> >> ---
> >> >Notice: This e-mail message, together with any attachments, contains
> >> >information of Merck & Co., Inc. (Whitehouse Station, New Jersey,
> >> >USA) that may be confidential, proprietary copyrighted and/or legally
> >> >privileged, and is intended solely for the use of the individual or
> >> entity
> >> >named on this message. If you are not the intended recipient, and
> >> >have received this message in error, please immediately return this by
> >> >e-mail and then delete it.
> >>
> >---------------------------------------------------------------------------
>
> >>
> >> ---
> >>
> >> Michael Rennie
> >> M.Sc. Candidate
> >> University of Toronto at Mississauga
> >> 3359 Mississauga Rd. N.
> >> Mississauga, ON  L5L 1C6
> >> Ph: 905-828-5452  Fax: 905-828-3792
> >>
> >>
> >>
> ----------------------------------------------------------------------------
> --
> >>
> >> Notice: This e-mail message, together with any attachments, contains
> >> information of Merck & Co., Inc. (Whitehouse Station, New Jersey,
> >> USA) that may be confidential, proprietary copyrighted and/or legally
> >> privileged, and is intended solely for the use of the individual or
> >> entity
> >> named on this message. If you are not the intended recipient, and
> >> have received this message in error, please immediately return this by
> >> e-mail and then delete it.
> >>
> ----------------------------------------------------------------------------
> --
> >>
> >
> >
> > Michael Rennie
> > M.Sc. Candidate
> > University of Toronto at Mississauga
> > 3359 Mississauga Rd. N.
> > Mississauga, ON  L5L 1C6
> > Ph: 905-828-5452  Fax: 905-828-3792
> >
> > ______________________________________________
> > R-help at stat.math.ethz.ch mailing list
> > https://www.stat.math.ethz.ch/mailman/listinfo/r-help
>
>
>
> ------------------------------------------------------------------------------
> Notice: This e-mail message, together with any attachments, contains
> information of Merck & Co., Inc. (Whitehouse Station, New Jersey,
> USA) that may be confidential, proprietary copyrighted and/or legally
> privileged, and is intended solely for the use of the individual or entity
> named on this message. If you are not the intended recipient, and
> have received this message in error, please immediately return this by
> e-mail and then delete it.
> ------------------------------------------------------------------------------
>

--
Michael Rennie
M.Sc. Candidate
University of Toronto at Mississauga
3359 Mississauga Rd. N.
Mississauga ON  L5L 1C6
Ph: 905-828-5452  Fax: 905-828-3792

```

More information about the R-help mailing list