Question

I'm trying to find out how many cells in column 3 of a table contain the string 'Yes %EDITCELL...'
Using the Spreadsheet plugin as follows doesn't work because the % is taken as the end of the CALC.

%CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes %EDIT)}%

Has anyone any ideas how I can achieve this? I've tried all sorts of things but can't escape the %. There's got to be some way!

Environment

-- DavidBaker - 21 Apr 2006

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

This works ,but doesn't solve the problem, read on.

%CALC{$LISTSIZE($LISTIF($SEARCH(Yes,$item) == 1,$LIST(R1:C3..R$ROW(0):C3)))}%

But it's a bit longhanded for something so simple. I thought it should be possable do it your way using $per or $nop() but I can't get it to work:

|             | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes %EDITCELL...)}% |
|             | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $perEDITCELL...)}% |
|             | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $NOP(%)EDITCELL...)}% |
|             | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $NOP($per)EDITCELL...)}% |
|             | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), $NOP(Yes $perEDITCELL...))}% |
| <nop>TWikiGuest | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes %EDITCELL...)}% |
| fixed       | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes %EDITCELL...)}% |
| fixed?      | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $perEDITCELL...)}% |
| fixed       | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $NOP(%)EDITCELL...)}% |
| fixed       | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $NOP($per)EDITCELL...)}% |
| fixed       | | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), $NOP(Yes $perEDITCELL...))}% |

-- SamHasler - 21 Apr 2006

The problem isn't the percent sign, the problem seems to be that the complete cell content must match.

-- TWikiGuest - 22 Apr 2006

Ah, if I fix my example above some of them do work but they wouldn't have solved the problem, not even my original example. I realise what was causing David's problem, it's not the opening precent of EDITCELL, it's the closing }% that would close the CALC. If we create a full working example you have to NOP something in the CALCs.

Although strangely when I try it the count is wrong :/

| | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes %EDITCELL{"select,2,Yes,No"}% |
| | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes $NOP(%)EDITCELL{"select,2,Yes,No"}$NOP(%))}% |
| !TWikiGuest | %CALC{$COUNTSTR($LIST(R1:C3..R$ROW(0):C3), Yes)}% %EDITCELL{label, 0, $percntCALC{$dollarCOUNTSTR($dollarLIST(R1:C3..R$dollarROW(0):C3), Yes)}$percnt}% |

I also noticed that when I edit the table the CALC's aren't preserved on save, so I think the problem may be moot.

And even my original example that worked with only EDITCELL... doesn't work any more:

%CALC{$LISTSIZE($LISTIF($SEARCH(Yes,$item) == 1,$LIST(R1:C3..R$ROW(0):C3)))}%

-- SamHasler - 22 Apr 2006

You are counting all Yes entries and there are seven in your example.

-- FranzJosefSilli - 23 Apr 2006

Thanks for all your efforts - I'd almost given up with my attempts and come to the conclusion I must find another way. I didn't want the select to apply to the whole column because I needed some entries to be labels. Because the list command creates a list of 4 elements from one cell (i.e. the first list member is 'Yes %EDITCELL{select', the second is '2', the third is 'Yes' and the fourth is 'No') I think I'll have derive the count in stages as follows.

1. Create a list from all the cells in the column
2. Add up the number of list elements equal to 'Yes'. This will be the sum of all the cells containing the label 'Yes', plus all the cells containing 'Yes %EDITCELL{select,2,Yes,No}% plus all the cells containing 'No %EDITCELL{select,2,Yes,No}% plus all the cells containing '%EDITCELL{select,2,Yes,No}%
3. Add up the number of cells containing 'Yes %EDITCELL{select'
4. Subtract 1 'Yes' for each cell containing 'EDITCELL' i.e. occurrence of a list element set to 'No %EDITCELL{select' or '%EDITCELL{select' or 'Yes %EDITCELL{select'

i.e. A=number of list elements containing 'Yes'
B=number of list elements containing 'Yes %EDITCELL{select'
C=number of list elements containing '%EDITCELL{select'
D=number of list elements containing 'No %EDITCELL{select'

Number of cells with 'Yes' selected = A+B-C-D-B = A-C-D

-- DavidBaker - 24 Apr 2006

Reverted TWikiGuest's edit that removed the EDITCELL's from one of my examples and which appeared to make it work. I've turned of table editing since it won't work anyway. And moved the CALC's to the previous column as they were counting each other which wasn't helping :).

Thanks for the explanation David. I see now how all the CALCs are failing. None of those approaches were going to work.

-- SamHasler - 24 Apr 2006

AHA! found a solution to the comma problem on SpreadSheetPluginDev posted by ThomasMundar.

Here's the solution:

| 4 | %CALC{$COUNTSTR($LISTMAP( $TRANSLATE( $T( R$EVAL($index+1):C3 )$SET( junk, $item ), $comma, ; ), $LIST( R1:C1..R$ROW(-1):C1 ) ), Yes %EDITCELL{"select;2;Yes;No"}$NOP(%))}% |

It translates cells offset from a column that must always contain something, but never commas. I'm converting the commas to ; instead of | from the example on SpreadSheetPluginDev as that doesn't mess up the table. Also note that the +1 in the R$EVAL($index+1):C3 is to take account of the header, if you have more header rows, or none, you will have to take that into account.

I've changed the status to AnsweredQuestions.

-- SamHasler - 24 Apr 2006