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

ranges	description
$1..$3	first three fields in the current row
$P..$Q	range, using column names (see under Advanced)
$<	start in third column, continue to the last but one
@2$1..@4$3	6 fields between these two fields (same as A2..C4)
@-1$-2..@-1	3 fields in the row above, starting from 2 columns on the left
@I..II	between first and second hline, short for @I..@II

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 ^