This AppleWorks function script allows a spreadsheet to calculate the frequency within data of scores that are within a given range.
This is one of over 40 scripts in the Enhancement Pack for AppleWorks.
The Frequency custom function script should not appear in the Scripts menu. You invoke it by using the MACRO function in a spreadsheet cell or calculated field. The easiest way to do this is to use the Function... menu item in the Scripts menu. It copies the syntax:Sample
MACRO("Frequency",2,"calculate", scores, lower, upper)
You paste this into a spreadsheet cell after an = (equals) sign, then replace the parameters:
- scores with a range of cells
- lower with the minimum of the data range for counting the frequency. Scores that are exactly the lower value will be included in this count.
- upper with the maximum. Scores that are exactly the upper value will not be included in this count.
To view this sample included with the Enhancement Pack:Example
- Selecting About AppleWorks Scripts in the Scripts menu.
- Click the Document button.
- Click the Function entry in the table of contents.
- A few lines down, there is a link to Script Function Samples. Click it. 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 two columns: lower and upper. In the first cell under lower enter the lowest value that any score could have. In the first cell under upper, enter =B29+2 where B29 is the first lower cell and 2 is the size of each score range. In the second lower cell, type = and click on the first upper cell (so that the second range starts where the first range ended) and hit Enter. Fill down (Calculate menu) the lower and upper columns far enough to reach an upper value that is larger than any possible score.
Create a third column average to be the center of each data range. Type =AVERAGE( then select the lower and upper values in the same row, close with ) and hit Enter.
Create a fourth column frequency. Select Function... in the Scripts menu, select Frequency in the list of functions and click OK. In the first cell under frequency, type = then paste in the function syntax: MACRO("Frequency",2,"calculate", scores, lower, upper). In the function, select the scores parameter and select (by dragging) the data cells (see figure above). Select the lower parameter and click on the first cell under lower, similarly for upper, then hit Enter. Fill down the frequency formula.
To graph the data:
- Select these cells under the average and frequency headings (ie from cell D29 through E33).
- Select Make Chart in the Options menu.
- Click General, then enable Use numbers as labels in first column. Click OK.
Because the functions are dynamically calculated, you can change the precedent data points to see the frequencies and associated chart update automatically.Requirements
The first public release (version 1.2) of the Frequency function can be slow when used on a large set of data. A later version optimize speed.
The Frequency function can be placed in any spreadsheet cell.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 main part of the script is this repeat loop:
repeat with score in scores
if score >= lower and score < upper then set scoreFrequency to scoreFrequency + 1