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