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:

MACRO("Frequency",2,"calculate", scores, lower, upper)

You paste this into a spreadsheet cell after an “=” (equals) sign, then replace the parameters:
    1. “scores” with a range of cells
    2. “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.
    3. “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:
    1. Selecting “About AppleWorks Scripts” in the Scripts menu.
    2. Click the “Document” button.
    3. Click the “Function” entry in the table of contents.
    4. 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.

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:
    1. Select these cells under the “average” and “frequency” headings (ie from cell D29 through E33).
    2. Select “Make Chart” in the “Options” menu.
    3. 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.

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.

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.

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.

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
end repeat

Please email us any queries about this page.