Estimating multiple coefficients of an equation

I would probably start by using the LINEST() function: https://support.office.com/en-us/art...a-fa7abf772b6d Some would try to avoid the helper columns, but I would add three columns to compute 1/X, Y/X, and Z/X, then use the range with those helper columns in the LINEST() function. Be sure to read the full help file to see how to get LINEST() to output the standard errors and other statistics. Depending on what other statistics you need, you may need to review the list of statistical functions to see how other statistics are calculated: https://support.office.com/en-us/art...1-63f26a86c0eb

Note that the LINEST() function will not allow you to constrain C2. When you have a data set and the linear regression algorithm returns C2<0, you will need to decide how best to handle that scenario (set C2=0 and run the linear regression on the other 3 constants??).

Hello MrShorty
Thanks very much for the clear explanation. I will look into the Lincet closely to run my estimation.

Anyway it's a pity that you cannot constrain your coefficient that way. Wish there was such option.

Cheers.

LINEST() is only the starting place I would use. How often do you expect the regular linear regression algorithm to return a negative C2? What does a negative C2 mean? (in a few of the regressions I commonly do, a wrong sign constant is a good indication that there is something wrong with the input data -- not the regression algorithm.) One can use non-linear regression methods (using Solver when done in Excel), but I find those more difficult to program, so I prefer to start with the well known linear regression method before venturing into non-linear regression methods.

I guess what I am saying is, if you trouble with the linear regression algorithm returning C2<0, there are ways to fix it, but I would wait to see how badly "broken" the linear regression algorithm is before exploring those.

Yea, you're right. I need C2 to be positive because I'm going to put its value in a log expression which measures the the initial intrinsic growth of a technology.

I'm going to read the Lincet function for now to run the estimation for the database and also other database that I have. In case I was getting negative values, I'd let you know to give me more invaluable tips if you dont mind.

Cheers,

Hello MrShorty,

I read the function and the instruction, and accordingly I ran some estimations. It was really great though in some cases I am getting negative values for the constant that I need to be positive. Wondering if you could share your tricks or hacks with me.

Also, I need to calculate the probability of the results. I looked it up and apparently there is a function named FTEST which can do the probability, but the instruction only explains it for an array of definite numbers to make a comparison. While the LINEST has brought me the estimated values for the constants, I dont know how to calculate and construct an array for each constant so I could feed it to the FTEST function and calculate their corresponding probabilities.

I have been also able to calculate the R squared, ssreg, df, and ssresid.

Cheers,

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

I will point out that this is an Excel forum, not a statistics forum. It seems that a lot of these questions might be more about generic statistics (independent of programming language or analysis application) than about Excel specifically. We are generally very good at the Excel part of these questions, but not always as good at the statistics side of these questions. Where your questions are more about statistics, you might seek out resources that are better at statistics.

Re: C2<0 -- As I indicated in my first response, it really depends on what C2<0 means. You have indicated that, downstream of the regression, you need to take the log of C2 (which is impossible using real numbers), but you have not explained what C2<0 means for the regression itself.
As I noted, when I encounter this, it is usually because there is something wrong with the raw data, which usually means looking more carefully at the raw data to find what is wrong with the raw data. Hopefully, it is just a matter of finding the (few?) outliers that are pulling the regression in the wrong direction and discarding them.
Don't neglect the possibility of floating point error. You have not indicated how far below the returned C2's are. If they are sufficiently close to 0, that might indicate a floating point issue, and you can simply round the result to 0.
If the unconstrained best fit is returning a C2<0, I would expect that the constrained best fit will be when C2=0 (or as close to 0 as your problem statement will allow). If this applies, then I would simply fix the value of C2 at this minimum value, then use LINEST() to get C1, C3, and C4.
When all else fails, there is always Solver -- set up the spreadsheet to compute your desired regression objective function, then have Solver optimize the objective function by changing C1, C2, C3, and C4, subject to the constraint that C2>0 (this might be the way to test several scenarios and see if C2 is always 0).
That what I've got for "constrained regression" scenarios.

It's not clear to me what "probability of the results" means to you, so I'm not sure I can make any suggestions. Statistics is full of many different probabilities, and almost all statistical hypothesis testing is build on those probabilities. Saying that you need to calculate a probability does not tell us what probability you need to calculate and for what purpose. You mention the FTEST() function, which suggests that maybe you are expecting to perform some kind of F test? I put "F test in linear regression" into my favorite search engine and found:
Wikipedia, with a brief look at how F tests are used to test the quality of a regression: https://en.wikipedia.org/wiki/F-test...ssion_problems
A university class note page: http://facweb.cs.depaul.edu/sjost/cs...f-test-reg.htm
From a Minitab blogger: http://blog.minitab.com/blog/adventu...ssion-analysis
Another statistics blogger: http://statisticsbyjim.com/regressio...ce-regression/
A site I like who focuses on statistical analysis using Excel (following up on the university notes that talked about testing whether independent variables are significant): http://www.real-statistics.com/multi...ression-model/
If none of those applications of the F test are what you are referring to when you talk about "calculating probability", then I recommend that you explain further what probability you need to calculate.

By the "probability of the results" I mean p-value for each estimated constant, for example you choose alpha=0.05 as an indicator to accept the value of the estimate constants if their corresponding p-value is smaller than the alpha (saying it is significant).

I'm still not sure I understand exactly what p-value you are trying to calculate. In other places:
stattrek article https://stattrek.com/regression/slop...px?tutorial=ap
Wikipedia article https://en.wikipedia.org/wiki/Simple...ity_assumption
I see mention of a t-test to test the significance of the coefficient (is the coefficient significantly different from 0). If this is what you are talking about, then you will use the t distribution functions to compute the t score p-value along the way to performing the t test. If you're referring to some other p-value, we need your help to know what p-value you need to calculate. If it helps, here's a previous post where I talked about how to use the standard error from the LINEST() function to get the t score https://www.excelforum.com/excel-gen...ml#post4350280

The tricky part might be getting the standard error for each coefficient. I know how to get the standard error from the LINEST() function for the unconstrained regression. I can read Wikipedia's article or other statistics tutorial that shows how to calculate the standard error for the unconstrained regression. The tricky part might be determining if the same formulas to calculate the standard error for the unconstrained case apply to the constrained regression that you are performing now. I am not enough of a statistician to know. It should be a "simple" matter of researching statistics resources to determine how one should calculate the standard error, then enter those formulas into the spreadsheet.

I would note that most of this is statistics, not necessarily Excel. We are pretty good with Excel programming, but I don't think we have a statistics expert among our regular contributors here. If you can help us understand the statistics, we should be able to help you program that into Excel, but we may not be able to help you understand the statistics.

If you look below the table in the stattrek article, they explain that the p-value they are calculating is taken from their t distribution calculator. Excel has built in T.DIST, T.TEST, and similar functions related to the t distribution for computing these values in Excel. Excel/Solver can provide these quantities -- if you program them into the spreadsheet.

Help files for functions that start with T: https://support.office.com/en-us/art...90033e188#bm20

This might be where it is advantageous to revisit one of my recommendations -- fix C2 at the desired minimum value and perform the regression using LINEST() with C2 fixed. For those data sets where LINEST() returns an invalid C2, does the Solver algorithm consistently set C2 to its minimum value? If it does, then you should be able to fix C2 at its minimum and use LINEST() to get the values for the other constants along with their standard errors. It should be straightforward to get t and p from the LINEST() output.

If you need something more elaborately pre-programmed for these, you might look into applications like R or Minitab, which are more robust statistical packages that may have utilities for this kind of regression all pre-programmed. I've never used them, so I don't know what they can do out of the box, and what you would need to program into them.