Re: [sc-dev] =CELL("Width";A1) does not recognize changes of columnwidth ?

[Leonard Mada <discoleo@gmx.net>]

Hi Eike,

see my comments below. And apologies for cross posting, but this is 
ODF-relevant.

Eike Rathke wrote:
> Hi Leonard,
>
> On Sunday, 2008-10-12 19:11:36 +0200, Leonard Mada wrote:
>
>   
>> Shouldn't ODF implement the following functions:
>>
>> 1.) ISHIDDEN(cell, IGNORE_EMPTY = TRUE)
>> 2.) ISFILTERED(cell, IGNORE_EMPTY = TRUE)
>> 3.) ISVISIBLE(cell, IGNORE_EMPTY = TRUE)
>>     
>
> While ISHIDDEN() and ISFILTERED() might be useful, I don't see what
> ISVISIBLE() would be good for. Changing formula results depending on
> which part of the document is visible, if at all, is not such a good
> idea.
>   

Well, ISVISIBLE() was meant to represent (NOT HIDDEN) && (NOT FILTERED), 
and not the cells visible on the monitor. I agree that ISFILTERED() is 
the most useful one. But I sometimes hide rows as a replacement for 
filtering (when filtering is not feasiblel otherwise HIDE and FILTER are 
used quite differently), and I want to do some calculations on these 
rows. That was the purpose of hiding the rows in the first place. So, 
ISHIDDEN() (or its complement NOT ISHIDDEN() ) is also useful.

>> These functions would allow to catch the state of a cell  
>> (visible/hidden/filtered), allowing greatly-expanded interactivity in  
>> spreadsheet-sessions. I believe that especially the ISFILTERED() has an  
>> enormous potential. Of course, ISFILTERED() should support 
>> array-formulas:
>> {=SUM( IF( ISFILTERED(A1:A1000); 0 ; B1:B1000) ) }
>>     
>
> Exactly this example could already be accomplished using
> =SUBTOTAL(9;B1:B1000).
>   

Only that subtotal is limited to the basic 11 functions. Especially, I 
do not have a median (or other percentiles), and statistical tests. A 
lot of other computations are cumbersome if at all doable. With array 
formulas, I really meant, I want an array as a result. (So, not 
necessary the {= ... } formula I wrote, sorry for the confusion.)

Simple scenario: I would like to test if there is a statistical 
difference between 2 groups (or between a subgroup and its parent 
population). Lets use the t-test for simplicity. I haven't figured it 
out how to use subtotals to compute this.

My concept:
=TTEST( IF(  ISFILTERED(A1:A1000) ; E() ; B1:B1000  ) ; IF(  
ISFILTERED(A1:A1000); B1:B1000 ; E() ) ; 2 ; 3 )

where E() returns an empty element (we don't want zeroes in our t-test).

I agree that the formula still looks like hell of complex, and some 
further brainstorming and simplification should be thought. We need 
basically a function that returns 2 arrays, something like:

ISFILTERED(array_compare ; if_true_array ; if_false_array ; 
include_false_always = FALSE )

 - where array_compare are tested for an
   IF filtered condition
 - IF YES => element from if_true_array is appended
   to first array
 - IF NO => element from if_false_array is appended
   to second array
 - include_false_always
   -- IF set to TRUE, then return whole
       if_false_array, even IF condition not met
       this is useful when testing against the whole population
   -- otherwise return only elements were condition is met
 - the function should return 2 arrays

The inlcude_false_always could be omitted, if the function would support 
2 input-modes:
- taking one array argument:
  ISFILTERED( array_compare ; if_true_array )
  => returns only ONE array
  [2nd array will be crafted manually if needed
   by the user]

- taking 2 array arguments
  ISFILTERED( array_compare ; if_true_array ; if_false_array )
  => returns TWO arrays

I can do such a t-test using a very complex array-calculation, but MOST 
people are not able to write such a formula (actually, I test for one 
value vs other values, NOT if the cell is filtered out - this I am not 
able to do).

>> To go one step further, it should be possible to mark a row as  
>> *non-filterable*, this means, it should be displayed always. This would  
>> be the row where the user computes and displays some results.
>>     
>
> _Within_ the data to be filtered? Doesn't make much sense to me.
>   

Lets say one has 120 columns. Likely, there will be even more columns 
with massive spreadsheets.

The user finds it difficult to write his results on the right side of 
the data. He will prefer the bottom (which is also the more organised 
way). Then he computes some results. It is ugly to have results computed 
at the top. So, the results are computed at the bottom.

The most used filter is autofilter. So, how do I keep the results 
displayed, when I am filtering?
You need to keep a space between the range and the cell that holds the 
results. But then, the auto-sum breaks (it will compute the sum over the 
previous results to the left). And it is likely that at least 20-30% of 
results are still ordinary sums.

By the way, there seems to be a bug if using SUBTOTAL() in OOo Calc. IF 
computing SUBTOTAL(9 ; range ), and selecting only one element using the 
autofilter, THEN SUBTOTAL == with that element, so the autofilter should 
display it; BUT it doesn't. I have tested that the value computed IS 
actually identical to the condition used for filtering (write in a 
visible cell =cell_where_SUBTOTAL_is_computed to check the result).

>> [A more  
>> advanced concept involves splitting the results area completely off the  
>> main spreadsheet - a completely dedicated results area, going in-line 
>> with:
>> http://www.openoffice.org/issues/show_bug.cgi?id=83767 and
>> http://www.openoffice.org/issues/show_bug.cgi?id=85825 .]
>>     
>
> IZ currently is unavailable so I can't look it up.. but this sounds
> pretty much like copying a filtered result to a different area, which is
> already possible.
>
>   Eike
>   

This was referring to basically split the spreadsheet window into some 
functional areas. Lets take e.g. Microsoft's Visual Studio. When 
debugging an application, various variables are displayed at the bottom. 
You can hover over the code, but you have a specific area, where the 
"results" are displayed. This is a proven concept. Why mix end results 
with data?

This hardens debugging a spreadsheet a lot.

I hope these explanations help.

By the way, I noticed a discussion on the office-requirements TC-list. 
Please be aware, that summertime changes in the US one week later, so 
that basically there is one week, where the time difference between 
Europe and the US will be less one hour.

Sincerely,

Leonard