Implementing Excel Solver in pure VBA for Least Square, curve fit, and Cx/Crr calculation

Googling around I found something about the Linest() function in excel, but I don't understand if it's the function used by the solver or not.

Solver and LINEST() use completely different algorithms.

I'm not quite sure how to answer your question, mostly because there are entire textbooks written about numerical methods, and I don't feel like I could or should write a new textbook in a single internet forum post. My suggestions here are probably going to leave you with more questions than answers, but hopefully enough direction to let you research what you need to know.

As a mostly self-taught programmer, one of the most useful books I came across was an old (70's era?) text by Peter Stark, I think it was, explaining numerical methods programming. All of his examples were in FORTRAN, but the programming language didn't matter. He explained the math behind the programming fairly well, so that the code was easy to understand, and, therefore, easy to adapt to other programming languages. If you are truly serious about learning this kind of programming, I would probably suggest that you find a similar "intro to numerical methods" text.

To understand the algorithms behind Solver, I would probably suggest you start with the basic idea of "root-finding" or "finding the zeros" of an equation. http://en.wikipedia.org/wiki/Root-finding_algorithm
I don't fully understand the default GRG non-linear, though my impression is that it is basically a robust extension of the basic but popular Newton-Raphson method. This tutorial (http://www.cs.utah.edu/~zachary/isp/...ot/Newton.html) seems to be a decent introduction to the NR algorithm, showing how it works, and also when it will not work. In terms of understanding how Solver solves problems (at least the default algorithm), I would suggest you start by first understanding how to program your own root finding problems.

Linest uses a different set of numerical methods -- specifically the technique known generically as "linear least-squares regression." The best first place to start to really understand these methods is probably an intro to stats text where there should be a chapter or two discussing least squares regression. From there, you should learn the true definition of "linear" (hint: it will come from linear algebra and it means a lot more than "straight lines". For example, all polynomials are "linear" functions). Along the way, you should learn enough matrix algebra to express your linear regression problem as a single, simple (if matrix algebra is ever simple) matrix equation. The algorithms for linear least squares are then the algorithms needed to solve this matrix equation (specifically, how to take the inverse of a matrix).

I, too, am having trouble backing out the math used in the specific spreadsheet in question. I so often find that I first need to understand the math before I can program the solution. The key to understanding this particular problem will be to understand the sequence of calculations from C23 to E37. I would suggest that you start in C23, write out the formulas used in getting to C24 in an algebraic notation. An understanding of the physics behind the problem would probably also be useful.

The attachment failed to attach or something, because it gives me an error

I figured out how to simulate least square calculation without both Solver and linest: for a 2nd grade polynomial, it's "just" a system of three equations with 3 unknown terms:
http://mathworld.wolfram.com/LeastSq...olynomial.html

This is how LINEST() does regressions. The programming challenge is porgramming an appropriate algorithm for inverting the matrix. In the past, Excel has been rightly criticized for choosing a less stable, less accurate algorithm for the matrix inversion (it seems that the preferred algorithm is Qr decomposition). For a small matrix like this one, it probably does not matter as much what algorithm you employ for the matrix inversion.

It looks like you have this reduced to a linear least squares regression, which means it is basically a matrix inversion problem. At this point, it seems like the problem is "simply" implementing a matrix inversion algorithm in VBA, javascript, or whatever programming language you are currently working in. I have been using Excel's built in matrix inverters (LINEST and MINVERSE) that it has been a long time since I coded my own matrix inverter, so I don't remember many of the details.

But I still miss how the resulting polynomial relates to Cd and Crr :-(

Seems like it should be algebra at this point. I haven't got the time to do the algebra for you, but it should look something like this:

your regression appears to be V=A+B*t+C*t^2.

From the physics formulas you give post 3, it appears that you have an expression like V(i)=V(i-1)*a*t where a is a function of Cd and Crr. Further manipulation/substitution should allow you to put that expression into a form like: V(i)=f(V0,Cd,Crr) + g(V0,Cd,Crr)*t + h(V0,Cd,Crr) *t^2

Then, comparing the regression equation with this derived formula, it should be obvious that A=f(V0,Cd,Crr); B=g(V0,Cd,Crr); and C=h(V0,Cd,Crr). Solve this system of equations to get V0, Cd, and Crr from the regression coefficients.

My daughters get tired of their math teachers insisting that they "show their work." Oftentimes, though, it is difficult to really see where someone may have gone wrong unless they do show their work. Not knowing what you have done, it is hard to suggest where you have gone wrong.

When I check my daughter's algebra homework for errors, I often do "reverse math" as you call it to check their answers. Usually, when the "reverse math" fails, it is because they made a mistake in the algebra (the rest of the time, I make a mistake in my algebra). So, my first suggestion would be to double check your algebra. Look for the usual culprits -- not carrying a negative sign through, misplacing a parenthesis, putting a multiplication sign where you should have put a minus sign (he says, sheepishly), and so on.

I recognize your frustration, because I find that a lot of solving a problem is understanding the math behind the problem. When I understand the math, it is much easier to figure out how to program the solution.

It seems that, up to this point, you have been working out a linear least squares solution that assumes that V can be expressed as some 2nd order polynomial in t (V=A+Bt+Ct^2) and that you can then back out Cv and Crr from A, B, and C. Are we certain that this is the correct approach?

Going back to the formulas in post 3, here's what I came up with (I have to assume that this is not "new" to you, since you say you have studied this for some years):

V(i)=V(i-1)-a*t (from the spreadsheet, it looks like this should be (t2-t1), which is a constant for the given data)
V(i)-V(i-1)=-a*[t(i)-t(i-1)]
dV/dt=-a <-- This is a differential equation. Like a lot of physics problems, I suspect that this problem, at least theoretically, starts out as a differential equation
a=F/m and F=AV^2+B so a=1/m(AV^2+B) where I have lumped all of the "constants" (including Cv and Crr) into A and B.
dV/dt=-1/m(Av^2+B)

At this point, do you have enough calculus under your belt to understand this and see how to solve this diff eq? After separating variables, it looks to me like you will need an integral table you trust to get the antiderivatives then go from there. I don't expect that this equation resolves to a quadratic function like we have kind of assumed up to this point.

Another thought: dV/dt is a simple quadratic function. Would you get the correct results if you calculated dV/dt and v^2, then did a linear regression on that? Of course, any time you take a numeric derivative like that, there is a loss of accuracy, perhaps you would lose too much accuracy.

Going back to the approach the original spreadsheet uses. It appears they are using a variation of Euler's method, assuming V0 is exact, then computing V(i) from V(i-1) as you've described above. The spreadsheet programmer probably felt that it was too difficult (or impossible) to express this as a "linear least squares" regression, so he/she opted for a non-linear least squares using Solver. In my first response, I kind of suggested that there are two possible approaches to this kind of problem. A linear least squares approach, which you have explored and I am beginning to wonder if it is the wrong approach. The other was something based on Newton's method for finding the minimum. in the objective function. At this point, I might suggest going back to the beginning and exploring a NR type approach to this problem.

I wonder if not recording data down to 0 m/s affects negatively the precision of results, as it looks close-to-zero data are the harder to simulate.

Probably. From the graph, it shows that in extrapolating down to v=0, you are extrapolating to t~140 -- about double t at the last data point. Depending on the data and the model, this could be a sizable extrapolation. As an experimentalist, I might also point out that the data near V=0 might be the hardest to measure.

I don't know that there are any "magical" techniques for dealing with this sort of thing. You might try putting different, more reasonable values for Cd into the model, regress a Crr based on that value, then look at how that affects your objective function and your measure of "goodness of fit" (r^2 or rmsd or whatever you are using). This kind of technique can be used to estimate an "uncertainty" in the regression parameter. You might find that lowering Cd by 20% has a minimal impact on the objective function and "goodness of fit", which would suggest that your regressed Cd cannot be more accurate than 20%. I seem to recall seeing this kind of technique formalized into a "Monte Carlo" type method that randomly assigns values to the desired parameters, measures the "goodness of fit" then repeats the process to show what range of values for the parameters give a "reasonable" fit to the data.

At this point, I'm not sure there are any magical methods for dealing with "I have my best fit curve but the parameters are a little off" kind of problem. Sometimes, additional or more accurate data are needed. Sometimes you can resolve by estimating the error in the parameter to see if a more reasonable value for the parameter still fits the measured data adequately. I might simply say, "welcome to the sometimes very nuanced world of data regression and analysis."