Cells, ranges and other sheets

Refer to cells, ranges and other sheets in a Sheet formula, lay out histories and matrices, and use one workbook from another widget.

What the Sheet adds

A Sheet formula is a Calculator formula that starts with =, so every function in Write a formula works in a cell. The Sheet adds references to cells, ranges, other sheets and named cells, and lays out a Series or Matrix result across cells. This page covers the formula side. Sheet covers entry, tabs, formats and the name box.

Cell formulas on this page appear in tables of three columns: Cell, Formula and Shows. They need a workbook, so they cannot run in the Calculator.

What a cell contains

A cell's content is one of four kinds, set when you commit the cell:

Formula: content that starts with =.
Number: content that converts to a finite number, such as 12, -0.5, .5 or 1e3. Spaces around the number are allowed.
Text: any other content, including 1,000 and 5%. Type a percentage as a decimal, such as 0.05.
Empty: no content, or spaces only.

Refer to a cell

A reference is a column of one or two letters, A to AZ, then a row number from 1 to 10,000. Letters in either case are accepted. An address outside the sheet, such as BA1 or A10001, returns #REF!.

A reference returns the value of the cell: a number, text, a Series or a Matrix. A cell that refers to an empty cell gets 0. When the referenced cell shows an error, the formula returns the same error.

Cell	Formula	Shows
A1	`100`	100
A2	`=A1*2`	200
A3	`=A9+1`	1, because A9 is empty
B1	`abc`	abc
B2	`=B1+1`	`#ERROR`, because text is not a number

A $ before the column or the row, as in $A$1, does not change the value. It fixes that part of the reference when you paste the formula.

Other sheets of the workbook

Put the sheet name and ! before the address: Sheet2!A1, or Sheet2!A1:B10 for a range. A sheet name starts with a letter or an underscore and contains only letters, digits and underscores. Quoted sheet names are not supported, so a name cannot contain a space.

A reference to a sheet that does not exist returns #REF!.
When you rename a sheet, Sheet rewrites every formula that refers to it.
When you delete a sheet, every formula that refers to a filled cell of it shows #REF!. A formula that refers only to empty cells of it shows #REF! when it next recalculates.

Named cells

A name defined in the name box works in place of its address. When B1 is named spot, =spot*1.1 is the same as =B1*1.1. A name for a range works as that range. Names ignore case.

A name that does not exist shows #NAME?.
A name whose sheet was deleted shows #REF!.

Ranges

A range is two corners joined by :, such as A1:B10. The corners can be given in either order. A range returns a Series with one point per cell, taken row by row: A1, B1, A2, B2 and so on. Each point's label is its position in the range, counted from 0, row by row. No two points share a label. Two ranges of the same length line up point by point, whatever their start rows. For example, =A1:A3 + B2:B4 adds A1 to B2, A2 to B3 and A3 to B4.

An empty or text cell becomes a gap in the Series and keeps its place.
A cell that shows an error makes the whole formula return that error.
The top cell of a spill gives the value it shows: the first point of a Series, or the top left element of a Matrix. A Matrix that shows its size, such as [3×3], gives a gap. Refer to the top cell alone to use the whole result.

The aggregates sum, mean, average, median, min, max, std, stdevp and count skip gaps. When every point is a gap, sum, mean and the others return #N/A. count returns 0.

Cell	Formula	Shows
A1	`4`	4
A2	`6`	6
A3		empty
A4	`n/a`	n/a
B1	`=sum(A1:A4)`	10
B2	`=count(A1:A4)`	2
B3	`=average(A1:A4)`	5
B4	`=sum(C1:C4)`	`#N/A`, because C1 to C4 are empty

Spills: a Series or Matrix in a cell

A formula that returns a Series or a Matrix fills more than one cell. The formula stays in its own cell, the top cell. The other cells are tinted and have no content of their own. Other formulas use them like ordinary cells.

A Series fills down

The top cell shows the first point. The next points fill the cells below it, one per row, in the order of the Series. Only the values appear; the dates do not. A gap in the Series is an empty cell. The spill stops at row 10,000 with no error.

A formula that refers to the top cell gets the whole Series.

Cell	Formula	Shows
A1	`=iv30.series("SPY")`	the first point; the history fills column A below it
B1	`=iloc(A1, -1)`	the latest point of the history
C1	`=mean(A1)`	the mean of the whole history

When a cell that the history would fill has content, the whole spill is withheld and the top cell shows #SPILL!. Clear that cell and the history fills down again.

A Matrix fills a block

The top cell shows the element in row 1, column 1. The other elements fill rows down and columns to the right.

Cell	Formula	Shows
A1	`=identity(3)`	1; the matrix fills A1 to C3
E1	`=det(A1)`	1

A block can fail to fit: a cell in it has content, or it passes row 10,000 or column AZ. The top cell then shows the size, such as [3×3], in place of the block. A formula that refers to the top cell still gets the whole matrix, so =det(A1) works in both states. Clear the cells in the block, or move the formula up or left, and the matrix fills it again.

Recalculation and cycles

When you change a cell, Sheet recalculates that cell and every cell that depends on it, directly or through other cells. A cell is recalculated after the cells it refers to. Cells that do not depend on the change keep their values.

A formula with a time function, sheet or sheet.mat is recalculated once a second while the clock is live. In step, it is recalculated when you move the clock.
A data function loads its data in the background. Its cell updates when the data arrives, and shows #N/A when there is no data.
When the site publishes new data for a symbol, every cell that uses that data loads it again and recalculates.

A formula that refers back to its own cell, directly or through other cells, shows #CYCLE!. Every cell that depends on it shows #CYCLE! too. A formula that refers to its own spill is also a cycle.

Cell	Formula	Shows
A1	`=B1+1`	`#CYCLE!`
B1	`=A1*2`	`#CYCLE!`
C1	`=B1`	`#CYCLE!`, because it depends on the cycle

Change one formula of the cycle so that it no longer refers back. The cells recalculate at once.

Copy and paste

A formula copied inside Sheet and pasted elsewhere moves its references by the same number of rows and columns as the paste. A $ keeps its part of the reference fixed. A reference that would move off the sheet becomes #REF! in the pasted formula, and the cell shows #REF!.

Copied from	Formula	Pasted to	Pasted formula
C1	`=A1+$B$1`	C4	`=A4+$B$1`
C1	`=sum(A1:B1)`	D2	`=sum(B2:C2)`
C1	`=A$1*2`	D5	`=B$1*2`
C2	`=A1`	C1	`=#REF!`

Names and the text inside quotes do not move. Text pasted from another program is entered as written, with no move. Sheet has no drag-fill.

Sheet functions

These functions come from spreadsheets. They also work in the Calculator, with numbers or a Series in place of cell references.

if, and, or and not are described in Write a formula. In a cell, =if(A1 > 0, 1 / A1, 0) returns 0 when A1 is 0. With A1 at 0, =1/A1 shows infinity.

average(series): the mean of a Series, a range or a roll(...), skipping gaps. The same as mean. It takes one argument, so give a range, such as =average(A1:A10). =average(A1, A2) gives E_ARITY. A single number is an error.

count(value): the number of points of a Series or range that are numbers. A single number counts as 1. A single cell reference counts as 1 even when the cell is empty, because an empty cell used alone is 0. A text cell used alone is an error.

stdevp(series): the standard deviation of a Series or range, dividing by N (population), skipping gaps. The same as std with one argument. There is no sample (N - 1) version. std(series, window) returns one number: the standard deviation of the last window points.

if(2 > 1, 10, 20) => 10
and(1, 2) => 1
and(1, 0) => 0
or(0, 0) => 0
not(0) => 1
count(5) => 1
count(iv30.series("SPY")) => a number of points with a value
average(iv30.series("SPY")) => a number
stdevp(iv30.series("SPY")) => a number

sum is in the Series group. See Series for the other aggregates and roll.

Use a workbook from another widget

Every Sheet has a workbook name, shown left of the address. A new Sheet gets the first free name of Book1, Book2 and so on. Double-click the name to rename the workbook. When a restored layout repeats a name, the second workbook gets -2 added, such as Book1-2.

A Calculator, or another Sheet, on the same page uses these two functions. Both arguments are text in double quotes.

sheet(book, ref): returns one cell as a number, or a range as a Series. ref is an address such as "A1", a range such as "A1:B10", either with a sheet name such as "Data!A1:B10", or a named cell such as "spot".

A reference without a sheet name uses a named cell first, then the first sheet of the workbook. It never uses the sheet on screen.
A single cell must contain a number. An empty cell returns E_NO_DATA (#N/A), and a text cell returns E_TYPE. A cell that shows an error returns E_REF (#REF!). The top cell of a spill returns the value it shows: the first point of a Series, or the top left element of a Matrix. A Matrix that shows its size returns E_NO_DATA. Read the whole history with a range.
A range returns a Series in row-by-row order. Each point is labelled with its position in the range, from 0, the same as a range inside a Sheet. Empty, text and error cells become gaps.

sheet.mat(book, ref): returns a range as a Matrix with the same rows and columns. A single address returns the cell value as a number. Every cell must contain a number: an empty cell returns E_NO_DATA, a text cell E_TYPE, and a cell that shows an error E_REF.

Both functions return E_REF when the workbook or the sheet is missing, or when the address is outside the sheet. Workbook names are case-sensitive.

Both functions take a copy of the cells each time they run. In a Sheet cell or in the Calculator, they run once a second while the clock is live. The Calculator also runs them when you edit the formula. A Sheet that uses sheet on its own workbook gets the values from before the current change until the next clock tick.

Widget	Formula	Returns
Calculator	`sheet("Book1", "B1")`	the number in B1 of the first sheet
Calculator	`mean(sheet("Book1", "Data!A1:A20"))`	the mean of the numbers in A1 to A20 of sheet Data
Calculator	`det(sheet.mat("Book1", "A1:B2"))`	the determinant of the 2 by 2 block
Sheet in Book2	`=sheet("Book1", "spot") * 2`	twice the cell named `spot` in Book1

A range from sheet in a Sheet cell is a Series, so it fills down like any other history.

Errors

A Sheet cell shows the display code of its error. Hover the cell for the code and the message. The codes this page introduces:

#REF! (E_REF): an address outside the sheet, a missing sheet or workbook, or a reference lost in a paste. A sheet read of a cell that shows an error also returns it.
#SPILL! (E_SPILL): a history has no room below its top cell.
#CYCLE! (E_CYCLE): the formula depends on its own cell.
#NAME? (E_UNKNOWN_NAME): a name or function that does not exist.
#N/A (E_NO_DATA): a range with no numbers, a missing data series, or an empty cell read with sheet or sheet.mat.
#ERROR: text used as a number (E_TYPE), a wrong number of arguments (E_ARITY) and the other errors without a code of their own.

The error reference lists every error with its fix. The full signatures are in the function reference.