Get the latest news and alerts!

# How to Do a Weighted Regression in Excel

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.

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