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

Michael Rennie mrennie at utm.utoronto.ca
Wed Jul 16 22:54:42 CEST 2003

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

```