MetaTek

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== tablecalc plugin ======

===== Overview =====
Sometimes one may need to do simple calculations in DokuWiki table. The idea was taken from [[doku>plugin:tablemath]] plugin, but conception changed a little bit. With the help of the [[doku>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 [[doku>plugin:plugin|Plugin Manager]] using the following URL. Refer to [[doku>:Plugins]] on how to install plugins manually.

  * [[http://narezka.ru/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. 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:

''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()                       | FIXME  |

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 |
|  /         | 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:r1c4)~~** ||||
</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|



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


|1|
|2|
|3|   
|4|   
|5|
|6|
|7|   
|8|   
|9|   
|10|    
|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 |

~~=calc()~~
</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  |
|  /         | %%~~= 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:

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

==== Bug report ====
=== 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//
MetaTek

User Tools

Site Tools

Page Tools
