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.
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.
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.
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