# DokuWiki

It's better when it's simple

### Sidebar

Learn about DokuWiki

Advanced Use

Corporate Use

Our Community

Follow us on Facebook, Twitter and other social networks.

plugin:tablecalc

# tablecalc Plugin

Compatible with DokuWiki

• 2018-04-22 "Greebo" unknown
• 2017-02-19 "Frusterick Manners" yes
• 2016-06-26 "Elenor Of Tsort" yes
• 2015-08-10 "Detritus" yes
Last updated on
2018-04-11
Provides
Syntax

Similar to tablemath

Tagged with calculation, math, tables

## Overview

Sometimes one may need to do simple calculations in DokuWiki table. The idea was taken from Tablemath Plugin, but conception changed a little bit. With the help of this plugin you would be able to insert Excel styles formulas into the table

## Download and Installation

Search and install the plugin using the Extension Manager. 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
countif(range;b;operation) Count values in `range`. Counts only such values from `range` which meets comparation with `b`. The comparation type is specified in `operation`
calc() Depreciated. Not needed anymore

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 ~~=sum(range(col(),0,col(),row()-1))~~

### V

```|1|
|2|
|3|
|4|
|5|
|6|
|7|
|8|
|9|
|11|
|~~=average(range(col(),0,col(),row()-1))~~|```
 1 2 3 4 5 6 7 8 9 11 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 |```
 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

### XII

`|~~=r0c1+5~~|6|`
 11 6

### XIII

```| ~~=label(ex13_1)~~apples | 32 |
| oranges | 54 |
| peaches | 75 |
| apples | 86 |

Apples: ~~=countif(ex13_1.r0c0:r9c0,#apples,#=)~~```
 apples 32 oranges 54 peaches 75 apples 86

Apples: 2

## ChangeLog

11.04.2018

• Fixed php-7 compatibility issues
• Added plugin.info.txt
• Fixed cross references between tables

05.07.2017

• Fixed range() function

09.02.2017

• Added countif() function

31.01.2017

• Fixed min(),max(),round() functions as suggested
• Plugin execution code was significantly revisioned and corrected

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

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. ~~=multiply(cell(row(),col()-1), 1.25)~~ 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_idsAndreas 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

I'd appreciate sqrt() function. Great job! Thanks, Robert

— anonymous 2017/07/25 22:55

### Some bugfixes

I found a way how to get better result from round(). Now round() return as many decimal digits, as many you require.

Examples:
round(1,1) return 1.0
round(1,2) return 1.00
round(1,5) return 1.00000

This is a new code in script.js:

```function round(num,digits) {
var d=1;
for (var i=0;i<digits;i++) {
d*=10;
}
var n=Math.round(num*d)/d;
return n.toFixed(digits);
}```

fidokomik 2013/07/17

I found a bug in max() and min() functions. Javascript is “loosely typed” language so if you really need a number then you must retype a variable. Pretty old trick is to multiply text variable by 1.

Examples:
a=“10.123” - variable `a` is a text variable
a=1 * “10.123” - variable `a` is a numeric variable

This is a new code in script.js:

```function min(a) {
var s=1*a;
for (var i=1;i<a.length;i++) {
if (1*a[i]<s) {
s=1*a[i];
}
}
return s;
}

function max(a) {
var s=1*a;
for (var i=1;i<a.length;i++) {
if (1*a[i]>s) {
s=1*a[i];
}
}
return s;
}```

You can find the fork of tablecalc plugin at “Pirate Party CZ” GitHub repository too.
https://github.com/pirati-cz/tablecalc

fidokomik 2013/07/17

### Bug report

#### + Doesn't work in cell reference

When using + to reference a cell the + doesn't work.

`~~=cell(col(),row()+1)~~`

Will give nothing back, but using a - will work

`~~=cell(col(),row()-1)~~`
##### Plugin creates no output

None of the formulas outputs results for me (on Anteater). Any known conflicts with other plug-ins? — mubed mubed

2012/05/30 14:28

#### Access to cells follow up in same row not working

If you have a formula and giving a cell which is on the same row but in a cell to the right of the cell where you have the formula, the value of that cell might not be accessible by the plugin and the formula might return nothing or incorrect results.
The extra condition for that to happen is, that you have no rows after that particular row containing a formula.
In other words: If you access in a formula a cell right of the formula and don't have a row after that current row containing a formula, you will run into this issue.

If you have:

`|~~=r0c1+5~~|6|`

You expect 11, but will receive a empty cell.
Because you are accessing a cell in the formula which follows after the one containing the formula and there is no row following up containing a formula

If you change it to:

```|~~=r0c1+5~~|6|
|~~=1+1~~|6|```

You will receive 11 and 2.
Because after the line there is one more line containing a formula it works

If you do it like this:

`|6|~~=r0c0+5~~|`

This will work, you receive 11.
Reason: The cell you are accessing within the formula is before the cell which contains the formula.

### New version (2017/01/31)

Fixed all of the above

Gryaznov Sergey 2017/01/31

#### Bug Report in Frusterick Manners

```Warning: Declaration of syntax_plugin_tablecalc::handle(\$match, \$state, \$pos, &\$handler) should be compatible with DokuWiki_Syntax_Plugin::handle(\$match, \$state, \$pos, Doku_Handler \$handler) in ./lib/plugins/tablecalc/syntax.php on line 0

Warning: Declaration of syntax_plugin_tablecalc::render(\$format, &\$renderer, \$data) should be compatible with DokuWiki_Syntax_Plugin::render(\$format, Doku_Renderer \$renderer, \$data) in ./lib/plugins/tablecalc/syntax.php on line 0```

#### Fix

Change line 41 of `./lib/plugins/tablecalc/syntax.php` from

`     function handle(\$match, \$state, \$pos, &\$handler) { `

to

`     function handle(\$match, \$state, \$pos, Doku_Handler \$handler) { `

Change line 72 of `./lib/plugins/tablecalc/syntax.php` from

`      function render(\$mode, &\$renderer, \$data) { `

to

`      function render(\$mode, Doku_Renderer \$renderer, \$data) { `

via bug report at https://github.com/cosmocode/dokuwiki-plugin-note/issues/1

#### Missing plugin.info.txt

The plugin don't work without the file plugin.info.txt. Here is the content of this file :

```base   tablecalc
author Sergey Gryaznov
email  stalker@narezka.org <>
date   2017-02-09
name   TableCalc Plugin
desc   Table Calculation
url    https://www.dokuwiki.org/plugin:tablecalc```

#### Bug: sum() does not sum formulas that refer to a previous table

In the third table table, the sum() formula includes a literal number and references to two previous tables. When the reference is to a literal number (from table f1), it is accounted for; when the reference is from a formula (from table f2), it is ignored.

```===== Bug =====

^  ~~=label(f1)~~ table f1^^
^  1000 ^ this is a literal number |
|    800.00 |
|   -160.00 |

^  ~~=label(f2)~~ table f2^^
^  ~~=sum(range(col(),2,col(),3),2)~~ ^ this is a formula ^
|  16665.00 |
|   4315.88 |

^ sum() bug ^
|  ~~=f1.c0r1~~ | this reference to table f1 is accounted for |
|         10.00 |
|  ~~=f2.c0r1~~ | this reference to table f2 is ignored |
^  ~~=round(sum(range(col(),0,col(),row()-1)),2)~~ ^ the sum ignores reference to formula |
```

### New version (2018/04/11)

Fixed all of the above

Gryaznov Sergey 2018/04/11

### Looping

Unfortunately the new version loops on my page, which is more complex than the example I give above. I can send it to you privately for debugging, but I do not want to publish it here because it contains private data. — pot pot

2018-06-02 14:16

1)
The conflicting operators are: ^ | ~
plugin/tablecalc.txt · Last modified: 2019-07-08 20:11 by shshsh

### Page Tools 