Org Spreadsheets
Org can be used as a simple spreadsheet system by simply using Org tables and writing special formulas inside the cells. It works quite similar to how it does in Excel.
Let's look at an example.
| Product | Price | Qty | Stock value |
|-------------+--------------+---------+----------------|
| Apples | 13.5 | 25 | :=$2*$3 |
| Oranges | 15.2 | 14 | :=$2*$3 |
| Bananas | 17 | 13 | :=$2*$3 |
|-------------+--------------+---------+----------------|
| Total |:=vmean(@2..@4)| :=@2+@3+@4|:=vsum(@2..@4) |
We put down the different figures we have and insert formulas which starts with :=. The results of those formulas are calculated on every re-render of the Preview/Agenda view and inserted in-place instead of the formulas.
Below is the result of the above table.
| Product | Price | Qty | Stock value |
-----------------------------------------------------------------
| Apples | 13.5 | 25 | 337.5 |
| Oranges | 15.2 | 14 | 212.8 |
| Bananas | 17 | 13 | 221 |
-----------------------------------------------------------------
| Total | 15.233 | 52 | 771.3 |
And rendered in the Agenda/Preview as:
Formulas
A formula has to start with := followed by the cell operands and operators. each cell can be represented in a coordinate system where '@' indicates rows and '$' columns. So the top left cell is located at @1$1 (row 1, column 1).
The heading separator line does not count. For example 'Apples' is located at @2$1 (row 2, column 1).
You can omit either the row or the column references and that means that you want to target the particular row or column which the formula is placed on.
In the example above, :=$2+$3 means column 2 + column 3 on the row which that formula is on. So no need to write :=@2$2+@2$3.
Operators
You can use the most common mathematical operations: +, -, * and / as well as ^ for exponents/power.
There are also some special function which can be used. They are:
- vmean(range): Calculates the mean of a range of cells
- vsum(range): Calculates the sum of a range of cells
- vmin(range): Finds the smallest value of a range of cells
- vmax(range): Finds the largest value of a range of cells
When using these v-formulas you have to supply them with a vector (a range) as argument. You specify the range between ceels by using the '..' operator. For example vmean($1..$4).
You can also use parenthesis to explicitly define prescedence, for example :=$1*(1 + $2)^$3
And you can also combine v-function with regular math expressions such as: :=vsum(@2..@4)/3.
Variables
You can use variables in your formulas. A variable is defined by the word in the cell to the left of where it is used. Below is an example where variables are used.
| *Expected Income* | |
| Gross_Salary_Wanted | 100000 |
| Health_Insurance | :=0.07 * Gross_Salary_Wanted |
| Pension_Plan | :=0.15 * Gross_Salary_Wanted |
| Total Revenue | :=vsum(@2..@4) |
| *Hourly rate* | |
| Unpaid_vacation_days | 20 |
| PublicHolidaysAndWeekends | 120 |
| Work_days_per_year | :=365 - Unpaid_vacation_days - PublicHolidaysAndWeekends |
| Work_hours_per_year | :=Work_days_per_year*8 |
| Rate needed | :=Gross_Salary_Wanted/Work_hours_per_year |
You can read more about variables and table calculations in this article.
Number formats
.is used as decimal point. ,is used as thousands separator. $ can be used in cells and is ignored.
% can be used to calculate with percentages. 75% in a cell means that 0.75 will be used in the calculations.
If the numbers are specified with colons, time is assumed for example 2:30 or 3:00. When used as operands then the result will be given in minutes.
Gotchas
- The formula must be in a cell which comes after the cells which make up its operands. The order is from top-left to bottom-right.
- As mentioned above, horizontal separator lines do not count as rows when counting coordinates for referencing cells.
- Relative references are not supported.
- Only a subset of the features in Emacs calc package is supported.
- Decimal point (
.), not comma must be used.