# Spreadsheet in emacs with org-mode

## Basics

tested with org-mode >= 8.3 and emacs 25.1.50

define |
syntax |
examples |
---|---|---|

rows |
||

row | @<number> | `@1` – first row |

row number | @# | `@#` |

columns |
||

colum | $<number> | `$1` – first column |

column number | $# | `$#` |

`@2$3` – 2nd row, 3rd column (same as C2) |
||

`@2` – current column, row 2 |
||

others |
||

last line | @<gt> | `@>` |

hline | @<arabic> | `@I` – first hline |

`@-1$-3` – field one row up, three columns to the left |
||

`@-I$2` – field just under hline above current row, column 2 |
||

`@>$5` – field in the last row, in column 5 |
||

auto-calc | # | 1) |

which colums | C-? | |

*1)* auto-calc the field whenever `TAB`

`S-TAB`

or `RET`

are pressed in the row

**Column formulas** are for the complete column, expect there is a field or range formular for an other field in this column. See example below … the sum `col4`

is a special field formular in the column formula from column `$4`

.

| | | | =$3: col3= | | | | | | v | | | | Ablesedatum | Stand | Verbrauch Vormonat m³ s | | |---+-----------------+---------+-------------------------+------------------------------------------------------------------| | # | <2016-02-29 Mo> | 172.445 | 172.445 - Stand | | | # | <2016-03-31 Do> | 180.060 | 7.615 | <= diff = current row col3 - current row col3-1 == $4 = $3-@-1$3 | |---+-----------------+---------+-------------------------+------------------------------------------------------------------| | # | | Σ | 7.615 | <= summ col4 from row3 @>$4=vsum(@3$4..@-1$4) |

## Define Ranges

**ROW Index - Row Numbers**
`$1=@#-1`

## Sum all rows in a column

### Method 1 declare rows in TBLFM

- Use
**last**line in column 2 for the result`@>$2`

- declare row start
`@2`

and row end`@-1`

- sum all rows in column $2
`vsum(@2$2..@-1$2)`

also working without column declaration`vsum(@2..@-1)`

article | cost |
---|---|

milk | 1 |

beer | 1.50 |

cookie | 2.00 |

# | 4.5 |

$1: col1 | v | article | cost | |---------+------| | milk | 1 | <- @2: row2 | beer | 1.50 | | cookie | 2.00 | <- @-1: next to last row |---------+------| | # | 4.5 | <- @>$2: last row col2 #+TBLFM: @>$2=vsum(@2..@-1)

### Method 2 declare hlines in TBLFM

- Use
**last**line in column 2 for the result`@>$2`

- declare hline between the sum should occur e.g. between hline I
`@I`

and II`@II`

- sum all rows in column $2 between hline
`vsum(@I..@II)`

*by moving hline result will change*

article | cost |
---|---|

milk | 1 |

beer | 1.50 |

cookie | 2.00 |

# | 4.5 |

## Format

Formating is done at the formulas.

### decimal

# sum $1+$2 and format 2 decimals $1+$2;%.2f Same, format result to two decimals

## time

http://orgmode.org/manual/Durations-and-time-values.html#Durations-and-time-values

| Task 1 | Task 2 | Total | |---------+----------+----------| | 2:12 | 1:47 | 03:59:00 | | 3:02:20 | -2:07:00 | 0.92 | #+TBLFM: @2$3=$1+$2;T::@3$3=$1+$2;t

Input duration values must be of the form HH:MM[:SS], where seconds are optional. With the T flag, computed durations will be displayed as HH:MM:SS (see the first formula above). With the t flag, computed durations will be displayed according to the value of the option org-table-duration-custom-format, which defaults to 'hours and will display the result as a fraction of hours (see the second formula in the example above).

Negative duration values can be manipulated as well, and integers will be considered as seconds in addition and subtraction.

## elisp in org-mode formulas

convert the epoch time to a formated time string

;N means argument is a number - here the argument is in column

`$2`

e.g.`1480522777`

# Column Formulas $3 = '(format-time-string "%Y%m%d%H%M%S" $2);N

| | epoch | time format | |---+------------+----------------| | 1 | 1480522777 | 20161130171937 | | 2 | 1480522776 | 20161130171936 | #+TBLFM: $3='(format-time-string "%Y%m%d%H%M%S" $2);N

- http://orgmode.org/worg/org-tutorials/org-spreadsheet-lisp-formulas.html - using elisp fucntions in org-mode
- http://tonyballantyne.com/tech/tag/elisp-date/

## sorting table

org-sort

C-c ^