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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
