How to Do a Weighted Regression in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Excel can calculate the weighted regression values of your data set.
Image Credit: John Foxx/Stockbyte/Getty Images

Calculate the weighted regression in your Microsoft Excel spreadsheet by standardizing your values and employing the Regression tool. You can standardize your data set by taking the natural log of your y-values with the LN() function. After preparing your data set, you use the Regression tool to calculate a confidence interval, chart residuals and generate a regression summary output. The Regression tool is a component of the Data Analysis ToolPak. Access these built-in tools from the ribbon at the top of the screen.

Advertisement

Step 1

Open your Excel spreadsheet with the appropriate data set. Organize your data to list the x-values in column A and the y-values in column B. Label your columns to distinguish between the raw data and weighted data by placing descriptions into the first row of the data set.

Video of the Day

Step 2

Calculate the weighted amount of your data set by taking the natural log of your y-values. Enter "=LN(B2)" without the quotation marks into column C and then copy and paste the formula into all cells in that column. Label the column "Weighted Y" to help you identify the data.

Advertisement

Step 3

Click the "Data" tab on the ribbon at the top of the page and select the "Data Analysis" button. Choose the "Regression" tool from the list of options. Click the "Input Y Range" text box and select the data from column C. Click the "Input X Range" and select the data from column A.

Advertisement

Advertisement

Step 4

Select a confidence level to perform a confidence test, input the output range and plot residuals from the Residuals area of the window. Click "OK" to complete the regression wizard. Save your Excel spreadsheet by clicking the "File" tab and choosing "Save" from the context menu.

Video of the Day

Advertisement

Advertisement

references

Report an Issue

screenshot of the current page

Screenshot loading...