In one type of nonlinear regression, the function that summarizes the relationship between the variables is called power regression because in the function x is raised to a power. This regression model is appropriate when the data rises or falls rapidly and then levels out. Discover how to use Excel to perform this analysis and expand your ability to make predictions.
- [Voiceover] Now let's turn our attention to power curve regression. This one has a huge array of applications. In a wide variety of fields, we often see a high initial value on the Y variable, then a drop-off, sometimes sudden, and then a leveling out as the X variable increases. So it's important to be familiar with this kind of regression analysis. The population of United States cities is an example. A large proportion of our cities have small populations. As population size increases, the proportion of cities drops off dramatically.
The general equation for power regression is, "Y equals A times X raised to the B power". It's called a power curve because X is raised to a power. Like any regression analysis, part of the objective is to find the values of A and B that best fit the data. Industry has known about power curves since the 1930s. The aircraft industry led the way in promoting the industrial learning curve, a power curve model of how airplane production time decreases dramatically as aircraft workers become more experienced in manufacturing a particular airplane.
Here's some data to bear this out. I took the data from a graph in a 1957 study done for the U.S. Air Force. It's the production data for the B-29 bomber. Column A shows the cumulative number of planes, and Column B shows the corresponding person hours per pound of airplane, to get the plane produced. The scatter plot shows a classic power curve relationship. Our square shows that the power curve is an almost perfect fit to the data. Columns C and D will hold the natural logarithms of these values.
Why do we need the natural logarithms? Let me explain. We're going to work with the regression tool to do the complete analysis. And the regression tool needs a linear relationship to work with. The power regression equation, as I pointed out before, is that Y equals A times X raised to the B power. If you take the natural logarithm of both sides of that equation, you wind up with the natural logarithm of Y equals A plus B times the natural logarithm of X. And so now we have a linear relationship for the regression tool to work with.
It's the relationship between the natural log of Y and the natural log of X. So in C2, type "equals, L, N, A, 2", drag over, and autofill both columns. Select the data analysis tool pack, select the regression tool, for the Input Y Range, select D1 through D17. For the Input X Range, it's C1 through C17.
Check the box next to Labels and click Ok. Open up the columns, and the regression statistics table and the ANOVA table complete the picture of just how well the model fits the data. Especially the huge F ratio in E12, and the really tiny P value in F12. The regression coefficients are in B17 and B18. Excel returns the natural logarithm of the intercept. So to calculate the true value, we have to take the exponential of the value in B17.
So in B21, type "equals, E, X, P" for B17, and there it is. You learned how to analyze a learning curve. Companies use this kind of analysis to make important managerial decisions about costs and resources. Power regression shows up in a wide variety of areas, from business to biology. And now you're set up to analyze that wide variety.
He explains how to organize and present data and how to draw conclusions using Excel's functions, charts, and 3D maps and the Solver and Analysis ToolPak add-ons. Learn to calculate mean, variance, standard deviation, and correlation; visualize sampling distributions; and test differences with analysis of variance (ANOVA). Then find out how to use linear, multiple, and nonlinear regression testing to analyze relationships between variables and make predictions. Joseph also shows how to perform advanced correlations, variable frequency testing, and simulations.
By the end of this course, you should have the foundational knowledge you need to take other statistics-related courses and perform basic analysis in the workplace.
- Using Excel's statistical functions and 3D charts
- Visualizing sampling distributions
- Performing comparisons with ANOVA
- Performing two-way analysis with ANOVA
- Analyzing linear regression
- Performing multiple regression and nonlinear regression analysis
- Making advanced correlations
- Testing variable frequencies
- Running simulations