Two data sets and want to do a regression excel y = C(x^n)

[engineer23]

I have two data sets and want to do a regression so that the equation that relates them is of the form y = C(x^n), where C and n are constants.

How do I do this in Excel?

 

[stewartcs]

I have two data sets and want to do a regression so that the equation that relates them is of the form y = C(x^n), where C and n are constants.

How do I do this in Excel?

Use the regression tool from the data analysis addin. Here is a guide that may help:

http://www.tcc.edu/faculty/webpages/pgordy/Excel/expstat.pdf

CS

 

[statdad]

Note that the aforementioned worksheet deals with the model

$$y = b e^{mx}$$

and not to

$$y = C x^n$$

 

[ssd]

Take log on both sides. Then the model will reduce to the common one.

 

[statdad]

No, it does not. The model given in the reference

$$y = b e^{mx}$$

reduces to

$$\ln y = \ln b + mx$$

The other model reduces to

$$\ln y = \ln C + n \ln x$$

With the obvious changes in notation the first is a simple linear regression model, in which the original $$x$$ values can be used. In the second both $$y$$ and $$x$$ must have their logarithm calculated. Blindly applying the first approach would miss this: that was my point.

Of course, there is the question of why Excel would be used for regression in the first place.

 

[stewartcs]

Of course, there is the question of why Excel would be used for regression in the first place.

Just qurious, why do you not like to use Excel for regression?

CS

 

[statdad]

Excel doesn't have a very good reputation with regards to statistics (I haven't used the most recent version much, but since the problems that existed in previous versions were never addressed, I would be surprised if they were fixed this time).

1. The histogram algorithm is flawed. There is no check to make sure the final "bin" (usually labeled as other) is the width as the others
2. The default rendering of the histogram leaves gaps between the rectangles. Easy to fix, but annoying
3. The regression procedure has many problems. The residual plots are poorly done. The fit mechanism has a tendency to fit multiple-regression models even when there is a high amount of collinearity in the predictors
4. (This may be considered minor by many, but it is an example of something that shouldn't occur) In the situations where you want to force a regression through the origin (i.e., no intercept term) Excel still reports a value for $$R^2$$, which is inappropriate
5. Many plots that are commonly used aren't available (boxplot, dotplot, two of the simplest) without using an external Add-In (which typically means buying the Add-In)
6. Support for non-parametic/robust methods in general, and regression in particular, seems to be non-existent. It would be a great improvement to have (at least) one-variable rank regression, even with Wilcoxon weighting, included

There are other problems, but many are not related to regression. I understand why the temptation to use Excel is so high: immense market penetration - it seems almost every school/workplace has it. It is fantastic for many purposes - I just don't think regression in particular, and statistics in general is one of those purposes.

I hope this hasn't sounded too much like an angry rant - I apologize if it has.

 

[ssd]

No, it does not.

?? Whatever the model (of the two you considered) may be, after taking log on both sides, what remains to convert the the model to the common one is to suitably rename the variable(s). OP mentioned ony one model. So, no question of blindly applying same trick to "both".

 

[statdad]

?? Whatever the model (of the two you considered) may be, after taking log on both sides, what remains to convert the the model to the common one is to suitably rename the variable(s). OP mentioned ony one model. So, no question of blindly applying same trick to "both".

No - exponential regression (the method you pointed out) and polynomial regression (the question posed by the OP) are not identical, and treating them as such causes problems.

 

[ssd]

No - exponential regression (the method you pointed out) and polynomial regression (the question posed by the OP) are not identical, and treating them as such causes problems.

I don't get your point. Can you point out some of such problems? It will be a nice help.