====== tablecalc Plugin ======

---- plugin ----
description: Adds ability to use Excel-style formulas in tables
author     : Sergey Gryaznov
email      : stalker@narezka.org
type       : syntax
lastupdate : 2020-08-27
compatible : Lemming, Anteater, Rincewind, Hrun, Detritus, Elenor of Tsort, Frusterick Manners, Greebo, Hogfather, Igor, Jack Jackrum
depends    : 
conflicts  : 
similar    : tablemath
tags       : tables calculation math

downloadurl: https://narezka.org/cfd/msgdb/740/tablecalc.zip
bugtracker : 
sourcerepo : 
donationurl: 

screenshot_img: 
----

===== 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 this plugin you would be able to insert [[wp>Microsoft Excel|Microsoft Excel (XL)]] styles formulas into the table

===== Download and Installation =====

Search and install the plugin using the [[plugin:extension|Extension Manager]]. Refer to [[:Plugins]] on how to install plugins manually.

  * [[http://narezka.org/cfd/msgdb/740/tablecalc.zip|TableCalc.zip]]

===== 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:
<code>
r0c4
</code>

:!: Please note, that row (''r'') and column (''c'') index starts from **zero** (''0'').

^  .  ^  0     ^  1     ^  2     ^
^  0  |  r0c0  |  r0c1  |  r0c2  |
^  1  |  r1c0  |  r1c1  |  r1c2  |
^  2  |  r2c0  |  r2c1  |  r2c2  |

Row and column prefixes can be swapped. The following is equal of the above:
<code>
c4r0
</code>

You can also reference to multiple cells in one range:
<code>
|r0c0:r1c1||
</code>

Furthermore you can use multiple ranges:
<code>
r0c0:r1c1,r0c3:r1c4
</code>

There is a recommendation not to use references for non-existing cells. For example, this is not correct (though it will work, returning "3"):
<code>
| 1 |
| 2 |
| ~~=sum(r0c0:r99c99)~~ |
</code>

Instead use constructions like this:
<code>
| 1 |
| 2 |
| ~~=sum(range(0,0,col(),row()-1))~~ |
</code>

===== Functions =====

The following functions are implemented:

^ Func  ^  Description  ^
| average(range)  | Returns average of the specified range  |
| cell(column,row)  | Returns numeric value of (column,row) cell |
| check(condition,true,false) | Executes ''true'' statement, if ''condition'' is not zero |
| col() | Returns current column  |
| compare(a,b,operation)  | Do math compare for ''a'' and ''b''. Returns zero when conditions for the ''operation'' are not met |
| count(range)  | Returns number of elements in the specified range |
| 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''  |
| label(string) | Binds label to the table  |
| max(range)  | Returns minimum value within specified range  |
| min(range)  | Returns minimum value within specified range  |
| range(column1,row1,column2,row2)  | Returns internal range for other functions  |
| round(number,decimals)  | Returns number, rounded to specified decimals |
| row() | Returns current row |
| sum(range)  | Returns sum of the specified range  |
| 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 ((The conflicting operators are: %%^ | ~%%)) conflict with the table markup so the following operators are available:

^  Operator  ^  Description  ^
|  +         | Addition and unary plus  |
|  -         | Subtraction and unary negative  |
|  *         | Multiplication  |
|  %%**%%     | Exponentiation  |
|  /         | Division  |
|  %         | Modulus (division remainder)  |
|  &         | Logical AND  |
|  %%<<%%    | Shift left  |
|  %%>>%%    | Shift right  |

===== Examples =====


==== I ====

<code>
| 1 | 2 | ~~=r0c0+r0c1~~ | ~~=10.2+1.5~~ |
</code>

| 1  | 2  | 3  | 11.7  |


==== II ====

<code>
| 1 | 2 |
| 3 | 4 |
| ~~=sum(r0c0:r1c1)~~ ||
</code>

| 1 | 2 |
| 3 | 4 |
| 10 ||


==== III ====

<code>
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
|  **~~=sum(r0c0:r1c1,r0c3:r1c3)~~**  ||||
</code>

| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
|  **26**  ||||


==== IV ====

<code>
|1|
|2|
|3|   
|4|   
|5.74|
|6|
|7|   
|8|   
|9|   
|10|    
|11|   
|~~=sum(range(col(),0,col(),row()-1))~~|
</code>

| 1                                       |
| 2                                       |
| 3                                       |
| 4                                       |
| 5.74                                    |
| 6                                       |
| 7                                       |
| 8                                       |
| 9                                       |
| 10                                      |
| 11                                      |
| 65.74                                   |
| ~~=sum(range(col(),0,col(),row()-1))~~  |



==== V ====

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


|1|
|2|
|3|   
|4|   
|5|
|6|
|7|   
|8|   
|9|   
|11|    
|5.5|


==== VI ====

<code>
| ~~=label(ex6_1)~~1 | 2 |
| 3 | 4 |

Sum: **~~=sum(ex6_1.c0r0:c99r99)~~**
</code>


| 1 | 2 |
| 3 | 4 |

Sum: **10**


==== VII ====

<code>
| **~~=label(ex7_1)~~11** | ~~=sum(ex7_2.c0r0:c99r99)~~ |
| 13 | 14 |

| ~~=label(ex7_2)~~1 | 2 |
| 3 | 4 |

Sum: **~~=sum(ex7_1.c0r0:c1r1)~~**
</code>


| **11** | 10 |
| 13 | 14 |

| 1 | 2 |
| 3 | 4 |

Sum: **48**


==== VIII ====

<code>
| **~~=min(c0r1:c0r3)~~** | **~~=max(c1r1:c1r3)~~** |
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |
</code>

| **1** | **9** |
| 1 | 7 |
| 2 | 8 |
| 3 | 9 |


==== IX ====

<code>
| 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)~~ |
</code>

| 1  | True   |
| 0  | False  |
| x  | False  |
|    | False  |
|    | False  |


==== X ====

<code>
| 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)~~ |
</code>

| 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  |
|  %%**%%    | %%~~= 3 ** 2 ~~%%  |  9  |
|  /         | %%~~= 6 / 2 ~~%%   |  3  |
|  %         | %%~~= 7 % 2 ~~%%   |  1  |
|  unary +   | %%~~= +2 ~~%%      |  2  |
|  unary -   | %%~~= -2 ~~%%      |  -2  |
|  &         | %%~~= 3 & 2 ~~%%   |  2  |
|  %%<<%%    | %%~~= 2 << 7 ~~%%  |  256  |
|  %%>>%%    | %%~~= 8 >> 2 ~~%%  |  2  |


==== XII ====

<code>
|~~=r0c1+5~~|6|
</code>

| 11 | 6 |


==== XIII ====

<code>
| ~~=label(ex13_1)~~apples | 32 |
| oranges | 54 |
| peaches | 75 |
| apples | 86 |

Apples: ~~=countif(ex13_1.r0c0:r9c0,#apples,#=)~~
</code>

| apples | 32 |
| oranges | 54 |
| peaches | 75 |
| apples | 86 |

Apples: 2


===== ChangeLog =====

__26.08.2020__
  * Fixed Hogfather compatibility issues


__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:

<code>
| A | B | ~~=col()~~ | 
| C    || ~~=col()~~ |
</code>

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...  [[axos88@gmail.com | Vandra Ákos ]] 08/31/2011
> very nice indeed, I need to borrow your syntax a bit, so I don't need to use ~~tm: [[guanfenglin@gmail.com|James Lin]]08/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 [[devel:javascript#using_ids]]  --- //[[andi@splitbrain.org|Andreas Gohr]] 2009/09/09 15:42//

> The plugin definitely needs max/min and conditional functions and/or :)

> Fixed all of the above  --- //[[stalker@os2.ru|Gryaznov Sergey]] 2010/04/14//

Can you add support for comma as decimal separator? //[[madenate@gmail.com|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:

{{http://img705.imageshack.us/img705/3628/tableexample.jpg|Table example with round() function applied to columns 3 to 6}}

--- //Christian 2010/09/29//

> There's problem when preceeding column to calculation contains markup, it goes bezerk: <file>
^ 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))~~  ^
</file>
Workaround is to avoid any markup in preceeding columns, i.e in this sample swap ''Description'' and ''Hours'' columns

{{http://img262.imageshack.us/img262/7013/w718.png|}}

 --- [[user>glen]] //2010/12/08 14:04//

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

 --- [[user>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**:
<code>
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);
}
</code>
[[http://flattr.com/thing/1660751/DokuWiki-tablecalc-plugin|{{http://api.flattr.com/button/button-static-50x60.png|Flattr this}}]]

--- [[fidokomik@gmail.com|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**:
<code>
function min(a) {
	var s=1*a[0];
	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[0];
	for (var i=1;i<a.length;i++) {
		if (1*a[i]>s) {
			s=1*a[i];
		}
	}
	return s;
}
</code>

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

[[http://flattr.com/thing/1660751/DokuWiki-tablecalc-plugin|{{http://api.flattr.com/button/button-static-50x60.png|Flattr this}}]]

--- [[fidokomik@gmail.com|fidokomik]] //2013/07/17//
==== Bug report ====

=== + Doesn't work in cell reference ===
When using + to reference a cell the + doesn't work.
<code>
~~=cell(col(),row()+1)~~
</code>
Will give nothing back, but using a - will work
<code>
~~=cell(col(),row()-1)~~
</code>

--- [[user>shshsh|SH]] //2019/07/08 //

== Plugin creates no output ===
None of the formulas outputs results for me (on Anteater). Any known conflicts with other plug-ins?
 --- [[user>mubed|mubed]] //2012/05/30 14:28//
> The plugin works beautifully for me on Detritus with loads of plugins installed. --- [[user>KaiMartin|KaiMartin]] //2015-09-02 19:54//

=== 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:
<code>
|~~=r0c1+5~~|6|
</code>
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:
<code>
|~~=r0c1+5~~|6|
|~~=1+1~~|6|
</code>
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:
<code>
|6|~~=r0c0+5~~|
</code>
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  
--- //[[stalker@narezka.org|Gryaznov Sergey]] 2017/01/31//

===Bug Report in Frusterick Manners===
<code php>
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
</code>
  
===Fix=== 
Change line 41 of ''./lib/plugins/tablecalc/syntax.php'' from

<code php>     function handle($match, $state, $pos, &$handler) { </code>

to 

<code php>     function handle($match, $state, $pos, Doku_Handler $handler) { </code>
     
Change line 72 of ''./lib/plugins/tablecalc/syntax.php'' from 

<code php>      function render($mode, &$renderer, $data) { </code>

to 

<code php>      function render($mode, Doku_Renderer $renderer, $data) { </code>

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

 --- [[user>mopani|mopani]] //2017-03-27 18:20//

=== Missing plugin.info.txt ===

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

<code>
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
</code>

 --- [[user>sphaira|sphaira]] //31/08/2017 12:10:29//

=== 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.

<code>
===== 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 |

</code>

 --- [[user>pot|pot]] //2018-04-03 01:34//

==== New version (2018/04/11) ====
> Fixed all of the above  
--- //[[stalker@narezka.org|Gryaznov Sergey]] 2018/04/11//

>> Above was fixed, but similar bug now appears in Chrome etc. browsers, but not Firefox etc. browsers. 
 --- [[user>mopani|mopani]] //2022-11-14 16:47//
==== 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. --- [[user>pot|pot]] //2018-06-02 14:16//

==== Decimals ====

>I've seen that the request for comma as a decimal separator is further above, somewhere after that comes a "fixed all of the above". Looks like this isn't part of this, is it?
>
>Also, what about delimiters in general? Large numbers like 1000000 are easily readable as 1.000.000,00 (or 1,000,000.00 for that matter). But the plugin doesn't really seem a) calculating those values correctly or b) giving a similar output, meaning that the output of 500,000.50+500,000.40 would be 1,000,000.90 instead of 1000000.90. Is there a way to achieve this? Or a possibility to get this supported? :)\\
--- [[user>benas|benas]] //2020-09-30 17:45//

> The following code in your [[devel:javascript#javascript_loading|/dokuwiki/conf/userscript.js]] will turn all occurences of dots "." into commas "," !
>
> <code php>   if( jQuery("[id^=__tablecalc]").length > 0 ) {
      setTimeout(function() {
         jQuery("#dokuwiki__content th").add("#dokuwiki__content td").each(function() {   
            var $calc_decimal = jQuery(this).text().split('.').join(',');
            jQuery(this).text($calc_decimal);
         });
      }, 100);
   } </code>
>Limitations:
> ・ at least ONE cell of ANY table on the page needs to be set up with tablecalc syntax: ~~= ... ~~
> ・ then ALL dots in ALL tables on the page are converted to commas on pageload (only for visual display)
> ・ in page edit mode (or edittable) decimal delimiters are still to be filled in as dots "." (as before), if intended to be used for calculations with tablecalc!
> --- [[user>Chris75|Chris75]] //2022-11-18 12:25//

==== Incompatibility with Chrome and Brave? ====

I have been using TableCalc for months now and haven't had too many problems until recently. When using Chrome or Brave, the tables randomly stop calculating after a few entries and show the wrong results. The second link has an example table. The result should show 446 Total and 0 (zero) Current. This is accurate in Firefox. In Chrome/Brave, I get many various results BUT 446 and 0. Is there a way to fix this?\\

https://forum.dokuwiki.org/d/20026-tablecalc-stops-sum-after-too-many-entries\\
http://www.shadowsoverthesouth.net/characterwiki/doku.php?id=app:output:test\\
 --- [[user>Feathers|Feathers]] //2022-03-24 23:07//

> There appears to be a javascript problem in Chrome/Brave/Vivaldi/Edge that does not appear in Firefox or PaleMoon (Gecko/Goanna). For example, a table that sums a previous table using a ''label'' does not work in Chrome etc. if there are more than one or two lines in the previous table, but all work fine in Firefox etc. 
>
> See an example at https://www.metatek.org/test_tablecalc 
>
> This is the Tablecalc issue that I run into most frequently; I suspect the above is similarly related to the way that Chrome etc. handle the javascript created by Tablecalc pages. 
> --- [[user>mopani|mopani]] //2022-11-13 22:59//

>That's because as browsers are ever getting faster at loading pages javascript is starting to fall behind. 
>PHP can only show you results on the page when Javascript is ready with calculation. Since this is a javascript plugin, some timeout has to be adjusted. The more math work is given to tablecalc, the more timeout it needs to complete the task before the page can be loaded by PHP using those results. Look for this line of code in **/dokuwiki/lib/plugins/tabelcalc/script.js** and set the timeout to 100 milliseconds, like so:
>
> <code php>    setTimeout(tablecalc_final,100) </code>
>Save your file, close and restart your browser and navigate back to the wiki page. Now, it will work in Edge, Chrome, Firefox and IE11. (Tested with DokuWiki-on-a-stick, release Igor, Sprintdoc template, latest version of tablecalc plugin and with the wiki page syntax from the example above.) 
>
>EDIT: At least part of the above problem seems to be related to using non-existing cells which was discouraged here before. (Probably, because it gives tablecalc just too much mathwork it cannot handle.) I found the following useful for autosum of all of the cells above the current cell (assuming you have exactly one header row): 
><code>~~=round(sum(range(col(),1,col(),row()-1)),2)~~ </code>
>--- [[user>Chris75|Chris75]] //2022-11-17 21:47//

==== Feature requests ====

>Feature: label for single cells - similar to label for table 
>Application: to make reference to single values more dynamic
>
>--- [[user>Chris75|Chris75]] //2022-11-17 21:47//