Linear Regression With Microsoft Excel
Search:

Home | Accounting

 
 

Linear Regression With Microsoft Excel

By: Steve Nelson

You use Excel's linear regression functions to find a linear equation that best describes a data set.
Excel uses the sum of least squares method to find the straight line of best fit. People often try to predict future amounts by assuming linear growth and extending the line forward in time. For example, if you have a series of sales data for 9 months and want to predict the sales in the 10th month, you can use Excel’s linear regression functions to find the slope and y-intercept (the point on the y-axis where the line crosses) of the line that best fits the data.

Some Background Info on Linear Regression

To use the linear regression functions, it helps to remember the equation for a line:

y=mx+b

where y is the dependent variable, m the slope, x the independent variable, and b the y-intercept. If there are multiple ranges of x values, the equation looks like this:

y=m1 x1+m2x2+.mnxn+b

NOTE To visualize and experiment with linear regression, visit the interactive web page at http://www.math.csusb.edu/faculty/stanton/m262/regress/regress.html. Click the graph area to add data points (x,y) to the graph. The applet draws the straight line that best fits the points you add, adjusting the line for the new data points you add.

Using the FORECAST Function

The FORECAST function predicts a future y-value for the x-value you specify using existing

x and y values. The FORECAST function uses the following syntax:

=FORECAST(x, known ys, known xs)

where x is the x-value for which you want to predict a y-value.

Using the INTERCEPT Function

If you have existing x and y values, Excel can find the straight line that best fits the data and then calculate the point at which the line intersects the y-axis, in other words, the value of b in the “y=mx+b” equation. The y-intercept is useful when you want to know the value of the dependent variable when the independent variable equals 0.

NOTE: The INTERCEPT function returns the same value as the FORECAST function if you enter 0 for x in the FORECAST function.

The INTERCEPT function uses the following syntax:

=INTERCEPT (known ys, known xs)

Using the LINEST Function

The LINEST function returns the value of m and b given at least one set of known ys and known xs. The LINEST function has the following syntax:

=LINEST (known ys, known xs, constant, statistics)

where known ys is the array of y values you already know, known xs is the array of x values you may already know. If you leave out the known xs, they are assumed to be 1, 2, 3,…n. If constant is set to FALSE, b is assumed to be 0. If statistics is set to TRUE, the LINEST function also returns the standard error for each data point.

NOTE: If the known ys are in a single column or row, then Excel considers each column of known xs to be a separate variable.

NOTE: The array known xs can include multiple sets of variables. If you use only one set, then known ys and known xs can be ranges of any shape, as long as they have equal dimensions. If you use more than one variable, then the known ys array must be either a single column or a single row. If you don’t enter known xs, Excel assumes this array is the same size as the known ys array.

Using the SLOPE Function

Use the SLOPE function to find the slope (m) of the linear regression line from the known x and known y data sets. The slope is the change in y over the change in x for any two points on the line. The SLOPE function in Excel uses the following syntax:

=SLOPE (known ys, known xs)

A positive (upwards) slope means that the independent variable (such as the number of salespeople) has a positive effect on a dependent variable (such as sales). A negative (downwards) slope means that the independent variable has a negative effect on the dependent variable. The steeper the slope, the more effect the independent variable has on the dependent variable.

Using the STEYX Function

Use the STEYX function to find the standard error of the predicted y-value for each individual x in the regression. The STEYX function uses the following syntax:

=STEYX (known ys, known xs)

Using the TREND Function

Use the TREND function to find values along a linear trend. Specify an array of new xs and the TREND function uses the method of least squares to fit a straight line to the known x and y data sets and return the y-values along the line for the new array. If constant is set to FALSE, the “b” in the y=mx+b equation is set to zero. The TREND function uses the following syntax:

=TREND (known ys, known xs, new xs, constant)

Article Source: http://articlenexus.com

Prolific computer book author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel. Nelson also writes and edits downloadable do-it-yourself small business incorporation kits a South Carolina limited liability company or a South Dakota limited liability company.

Please Rate this Article

 

Not yet Rated

Click the XML Icon Above to Receive Accounting Articles Via RSS!
Additional Articles From - Home | Accounting
Sign Up for a free account
free pay per click
STOP Paying for Your Ads! FREE Pay Per Click!
$8,132.26 Per Month on Craigslist
craigslist blackhat system

CraigsList Blackhat System
Everything you need for your website and home business.
Adsense Site Builder
Adsense Analyzer Software
Clip Art
Domain Names
Header Graphics
Make Money Online
Photo Art
Resell Products & Free Stuff
SEO Software
Web Hosting $4.95/mo
Website Templates
Web Site Promotion Tools

AdvancedWebsiteTools.com

Submit Articles
Member Login
Top Authors
Most Popular Articles
Submission Guidelines
Ezine Notifications
Article RSS Feeds
(Your 160x160 ad here)
Only $50 a month-click here
Link Exchange
Blog by WordPress
Privacy Policy
Terms of Service
About Us
Contact Us
Banner Advertisements
Business Reporter
Have you Victimized by a Business or the Government? File your consumer complaints or business complaints at BusinessReporter.net!
Structured Settlement?
Free information to help you learn more about structured settlements, your rights, and how to select the best company for you.
Affordable Discount Dental Plans
Choose from over 30 top-quality national and regional discount dental plans. Save 10% to 60% on most dental procedures
Classic Autos
Replica Kit Cars
Staffing Resources
Online Recruiting
Links
Bad Credit Repair
Hair Loss Remedies
Steiff Bears, Antique Bears
Model Railroading | Model Trains
McCain Sucks
mccain for president
Free Diabetic Supplies
Antique Radios | Ham Radios

отдых в Севастополеplugin wordpressрыбалкатанцевальный лагерь

Powered by Article Dashboard