This AppleWorks function script allows a spreadsheet to calculate the slope and intercept of the line that best fits the sample x,y data points, using least squares analysis.
This is one of 40 scripts in the Enhancement Pack for AppleWorks.
Calculates the slope, y intercept and correlation coefficient for a least squares fit line through the supplied x,y values. Use a formula in a spreadsheet cell.Sample
The easiest way to do this is to select the Function... menu command in the Scripts menu. Choose linear Regression from the list, then your choice of:
You choose one of the above depending on whether you want to calculate the slope, intercept or correlation of the line of best fit. This will copy the formula to the clipboard for you, ready to paste.
Then, type an = in a spreadsheet cell, and paste the formula in. Replace the x_range and y_range parameters with a refernce to your x and y data.
To view this sample included with the Enhancement Pack:Example
- Selecting About AppleWorks Scripts in the Scripts menu.
- Click the Document button.
- Click the Script Function Samples link. A spreadsheet should open containing several sample functions.
The example explains how the above sample was constructed.Advanced
For a spreadsheet containing the xy data points:
Create three cells, each with the corresponding Linear Regression MACRO formula (from the Scripts: Universal:Copy Function Script menu):
Select these six cells and select Auto Name in the Name Pop-up menu in the Entry Bar. Click OK to automatically define slope as cell C11 etc.
To use the slope and intercept to show the line of best fit, you need to add some calculated points next to the data points. Enter any x values, probably spanning your xy data points. For the y calculated cell E5, use the formula =intercept+slope*D5 and fill down.
To graph the data:
To neaten the chart:
- Select the data and calculated points (ie from cell B5 through E9).
- Select Make Chart in the Options menu.
- Select X-Y Line and OK.
- In the legend, click on series 1. In the tool (AppleWorks 5) or Accents (AppleWorks 6) palette, set the line width to None.
- Double-click on series 2. Disable the Symbol checkbox.
- Click the Labels button. Disable the Legend checkbox. Click OK.
Because the functions are dynamically calculated, you can change the xy data points to see the slope and intercept and associated chart update automatically.Requirements
The Linear Regression function can be placed in any cell or calculated field.Download
This script works with AppleWorks 6 and AppleWorks 5.
Only registered users are permitted to use the supplied files after 14 days or edit them.
This script is included in the Enhancement Pack for AppleWorks, not supplied separately.Scripting
To make the script editable, drag and drop it onto the Show/Hide in Scripts Menu applet in the Scripts Support folder, then double click to open in your script editor. After closing the script, drop it on the applet again to hide it in the AppleWorks menu.Please email us any queries about this page.
The script caches the results of all calculations when first called, recalculating only if the input xy values are changed. This speeds calculation of the second and third parameters.
Some of the major instructions are:
repeat with pointN from 1 to n
set x to item pointN in xList as number
set sumX to sumX + x
set sumXY to sumXY + x * y
set sumX2 to sumX2 + x ^ 2
set denominator to n * sumX2 - sumX ^ 2
set interceptVal to (sumY * sumX2 - sumX * sumXY) / denominator