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