Next: Sorting by column, Previous: The print area, Up: Advanced Features [Contents][Index]

### 4.2 Ranges in formulas

A formula like

    (+ A1 A2 A3)

is the sum of three specific cells. If you insert a new second row, the formula becomes

    (+ A1 A3 A4)

and the new row is not included in the sum.

The macro `(ses-range from to)` evaluates to a list of the values in a rectangle of cells. If your formula is

    (apply '+ (ses-range A1 A3))

and you insert a new second row, it becomes

    (apply '+ (ses-range A1 A4))

and the new row is included in the sum.

While entering or editing a formula in the minibuffer, you can select a range in the spreadsheet (using mouse or keyboard), then paste a representation of that range into your formula. Suppose you select A1-C1:

`[S-mouse-3]`

  Inserts "A1 B1 C1" `(ses-insert-range-click`)

`C-c C-r`

  Keyboard version (`ses-insert-range`).

`[C-S-mouse-3]`

  Inserts "(ses-range A1 C1)" (`ses-insert-ses-range-click`).

`C-c C-s`

  Keyboard version (`ses-insert-ses-range`).

If you delete the from or to cell for a range, the nearest still-existing cell is used instead. If you delete the entire range, the formula relocator will delete the ses-range from the formula.

If you insert a new row just beyond the end of a one-column range, or a new column just beyond a one-row range, the new cell is included in the range. New cells inserted just before a range are not included.

Flags can be added to `ses-range` immediately after the to cell.

`!`

  Empty cells in range can be removed by adding the `!` flag. An empty cell is a cell the value of which is one of symbols `nil` or `*skip*`. For instance `(ses-range A1 A4 !)` will do the same as `(list A1 A3)` when cells `A2` and `A4` are empty.

`_`

  Empty cell values are replaced by the argument following flag `_`, or `0` when flag `_` is last in argument list. For instance `(ses-range A1 A4 _ "empty")` will do the same as `(list A1 "empty" A3 "empty")` when cells `A2` and `A4` are empty. Similarly, `(ses-range A1 A4 _ )` will do the same as `(list A1 0 A3 0)`.

`>v`

  When order matters, list cells by reading cells row-wise from top left to bottom right. This flag is provided for completeness only as it is the default reading order.

`<v`

  List cells by reading cells row-wise from top right to bottom left.

`v>`

  List cells by reading cells column-wise from top left to bottom right.

`v<`

  List cells by reading cells column-wise from top right to bottom left.

`v`

  A short hand for `v>`.

`^`

  A short hand for `^>`.

`>`

  A short hand for `>v`.

`<`

  A short hand for `>^`.

`*`

  Instead of listing cells, it makes a Calc vector or matrix of it (see GNU Emacs Calc Manual). If the range contains only one row or one column a vector is made, otherwise a matrix is made.

`*2`

  Same as `*` except that a matrix is always made even when there is only one row or column in the range.

`*1`

  Same as `*` except that a vector is always made even when there is only one row or column in the range, that is to say the corresponding matrix is flattened.

Next: Sorting by column, Previous: The print area, Up: Advanced Features [Contents][Index]