Excel has a nice linear regression tool as part of the included Analysis ToolPak add-in. It is quick and easy to use even for the layperson.

To make sure you have this add-in enabled:

Let’s say we have the following sample data of gas and crude oil prices.

And we want to analyze the relationship crude oil pricing has to gas pricing. To manually run the regression tool:

This would be the output:

There are many articles out there on how to interpret and use the results of linear regression analysis. You may want to take note of the R Square value, which indicates how closely your values relate.

Now let’s say you wanted to run a lot of linear regressions or wanted to re-run them every time there is a new data point. And you are lazy and don’t want to do that all manually every time. Time to automate. The following C# code generates the the same spreadsheet as shown above in the manual example.

It is hard to find resources on how to run add-in tools via Excel interop. Note the following two statements.

This command is used to activate the Analysis ToolPak add-in:

This statement executes the regression analysis:

The parameters are the equivalent of the same manual settings shown earlier:

Let me know if this is helpful.

To make sure you have this add-in enabled:

- Go to File > Options > Add-ins
- Select Manage “Excel Add-ins”
- Click on “Go”
- Make sure “Analysis ToolPak” is checked

Let’s say we have the following sample data of gas and crude oil prices.

And we want to analyze the relationship crude oil pricing has to gas pricing. To manually run the regression tool:

- Open the “Data” ribbon menu
- Cick “Data Analysis”
- Select “Regression” in the list
- Click “Ok”

This would be the output:

There are many articles out there on how to interpret and use the results of linear regression analysis. You may want to take note of the R Square value, which indicates how closely your values relate.

Now let’s say you wanted to run a lot of linear regressions or wanted to re-run them every time there is a new data point. And you are lazy and don’t want to do that all manually every time. Time to automate. The following C# code generates the the same spreadsheet as shown above in the manual example.

_{}^{}It is hard to find resources on how to run add-in tools via Excel interop. Note the following two statements.

This command is used to activate the Analysis ToolPak add-in:

This statement executes the regression analysis:

The parameters are the equivalent of the same manual settings shown earlier:

Let me know if this is helpful.

## Comments

## Post a Comment