The power of a spreadsheet or table derives from the ability to assign formulas to specific cells. These formulas usually involve the application of a mathematical function that uses the values of a range of cells as its input.

Constructing a Formula

In the Numbers '09 Compatibility Suite, formulas are represented as text strings beginning with an equal sign (=), followed by a function name or abbreviation, which is then followed by a range reference, comprised of the colon-delineated names of the starting and ending cells of the targeted range:

For example, here are the standard function operators used in formulas:

Assigning Formulas to Cells

In the Numbers '09 Compatibility Suite, there is no formula property of a cell, which could be assigned the formula as its value. Instead, a cell’s value may be set to a formula by setting it to a text string beginning with an equals sign. The text string will automatically be interpreted as a formula, whose resulting value will appear in the cell.

Function Examples Table

As a demonstration of how to assign formulas to cells, the following script example will create a new table, displaying a column whose last cell is assigned one of the standard functions using the column’s cell values as input:

Here’s the script:

Create Example Functions Table
Open in AppleScript Editor
01		property functionNames : {"SUM", "AVERAGE", "MIN", "MAX", "COUNT", "PRODUCT"}
02		property columnCount : 7
03		property rowCount : 8
04		property useColumnHeaders : true
05		property useRowHeaders : true
06
07		tell application "Numbers"
08		activate
09		if not (exists document 1) then make new document
10		tell document 1
11		tell active sheet
12		set thisTable to ¬
13		make new table with properties ¬
14		{row count:rowCount, column count:columnCount}
15		tell thisTable
16		if useRowHeaders is true then
17		set columnStartIndex to 2
18		else
19		set columnStartIndex to 1
20		end if
21		set x to 1
22		repeat with i from 2 to the columnCount
23		set thisFunction to item x of functionNames
24		tell column i
25		set alignment to center
26		set vertical alignment to center
27		set the value of cell 1 to thisFunction
28		repeat with q from 2 to (rowCount - 1)
29		set the value of cell q to q
30		end repeat
31		set the rangeStart to the name of cell 2
32		set the rangeEnd to the name of cell -2
33		set thisFormula to ¬
34		"=" & thisFunction & "(" & rangeStart & ":" & rangeEnd & ")"
35		set the value of the last cell to thisFormula
36		end tell
37		set x to x + 1
38		end repeat
39		tell the last row
40		set the background color to {0, 0, 0}
41		set the text color to {65535, 65535, 65535}
42		end tell
43		end tell
44		end tell
45		end tell
46		end tell