This question about Using an extension: Answered

Doesn't CALC have a division operator?

I've got a very simple table with some integer values, similar to this:
item count % of total
foo 7 ?
bar 9 ?
Total 16 0

What I want

I want to state the values 7 and 9 as percentage of their sum. This should be simple: 7 / 16 * 100 except that I don't know how to get these values.

What I have

Each cell in the bottom row uses %CALC{$SUM($ABOVE())}% to calculate the sums. Fine, that works. This also proves that the Spreadsheet plugin is installed correctly.

I tried using %CALC{$T(R2:C2)}% to access the value 7 but it returns the value 2?! What am I doing wrong?

If I manage to access the values, I would then need to divide them, but I can't find a division operator?! There's the $PRODUCT() function, but no $DIVISION(). Am I missing something?

Answer

Partial answer: For division, check out the EVAL function which can do simple calculations:

http://foswiki.org/System/SpreadSheetPlugin#EVAL_formula_evaluate_a_simple_m

Note the restriction: Formula can only reference cells in the current or preceding row of the current table; they may not reference cells below the current table row So it is not easy to calculate a running percentage like you want.

-- GeorgeClark - 24 Sep 2010

Theoretical answer: If the EVAL don't check out I would split the operation into two parts. Looking at the table it should be doable if you only knew the total before the search that sets up the table. So I would get the total one extra time at at the top of the topic and put it in a variable, like with a ___* Set TOTAL = %caluculate the total....
Then you could use the %TOTAL% when doing the table with calculations later (with an EVAL perhaps).

-- LarsEik - 25 Sep 2010

Just had to try that out. It's too difficult of course but a creative way:

item count % of total
foo 7 43.75
bar 9 56.25
Total 16 100

Variable total set from first table can be used anywhere later on: 16 View wiki text to see the hidden table.

-- LarsEik - 26 Sep 2010

Thank you, excellent input! With the help of the $EVAL() function, I was able to calculate the numbers I need. I found a solution that doesn't need an extra hidden table though, because the values I want to calculate on are actually TOPICCOUNT searches. So I'm just doing a lot of inline searches inside the $EVAL() and that works.

Having 7 searches in a page certainly isn't high-performance but it's not a problem with only 200 topics in the web.

I put the $EVAL() inside a $ROUND() to get rid of the decimal places.

readable summary:
|*Number of pages*|||
|Asked    |  %TOPICCOUNT{"asked" questions}% pages    |  %CALC{$ROUND($EVAL( %TOPICCOUNT{"asked" questions}%    / %TOPICCOUNT{all questions}% * 100 ), 0)}%% |
|Answered |  %TOPICCOUNT{"answered" questions}% pages |  %CALC{$ROUND($EVAL( %TOPICCOUNT{"answered" questions}% / %TOPICCOUNT{all questions}% * 100 ), 0)}%% |
|Total    |  %TOPICCOUNT{all questions}% pages        |  100% |

full code:
|*Number of pages*|||
| Asked  |  %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status'].value='Asked'" nonoise="on" order="topic" limit="all" excludetopic="%TOPIC%" separator="," format="$topic"}%"}% pages |  %CALC{$ROUND($EVAL( %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status'].value='Asked'" nonoise="on" order="topic" limit="all" excludetopic="%TOPIC%" separator="," format="$topic"}%"}% / %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status']" nonoise="on" order="topic" limit="all" excludetopic="%TOPIC%" separator="," format="$topic"}%"}% * 100 ), 0)}%% |
| Answered  |  %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status'].value='Answered'" nonoise="on" order="topic" limit="all" excludetopic="%PIC%" separator="," format="$topic"}%"}% pages |  %CALC{$ROUND($EVAL( %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status'].value='Answered'" nonoise="on" order="topic" limit="all" excludetopic="%TOPIC%" separator="," format="$topic"}%"}% / %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status']" nonoise="on" order="topic" limit="all" excludetopic="%TOPIC%" separator="," format="$topic"}%"}% * 100 ), 0)}%% |
| Total  |  %TOPICCOUNT{topic="%SEARCH{ type="query" "QuestionForm[name='Status']" nonoise="on" order="topic" limit="all" excludetopic="%TOPIC%" separator="," format="$topic"}%"}% pages |  100% |

-- TorbenGB - 27 Sep 2010

QuestionForm edit

Subject Using an extension
Extension SpreadSheetPlugin
Version Foswiki 1.0.9
Status Answered
Topic revision: r5 - 27 Sep 2010, TorbenGB
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy