Browse » Home
Tuesday, April 23, 2013
How to Use Excel 2007 to Estimate Linear Functions
1. Open a new or existing Microsoft Excel spreadsheet. If you start with a blank spreadsheet, enter your x-values into column A and y-values into column B. Each pair of numbers creates a data point on a chart, so they need to correspond to each other.
2. Label your columns with descriptions of your data, such as 'Month' and 'Hours.' In this example, if you worked 160 hours in the month of June, those two values create one data point on your chart. Highlight your data set and select the 'Insert' tab on the Ribbon at the top of the page.
3. Click the 'Scatter' drop-down box in the 'Charts' area, then select the 'Scatter With Only Markers' option. This option creates a plot from your data points and allows you to estimate the linear function. Select the 'Layout' tab of the Ribbon and click the 'Trendline' button from the 'Analysis' area.
4. Click 'More Trendline Options' from the drop-down options. Select 'Linear' from the 'Trend/Regression Type' to estimate a linear function. Select the 'Display Equation on Chart' box to view the linear function equation on the chart.