[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

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

*From*:**Leonard Mada <discoleo@gmx.net>***To*: dev@sc.openoffice.org, office-comment@lists.oasis-open.org*Date*: Tue, 14 Oct 2008 20:59:30 +0200

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

**References**:**Re: [sc-dev] =CELL("Width";A1) does not recognize changes of columnwidth ?***From:*Leonard Mada <discoleo@gmx.net>

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]