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

In summary: Some common problems with regression are:-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.-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.-Support for non-parametric/robust methods in general, and regression in particular, seems to be non-existent.-Many plots that are commonly used aren't available (boxplot, dotplot, two of the simplest) without using an external Add-In.
  • #1
engineer23
75
0
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?
 
Physics news on Phys.org
  • #2
engineer23 said:
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
 
Last edited by a moderator:
  • #3


Note that the aforementioned worksheet deals with the model

[tex]
y = b e^{mx}
[/tex]

and not to

[tex]
y = C x^n
[/tex]
 
  • #4


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


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

[tex]
y = b e^{mx}
[/tex]

reduces to

[tex]
\ln y = \ln b + mx
[/tex]

The other model reduces to

[tex]
\ln y = \ln C + n \ln x
[/tex]

With the obvious changes in notation the first is a simple linear regression model, in which the original [tex] x [/tex] values can be used. In the second both [tex] y [/tex] and [tex] x [/tex] 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.
 
  • #6


statdad said:
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
 
  • #7


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 [tex] R^2 [/tex], 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.
 
  • #8


statdad said:
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".
 
Last edited:
  • #9


ssd said:
?? 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.
 
  • #10


statdad said:
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.
 

What is a regression analysis?

Regression analysis is a statistical method used to quantify the relationship between two variables, typically referred to as the independent variable (x) and the dependent variable (y). It is used to determine how changes in the independent variable affect the dependent variable.

What is the purpose of using regression analysis in Excel?

The purpose of using regression analysis in Excel is to find the best-fit line that describes the relationship between two data sets. This can help predict future values of the dependent variable based on changes in the independent variable.

How do I perform a regression analysis in Excel?

To perform a regression analysis in Excel, you will need to have two data sets for the independent and dependent variables. Then, you can use the built-in "Data Analysis" tool to generate a regression equation and plot the data on a scatter plot.

What is the significance of the regression coefficient (C) and exponent (n)?

The regression coefficient (C) represents the slope of the regression line, indicating how much the dependent variable changes for every unit change in the independent variable. The exponent (n) is the power to which the independent variable is raised, indicating the type of relationship between the variables (e.g. linear, quadratic, etc.).

How do I interpret the results of a regression analysis in Excel?

The results of a regression analysis in Excel will include the regression equation, the coefficient of determination (R-squared), and the p-value. The regression equation can be used to predict future values of the dependent variable. The R-squared value indicates the strength of the relationship between the variables, with higher values indicating a better fit. The p-value is used to determine the significance of the relationship, with values less than 0.05 indicating a significant relationship.

Similar threads

  • Set Theory, Logic, Probability, Statistics
Replies
23
Views
2K
  • Set Theory, Logic, Probability, Statistics
Replies
4
Views
1K
  • Set Theory, Logic, Probability, Statistics
Replies
8
Views
1K
  • Set Theory, Logic, Probability, Statistics
Replies
4
Views
781
  • Set Theory, Logic, Probability, Statistics
Replies
13
Views
2K
  • Set Theory, Logic, Probability, Statistics
Replies
30
Views
2K
  • Set Theory, Logic, Probability, Statistics
Replies
2
Views
391
  • Set Theory, Logic, Probability, Statistics
2
Replies
64
Views
3K
  • Set Theory, Logic, Probability, Statistics
Replies
3
Views
699
  • Set Theory, Logic, Probability, Statistics
Replies
2
Views
253
Back
Top