Table of Contents
Logarithmic regression analysis is a statistical method used to model relationships between variables that follow a logarithmic pattern. This type of analysis is commonly used in fields such as economics, finance, and science. Excel offers a user-friendly and efficient way to perform logarithmic regression analysis. To do so, you will need to input your data into an Excel spreadsheet and use the built-in function “LOGEST” to calculate the regression coefficients. You can then plot the results using a scatter plot and use the trendline feature to display the logarithmic curve. This allows you to visually analyze the relationship between the variables and make predictions based on the regression model. Excel also provides tools for evaluating the accuracy and significance of the regression model, such as the R-squared value and p-value. Through these steps, Excel enables you to easily perform logarithmic regression analysis and gain valuable insights from your data.
Logarithmic Regression in Excel (Step-by-Step)
Logarithmic regression is a type of regression used to model situations where growth or decay accelerates rapidly at first and then slows over time.
For example, the following plot demonstrates an example of logarithmic decay:
For this type of situation, the relationship between a predictor variable and a response variable could be modeled well using logarithmic regression.
The equation of a logarithmic regression model takes the following form:
y = a + b*ln(x)
where:
- y: The response variable
- x: The predictor variable
- a, b: The regression coefficients that describe the relationship between x and y
The following step-by-step example shows how to perform logarithmic regression in Excel.
Step 1: Create the Data
First, let’s create some fake data for two variables: x and y:
Step 2: Take the Natural Log of the Predictor Variable
Next, we need to create a new column that represents the natural log of the predictor variable x:
Step 3: Fit the Logarithmic Regression Model
Next, we’ll fit the logarithmic regression model. To do so, click the Data tab along the top ribbon, then click Data Analysis within the Analysis group.
If you don’t see Data Analysis as an option, you need to first .
In the window that pops up, click Regression. In the new window that pops up, fill in the following information:
Once you click OK, the output of the logarithmic regression model will be shown:
The of the model is 828.18 and the corresponding p-value is extremely small (3.70174E-13), which indicates that the model as a whole is useful.
Using the coefficients from the output table, we can see that the fitted logarithmic regression equation is:
y = 63.0686 – 20.1987 * ln(x)
We can use this equation to predict the response variable, y, based on the value of the predictor variable, x. For example, if x = 12, then we would predict that y would be 12.87:
y = 63.0686 – 20.1987 * ln(12) = 12.87
Bonus: Feel free to use this online to automatically compute the logarithmic regression equation for a given predictor and response variable.