Sidebar
Table of Contents
tablecalc plugin
Overview
Sometimes one may need to do simple calculations in DokuWiki table. The idea was taken from plugin:tablemath plugin, but conception changed a little bit. With the help of the plugin:tablecalc plugin you would be able to insert Excel styles formulas into the table
Download and Installation
Download and install the plugin using the Plugin Manager using the following URL. Refer to :Plugins on how to install plugins manually.
Syntax
To perform a calculation you need to insert XL-style formula into work sheet. Any expression must be started with ~~= and finished by ~~. You can use direct range specification for functions (like XL does) or a special range() function. The range consists of a reference to the start cell and to the finish cell, like this:
r0c4
Please note, that row (r) and column (c) index starts from zero. Row and column prefixes can be swapped. The following is equal of the above:
c4r0
You can also reference to multiple cells in one range:
|r0c0:r1c1||
Furthermore you can use multiple ranges:
r0c0:r1c1,r0c3:r1c4
There is a recommendation not to use references for non-existing cells. For example, this is not correct (though it will work, returning “3”):
| 1 | | 2 | | ~~=sum(r0c0:r99c99)~~ |
Instead use constructions like this:
| 1 | | 2 | | ~~=sum(range(0,0,col(),row()-1))~~ |
Functions
The following functions are implemented:
x = column, y = row
| Func | Description |
|---|---|
| cell(x,y) | Returns numeric value of (x,y) cell |
| row() | Returns current row |
| col() | Returns current column |
| range(x1,y1,x2,y2) | Returns internal range for other functions |
| sum(range) | Returns sum of the specified range |
| count(range) | Returns number of elements in the specified range |
| round(number;decimals) | Returns number, rounded to specified decimals |
| label(string) | Binds label to the table |
| average(range) | Returns average of the specified range |
| min(range) | Returns minimum value within specified range |
| max(range) | Returns minimum value within specified range |
| check(condition;true;false) | Executes true statement, if condition is not zero |
| compare(a;b;operation) | Do math compare for a and b. Returns zero when conditions for the operation are not met |
| calc() |  |
Though you can use colon as delimiter in functions semi-colon is preferred and recommended.
Operators
Most of the standard Javascript arithmetic operators are supported but some 1) conflict with the table markup so the following operators are available:
| Operator | Description |
|---|---|
| + | Addition and unary plus |
| - | Subtraction and unary negative |
| * | Multiplication |
| / | Division |
| % | Modulus (division remainder) |
| & | Logical AND |
| << | Shift left |
| >> | Shift right |
Examples
I
| 1 | 2 | ~~=r0c0+r0c1~~ | ~~=10.2+1.5~~ |
| 1 | 2 | 3 | 11.7 |
II
| 1 | 2 | | 3 | 4 | | ~~=sum(r0c0:r1c1)~~ ||
| 1 | 2 |
| 3 | 4 |
| 10 | |
III
| 1 | 2 | 3 | 4 | | 5 | 6 | 7 | 8 | | **~~=sum(r0c0:r1c1,r0c3:r1c4)~~** ||||
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
| 26 | |||
IV
|1| |2| |3| |4| |5.74| |6| |7| |8| |9| |10| |11| |~~=sum(range(col(),0,col(),row()-1))~~|
| 1 |
| 2 |
| 3 |
| 4 |
| 5.74 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 65.74 |
V
|1| |2| |3| |4| |5| |6| |7| |8| |9| |10| |~~=average(range(col(),0,col(),row()-1))~~|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 5.5 |
VI
| ~~=label(ex6_1)~~1 | 2 | | 3 | 4 | Sum: **~~=sum(ex6_1.c0r0:c99r99)~~**
| 1 | 2 |
| 3 | 4 |
Sum: 10
VII
| **~~=label(ex7_1)~~11** | ~~=sum(ex7_2.c0r0:c99r99)~~ | | 13 | 14 | | ~~=label(ex7_2)~~1 | 2 | | 3 | 4 | Sum: **~~=sum(ex7_1.c0r0:c1r1)~~**
| 11 | 10 |
| 13 | 14 |
| 1 | 2 |
| 3 | 4 |
Sum: 48
VIII
| **~~=min(c0r1:c0r3)~~** | **~~=max(c1r1:c1r3)~~** | | 1 | 7 | | 2 | 8 | | 3 | 9 | ~~=calc()~~
| 1 | 9 |
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |
IX
| 1 | ~~=check(cell(0,row()),#True,#False)~~ | | 0 | ~~=check(cell(0,row()),#True,#False)~~ | | x | ~~=check(cell(0,row()),#True,#False)~~ | | | ~~=check(cell(0,row()),#True,#False)~~ | | **** | ~~=check(cell(0,row()),#True,#False)~~ |
| 1 | True |
| 0 | False |
| x | False |
| False | |
| False |
X
| 1 | 2 | 1=2 | ~~=check(compare(cell(0,row()),cell(1,row()),#=),#True,#False)~~ | | 3 | 3 | 3=3 | ~~=check(compare(cell(0,row()),cell(1,row()),#=),#True,#False)~~ | | 4 | 5 | 4<5 | ~~=check(compare(cell(0,row()),cell(1,row()),#<),#True,#False)~~ | | 6 | 7 | 6>7 | ~~=check(compare(cell(0,row()),cell(1,row()),#>),#True,#False)~~ | | 8 | 9 | 8>9 | ~~=check(compare(cell(0,row()),cell(1,row()),#>),#True,#False)~~ | | 10 | 10 | 10≥10 | ~~=check(compare(cell(0,row()),cell(1,row()),#>=),#True,#False)~~ | | 11 | 11 | 11≤11 | ~~=check(compare(cell(0,row()),cell(1,row()),#>=),#True,#False)~~ | | 12 | 12 | 12≠12 | ~~=check(compare(cell(0,row()),cell(1,row()),#!=),#True,#False)~~ |
| 1 | 2 | 1=2 | False |
| 3 | 3 | 3=3 | True |
| 4 | 5 | 4<5 | True |
| 6 | 7 | 6>7 | False |
| 8 | 9 | 8>9 | False |
| 10 | 10 | 10≥10 | True |
| 11 | 11 | 11≤11 | True |
| 12 | 12 | 12≠12 | False |
XI
| Operator | Equation | Result |
|---|---|---|
| + | ~~= 3 + 2 ~~ | 5 |
| - | ~~= 3 - 2 ~~ | 1 |
| * | ~~= 3 * 2 ~~ | 6 |
| / | ~~= 6 / 2 ~~ | 3 |
| % | ~~= 7 % 2 ~~ | 1 |
| unary + | ~~= +2 ~~ | 2 |
| unary - | ~~= -2 ~~ | -2 |
| & | ~~= 3 & 2 ~~ | 2 |
| << | ~~= 2 << 7 ~~ | 256 |
| >> | ~~= 8 >> 2 ~~ | 2 |
ChangeLog
14.04.2010
- Added labels and cross-table references
- Added cross-table resolver and forward calculations
- Added min(),max() and average() functions
- Added ability to use semi-colon as a function parameters separator
- Added compare functions
- Added string escaping (#)
- Fixed javascript/CPU float point calculation bug
- Fixed invalid HTML ID's usage</todo>
07.09.2009
- Initial release
Discussion
col() returns wrong number if the preceeding cells in the same row use colswap, eg:
| A | B | ~~=col()~~ | | C || ~~=col()~~ |
gives:
| A | B | 2 |
| C | 1 | |
— Michał Sacharewicz 2012/01/11
Is it possible to make math calculations (like multiplication)… So that to add the VAT to the price for example. for or sthg similiar… Vandra Ákos 08/31/2011
very nice indeed, I need to borrow your syntax a bit, so I don't need to use ~~tm: James Lin08/09/2009
Some coding advice: You're working with a blacklist to avoid script inclusion, a whitelist might be more secure. You're using the output of rand() as a HTML ID. Numbers alone are not allowed as IDs in XHTML, you should prefix them with your plugin name. Also have a look at using_ids — Andreas Gohr 2009/09/09 15:42
The plugin definitely needs max/min and conditional functions and/or :)
Fixed all of the above — Gryaznov Sergey 2010/04/14
Can you add support for comma as decimal separator? madenate 2010/06/25
I would appreciate this, too. (Nice work, though!) — Christian 2010/09/29
Also, it would be nice if the round() function could use the exact number of specified decimal places, even if these would be zero. See the following image as a sample why it would probably look better:
— Christian 2010/09/29
There's problem when preceeding column to calculation contains markup, it goes bezerk:^ Date ^ Description ^ Hours (Decimal) ^ | 2008-08-29 | xx | 6.5 | | 2008-08-30 | xxx | 1.5 | | 2008-09-03 | xxxx | -4.00 | | 2008-09-03 | [[:config]] yea | -4.00 | ^ ^ ^ ~~=sum(range(1,0,col(),row()-1))~~ ^
Workaround is to avoid any markup in preceeding columns, i.e in this sample swap Description and Hours columns
— glen 2010/12/08 14:04
Bug report
Plugin creates no output
None of the formulas outputs results for me (on Anteater). Any known conflicts with other plug-ins?
— mubed 2012/05/30 14:28
