Re: [sc-dev] Validator for Calc
My biggest wish would be for type checking. E.g. if a cell seems to hold a currency value, and one a date, than currency + date is an error. So, the idea is to verify that types are used in proper calculations. Some options may specify the stringency of the validation, e.g.: - allow or disallow currency + scalar This is more for the financial system, but it would be of huge benefit in that case. Sincerely, Leonard Original-Nachricht Datum: Fri, 04 Jun 2010 11:47:12 +0200 Von: Niklas Nebel niklas.ne...@sun.com An: dev@sc.openoffice.org Betreff: Re: [sc-dev] Validator for Calc On 06/04/10 10:59, Kálmán „KAMI” Szalai wrote: We started this out of OpenOffice.org Intership program. I am thinking about the possible other checkt (the current version of Validator extension implements these checks, so we are looking more. For example excluded cells from sum, for example. Excel has this functionality. Oh, I see. Yes, excluded cells would be a possible check. Or a set of repeated formulas where one is different from the others. I don't have a list of possible errors, it's still open to define them. Because this extension is not production quality we are still working on it, but we hit a problem that we can't avoid. If you interested I can send two version of it, one that works well with A1 reference modell another (patched by Kohei) works well with A1 and R1C1 notation but drops mystery reference errors. So that is why we did not uploaded to the extension repo. It would be best to have it available for everyone to take a look. But if that isn't possible, you can send it to me. Niklas - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org -- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] constraint of x in sin(x) in scaddins
Dear Regina, It is completely irrelevant how accurate the implementation is. The problem is that: sin(1) = a very accurate number sin(1 + pi) = (-1) * (the very accurate number from above) The difference between those 2 numbers is only pi! BUT: pi might be too small with those BIG numbers! If Calc aproximates 1 + pi as 1, because it cannot store (1 + pi) to the relevant digit, then you get bullshit and sinus cannot be computed accurately. Lets give another example: sin(10^16 * pi) = 0 sin(10^16 * pi + pi/2) = -1 However, if the accuracy of storing 10^16 * pi + pi/2 is less than pi/2, then the actual computed result will be anything between -1 and +1, without any resemblance to the truth. And it does NOT depend on the accuracy of the math library. It depends on how Calc stores the number: 10^16 * pi + pi/2. How many decimals are sotred? If it is inaccurate even to the first decimal or, even worse, the first digit (pi/2 ~ 1.57), then it does NOT make sense to compute the sinus of it. I hope this explains why there must be an upper limit (unlike monotone functions, where the value is continuously changing in a single direction, sinus is not a monotone function). Sincerely, Leonard Mada Original-Nachricht Datum: Sun, 09 May 2010 18:07:22 +0200 Von: Regina Henschel rb.hensc...@t-online.de An: dev@sc.openoffice.org Betreff: Re: [sc-dev] constraint of x in sin(x) in scaddins Hi Leonard, it seems, that I have not been clear enough. sin() is a function in the math library of C and C++ compilers and is nowadays accurate in double precision up to x2^64. To prevent larger arguments the special implementation ::rtl::math::sin() is used in OOo. But inside the implementation of IMSIN() in the module scaddins a constraint x134217728 is used. I only want to know, why this has been introduced. I doubt, that this restriction is still necessary. I know, that a lot of binary values give different results for sin(), although they are shown with the same decimal values in 15 digit precision (the highest precision Calc UI has). But that is not central point of my question. kind regards Regina Leonard Mada schrieb: Dear Regina, I believe that sin() should have as constraint the largest float (or integer) that fulfills the following condition: sin(max) = correct result + epsilon sin(max + lambda) = correct result + epsilon , where lambda is a float in the interval (0, pi/2). Let me explain this a little bit. sin: [any float] - [-1, 1] sin(pi/2) = 1 sin(pi/2 + pi/2) = 0 So basically, if we want to compute sin() at some precision, we have to guarantee that the number we compute the sinus of, can be accurately represented. Else, we risk a total fiasco, because sin(a_very_big_number) may correspond to any value in [-1, +1], as the last digits in a_very_big_number are not accurate. So, big_number +/- pi/2 may be represented as big_number, although the sinus would have different signs for those theoretically different numbers. Therefore, the number which still makes sense to compute the sinus off, should fulfill the requirements set above. We still need to determine what values lambda and epsilon take, but I hope that the general idea is clear. Basically, this also depends on the implementation of general numbers in the particular software (Calc in this example). Some may store 16 decimal digits, so 1.xxx * 10^16 is still ok to compute the sin() (or marginally so), some may store 32 digits, so 1.xxx * 10^32 would be fine in that case. I believe that lambda should be not bigger than pi/4, while epsilon is flexible in this case (e.g. 10^-6 is probably sufficient). Sincerely, Leonard Mada Original-Nachricht Datum: Sat, 08 May 2010 21:02:45 +0200 Von: Regina Henschelrb.hensc...@t-online.de An: Calc devdev@sc.openoffice.org Betreff: [sc-dev] constraint of x in sin(x) in scaddins Hi all, I have notice a constraint SinOverflow (which is x=134217728) in analysishelper.cxx in scaddins. What is the special reason for it? In other places with sin() the version ::rt::math::sin() is used to get a guard for overflow. That would result in constraint x=9,22337203685478E+018, which is much larger. If there is no special reason, should I replace it, while I work on the missing complex trigonometric functions? kind regards Regina - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org -- GRATIS für alle GMX-Mitglieder
Re: [sc-dev] constraint of x in sin(x) in scaddins
Minor correction:
sin(10^16 * pi + pi/2) = +1
Betreff: Re: [sc-dev] constraint of x in sin(x) in scaddins
Dear Regina,
It is completely irrelevant how accurate the implementation is.
The problem is that:
sin(1) = a very accurate number
sin(1 + pi) = (-1) * (the very accurate number from above)
The difference between those 2 numbers is only pi!
BUT:
pi might be too small with those BIG numbers!
If Calc aproximates 1 + pi as 1,
because it cannot store (1 + pi) to the relevant
digit, then you get bullshit and sinus cannot be computed accurately.
Lets give another example:
sin(10^16 * pi) = 0
sin(10^16 * pi + pi/2) = -1
However, if the accuracy of storing 10^16 * pi + pi/2 is less than pi/2,
then the actual computed result will be anything between -1 and +1,
without any resemblance to the truth. And it does NOT depend
on the accuracy of the math library. It depends on how Calc stores
the number: 10^16 * pi + pi/2.
How many decimals are sotred?
If it is inaccurate even to the first decimal or, even worse,
the first digit (pi/2 ~ 1.57), then it does NOT make sense
to compute the sinus of it.
I hope this explains why there must be an upper limit (unlike
monotone functions, where the value is continuously changing
in a single direction, sinus is not a monotone function).
Sincerely,
Leonard Mada
Original-Nachricht
Datum: Sun, 09 May 2010 18:07:22 +0200
Von: Regina Henschel rb.hensc...@t-online.de
An: dev@sc.openoffice.org
Betreff: Re: [sc-dev] constraint of x in sin(x) in scaddins
Hi Leonard,
it seems, that I have not been clear enough. sin() is a function in the
math library of C and C++ compilers and is nowadays accurate in double
precision up to x2^64. To prevent larger arguments the special
implementation ::rtl::math::sin() is used in OOo. But inside the
implementation of IMSIN() in the module scaddins a constraint
x134217728 is used. I only want to know, why this has been
introduced. I doubt, that this restriction is still necessary.
I know, that a lot of binary values give different results for sin(),
although they are shown with the same decimal values in 15 digit
precision (the highest precision Calc UI has). But that is not central
point of my question.
kind regards
Regina
Leonard Mada schrieb:
Dear Regina,
I believe that sin() should have as constraint the largest float
(or integer) that fulfills the following condition:
sin(max) = correct result + epsilon
sin(max + lambda) = correct result + epsilon ,
where lambda is a float in the interval (0, pi/2).
Let me explain this a little bit.
sin: [any float] - [-1, 1]
sin(pi/2) = 1
sin(pi/2 + pi/2) = 0
So basically, if we want to compute sin() at some precision,
we have to guarantee that the number we compute the sinus of,
can be accurately represented. Else, we risk a total fiasco, because
sin(a_very_big_number) may correspond to any value in [-1, +1],
as the last digits in a_very_big_number are not accurate.
So, big_number +/- pi/2 may be represented as big_number,
although the sinus would have different signs for those
theoretically different numbers.
Therefore, the number which still makes sense to compute the sinus
off,
should fulfill the requirements set above.
We still need to determine what values lambda and epsilon take,
but I hope that the general idea is clear.
Basically, this also depends on the implementation of general numbers
in the particular software (Calc in this example).
Some may store 16 decimal digits, so 1.xxx * 10^16 is still ok
to compute the sin() (or marginally so), some may store 32 digits,
so 1.xxx * 10^32 would be fine in that case.
I believe that lambda should be not bigger than pi/4,
while epsilon is flexible in this case (e.g. 10^-6 is probably
sufficient).
Sincerely,
Leonard Mada
Original-Nachricht
Datum: Sat, 08 May 2010 21:02:45 +0200
Von: Regina Henschelrb.hensc...@t-online.de
An: Calc devdev@sc.openoffice.org
Betreff: [sc-dev] constraint of x in sin(x) in scaddins
Hi all,
I have notice a constraint SinOverflow (which is x=134217728) in
analysishelper.cxx in scaddins. What is the special reason for it?
In other places with sin() the version ::rt::math::sin() is used to
get
a guard for overflow. That would result in constraint
x=9,22337203685478E+018, which is much larger.
If there is no special reason, should I replace it, while I work on
the
missing complex trigonometric functions?
kind regards
Regina
-
To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org
For additional commands, e-mail: dev-h
Re: [sc-dev] constraint of x in sin(x) in scaddins
Please take my apologies for the harsh words in my previous email. I will try to get a better explanation. There is a funny saying, like: Bullshit in, Bullshit Out. This is what happens with sin(). If the number you provide to the function is not accurate, and the precision is less than pi/2, then you end with a bogus output. The math library may provide 10,000 digits of precision, but all 10,000 digits are wrong. I made the following small experiment: ASSUMPTIONS: PI = 3; PI/2 = 1 [this is to avoid any errors due to floating point] 1) Open Calc 2) enter in A1: =3*10^16 3) enter in A2: =3*10^16 + 1 4) copy A1:A2 and paste as value in B1:B2 5) calculate the difference between B2 and B1, e.g. in cell B3: = B2 - B1 6) Voila, the result is 0 !!! So, Calc cannot differentiate between 3*10^16 and 3*10^16+1. But, the correct result in infinite precision would be: sin(3*10^16) = sin(pi * 10^16) = 0 sin(3*10^16 + 1) = sin(pi * 10^16 + pi/2) = 1 [we assumed pi to be 3 and pi/2 to be 1] However, Calc will compute: [reasoning done in our mind, not actual computations] sin(3*10^16) = sin(3*10^16 + 1) = 0!!! Because Calc sees both 3*10^16 and 3*10^16 + 1 as the same number. So, the math library may be accurate to the infinith digit, the result would be nevertheless wrong, because we provided the wrong number to sin(), and so the math library is not able to compute anything accurate out of the wrong input. We did not provide 3*10^16 + 1, but only 3*10^16, because Calc doe not know of 3*10^16 + 1. It does not know this number. This is a major problem with sin(), because the sine function is not monotonic, unlike other functions., and therefore we have BIG differences between numbers like 3*10^16 + 1 and 3*10^16, and those differences may be in any direction. So, lets get back to my earlier mails. In order to get sin() accurate to the 6th digit, we need to have the input precise to the 6th decimal digit. Because Calc stores numbers with 16 decimal digits, this means that the biggest number that retains a precision of 6 decimal points is 10^9 - 10^10. [16 digits is equivalent to 64 bits] So, in order to get an accurate sine from a number of the order of magnitude of 10^18, I still need 6 decimal places, so we actually need a precision of 24 digits, or ~24*4= ~96 bits (clearly more than 64 bits). I am sure, Calc does NOT store numbers as 96-bit values. I hope this explanation is better than my previous one. I get the feeling otherwise that my posts resemble more and more the dead parrot sketch. Sincerely, Leonard Mada Betreff: Re: [sc-dev] constraint of x in sin(x) in scaddins Dear Regina, It is completely irrelevant how accurate the implementation is. The problem is that: sin(1) = a very accurate number sin(1 + pi) = (-1) * (the very accurate number from above) The difference between those 2 numbers is only pi! BUT: pi might be too small with those BIG numbers! If Calc aproximates 1 + pi as 1, because it cannot store (1 + pi) to the relevant digit, then you get bullshit and sinus cannot be computed accurately. Lets give another example: sin(10^16 * pi) = 0 sin(10^16 * pi + pi/2) = +1 However, if the accuracy of storing 10^16 * pi + pi/2 is less than pi/2, then the actual computed result will be anything between -1 and +1, without any resemblance to the truth. And it does NOT depend on the accuracy of the math library. It depends on how Calc stores the number: 10^16 * pi + pi/2. How many decimals are sotred? If it is inaccurate even to the first decimal or, even worse, the first digit (pi/2 ~ 1.57), then it does NOT make sense to compute the sinus of it. I hope this explains why there must be an upper limit (unlike monotone functions, where the value is continuously changing in a single direction, sinus is not a monotone function). Sincerely, Leonard Mada Original-Nachricht Datum: Sun, 09 May 2010 18:07:22 +0200 Von: Regina Henschel rb.hensc...@t-online.de An: dev@sc.openoffice.org Betreff: Re: [sc-dev] constraint of x in sin(x) in scaddins Hi Leonard, it seems, that I have not been clear enough. sin() is a function in the math library of C and C++ compilers and is nowadays accurate in double precision up to x2^64. To prevent larger arguments the special implementation ::rtl::math::sin() is used in OOo. But inside the implementation of IMSIN() in the module scaddins a constraint x134217728 is used. I only want to know, why this has been introduced. I doubt, that this restriction is still necessary. I know, that a lot of binary values give different results for sin(), although they are shown with the same decimal values in 15 digit precision (the highest precision Calc UI has). But that is not central point of my question. kind regards Regina Leonard Mada schrieb: Dear Regina
Re: [sc-dev] constraint of x in sin(x) in scaddins
Dear Regina, I believe that sin() should have as constraint the largest float (or integer) that fulfills the following condition: sin(max) = correct result + epsilon sin(max + lambda) = correct result + epsilon , where lambda is a float in the interval (0, pi/2). Let me explain this a little bit. sin: [any float] - [-1, 1] sin(pi/2) = 1 sin(pi/2 + pi/2) = 0 So basically, if we want to compute sin() at some precision, we have to guarantee that the number we compute the sinus of, can be accurately represented. Else, we risk a total fiasco, because sin(a_very_big_number) may correspond to any value in [-1, +1], as the last digits in a_very_big_number are not accurate. So, big_number +/- pi/2 may be represented as big_number, although the sinus would have different signs for those theoretically different numbers. Therefore, the number which still makes sense to compute the sinus off, should fulfill the requirements set above. We still need to determine what values lambda and epsilon take, but I hope that the general idea is clear. Basically, this also depends on the implementation of general numbers in the particular software (Calc in this example). Some may store 16 decimal digits, so 1.xxx * 10^16 is still ok to compute the sin() (or marginally so), some may store 32 digits, so 1.xxx * 10^32 would be fine in that case. I believe that lambda should be not bigger than pi/4, while epsilon is flexible in this case (e.g. 10^-6 is probably sufficient). Sincerely, Leonard Mada Original-Nachricht Datum: Sat, 08 May 2010 21:02:45 +0200 Von: Regina Henschel rb.hensc...@t-online.de An: Calc dev dev@sc.openoffice.org Betreff: [sc-dev] constraint of x in sin(x) in scaddins Hi all, I have notice a constraint SinOverflow (which is x=134217728) in analysishelper.cxx in scaddins. What is the special reason for it? In other places with sin() the version ::rt::math::sin() is used to get a guard for overflow. That would result in constraint x=9,22337203685478E+018, which is much larger. If there is no special reason, should I replace it, while I work on the missing complex trigonometric functions? kind regards Regina - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org -- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
[sc-dev] Copying Conditional Formatting across spreadsheets
Dear Calc Team, I would like to easily copy the *Conditional Formatting* across different sheets. [see issue: http://www.openoffice.org/issues/show_bug.cgi?id=105659] A.) THE PROBLEM Lets suppose we want to do some useful, but more complex Conditional Formatting: [These examples are functional in Excel 2007; there is a significant shortcoming in OOo unrelated to this issue.] 1.) suppose we want to identify every *start* of a new series of records (rows) - suppose row 1 is the header row - suppose column A identifies distinct groups of records (e.g. vendors) VENDOR PRODUCT QTY ORACLE prod1 3 ORACLE prod2 5 IBM prod1 1 IBM prod1 6 - we select everything (except row 1), and enter the following conditional formatting, based on the formula: =IF($A1 = $A2; FALSE; TRUE) [as said, A1 was not selected] - IF a unique set is identified based on a number of columns, than we need slightly more complex formulas: =IF(CONCATENATE($A1; $B1; ...) = CONCATENATE($A2; $B2; ...); FALSE; TRUE) 2.) suppose we create a really complex conditional formatting schema [Actually, I think this is not possible yet in OOo, but I often do such work in Excel 2007.] 3.) suppose now that we want to apply this conditional formatting to multiple sheets and spreadsheets. B.) TASK I want to have this conditional formatting schema applied easily to different spreadsheets. C.) SOLUTION From my point of view, I would like something like CSS within HTML: separate the 2 layers: *Content* and *Conditional Formatting*. 1.) This probably needs ODF-changes, too: add a separate stream for conditional formatting. 2.) Allow exporting/importing the conditional formatting stream. This way, it becomes easy to clone the conditional formatting to different sheets, and to apply minor tweaks to this formatting. This would greatly enhance the conditional formatting experience. Many thanks, Leonard - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] Copying Conditional Formatting across spreadsheets
Unfortunately, this is not quite the same, and rather limited in possibilities. Also, it destroys ALL existing formatting, which we do NOT want to do. We want only the conditional formatting, e.g. to mark some critical data, or to highlight potential errors. I would rather want to program separately the conditional formatting - as a separate layer - and apply then this *Conditional Formatting Schema* to a spreadsheet. This is similarly to the various XML-schemes, I want to be able to write a schema and apply it to the spreadsheet. Lets say, I want an abstraction layer, and instead of $A1, $A2, $B1, ..., will use named ranges (once the versatility of conditional formatting is enhanced to support such advanced features). [This may need to enhance Calc with additional functions and Conditional Formatting features.] Then a programmer writes an abstract formatting schema. Then everyone reuses this schema everywhere, enterprise-wide. XPATH is already used for enterprise-wide schemes, so why not have something similar for spreadsheets and conditional formatting. Sincerely, Leonard Original-Nachricht Datum: Tue, 06 Oct 2009 21:28:22 +0200 Von: Regina Henschel rb.hensc...@t-online.de An: dev@sc.openoffice.org Betreff: Re: [sc-dev] Copying Conditional Formatting across spreadsheets Leonard Mada schrieb: Dear Calc Team, I would like to easily copy the *Conditional Formatting* across different sheets. [see issue: http://www.openoffice.org/issues/show_bug.cgi?id=105659] That is possible without problems. 1. Mark the cell in the source spreadsheet. 2. Copy to clipboard. 3. Goto the target spreadsheet. 4. Mark the cells, which should get the conditional formatting. 5. Menu Edit Paste special 6. Deselect all but Formats. OK The needed styles are copied too automatically. kind regards Regina - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3.5 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
[sc-dev] Re: [Issue 90759] ZTEST not same as Excel
Hello Eike, I might be too late, but the following small correction sounds slightly better: Calculates the probability of observing a z-statistic greater than the one computed based on *the* sample. Original-Nachricht Datum: 3 Sep 2009 12:34:44 - Von: e...@openoffice.org An: disco...@openoffice.org Betreff: [Issue 90759] ZTEST not same as Excel To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=90759 --- Additional comments from e...@openoffice.org Thu Sep 3 12:34:43 + 2009 --- I used this one now: Calculates the probability of observing a z-statistic greater than the one computed based on a sample. revision 275752 sc/source/ui/src/scfuncs.src - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] Issue 5658 solved
Hi Cor, So the CT2N extension still has its value. If it could additionally detect the origin of a #VALUE! error it would be a most useful tool to fix such broken documents. Eike I second that. If it could trace the string(s) that caused the error, that would be great. And of course, you need to (manually) change all strings to numbers, which again makes the extension very handy. Sincerely, Leonard Mada -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/atbrowser - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] Re: [Issue 90759] ZTEST not same as Excel
Dear Eike, dear Regina, I will try to explain the rationale behind the z-test. Unfortunately, the quirks behind its computation in spreadsheet software make it not that easy to describe. The assumptions of the z-test: - you have a random sample with a mean Xs - there is a population that follows a gaussian distribution with mean muP and variance sigma^2 - the question is whether this sample was drawn from this population - the statistical hypothesis are: H0: Xs = muP Ha: Xs != muP (2-tailed) The one tailed version Ha is: Xs either muP or Xs muP (only one of these) So, basically, what we are testing is the probability that the sample was taken from a population with mean muP. The z test will first compute the z-statistic, and then will infer the probability of H0 based on this z statistic. [There is a direct correspondence between z and the probability.] So, the 2-tailed version looks like: - if computed z is more extreme than a critical z0, then we have to reject H0 - else, we have to accept H0 More extreme means: either z -|z0| or z |z0|, where |...| is the absolute value; We compare 2 z values only when we talk about interpreting the z-statistic. Otherwise, we do not compare 2-values. The z-test simply gives us the probability, under the null hypothesis, to observe a z-statistic as extreme or more extreme than that calculated, or, as written on MathWorks: The p-value is the probability, under the null hypothesis, of observing a value as extreme or more extreme of the z test statistic... (slightly reworded) Or, still in other words: we obtain the probability to observe in a random sample from the given study population a z statistic as extreme as that calculated. [This is the meaning of the p-value.] This sounds good, and is easily understandable. The problem with the z-test implementation in spreadsheets (I infer the implementation details from previous posts, I did not test it specifically), is that a different probability is computed, namely: the probability of observing a z computed z for this sample. Statistically, this is the one-sided greater alternative. But this is not as easy to explain if you do not understand statistics. So basically, we compute the probability, under the null hypothesis, to observe a z-statistic greater than the one computed. Under the null hypothesis means to observe such a value by chance alone (aka randomly). The shortest definition that still makes some sense is: The probability of a z-statistic greater than the one computed. [where computed is based on the sample] I hope this sounds English enough, but unfortunately neither I am a native speaker. I would have welcomed some input from anyone speaking natively English. Sincerely, Leonard Mada A last note: I understand what was meant in the previous definition with a second sample, but I found that explanation very confusing, because we never take a 2nd sample. Also, the 2 samples are never compared. [A 2nd sample would also cause a lot of trouble because of 2 means and 2 distinct variances. The actual reasoning refers to H0 and goes like this: We draw a hypothetical random sample, and compute the probability to get a z-statistic as extreme or more extreme than that observed with our real sample. It is the probability of drawing such a sample, not of comparing 2 samples.] Original-Nachricht Datum: Wed, 26 Aug 2009 22:35:29 +0200 Von: Eike Rathke e...@sun.com An: dev@sc.openoffice.org Betreff: Re: [sc-dev] Re: [Issue 90759] ZTEST not same as Excel Hi Regina, On Wednesday, 2009-08-26 18:02:44 +0200, Regina Henschel wrote: calculates the probability of observing a value as large or larger for the z-statistic There is a comparison observing a value larger but it does not contain, to what it is compared. There must be something like observing a value larger than I think as large as... can be dropped, it makes no difference for a continuous distribution and the text becomes shorter. Is for the z-statistic an attribute to a value? I understand it so. Is it a typical sentence order in English to put it at the end? In German I would say Berechnet die Wahrscheinlichkeit einen Wert der Gauß-Statistik zu beobachten, der größer ist als der Wert der Gauß-Statistik der Stichprobe. But I'm not sure, Leonardo wants to say this. (Z-Statistik does not exist in German.) Translating that I'd get, hopefully correct: Calculates the probability of observing a value of the z-statistic larger than the value of the sample's z-statistic. Is that what we want to say? Describing the function using 'z-statistic' is indeed better than using a description with 'mean', because of the function name ZTEST. I agree. Eike -- OOo/SO Calc core developer. Number formatter stricken i18n transpositionizer. SunSign 0x87F8D412 : 2F58 5236 DB02 F335 8304 7D6C 65C9 F9B5 87F8 D412
[sc-dev] Re: [Issue 90759] ZTEST not same as Excel
Dear Calc team, the following wording is ambiguous and it may be wrong altogether: function description: calculates the probability of a *sample* mean greater than the mean of the given *sample*. [EMPHASIS ADDED] So, does it compute the probabilities based on 2 samples? I doubt it. I may go back to basics. Lets say we have a population (P) with a population mean Mu(P), and a sample X, with a sample mean mu(X). The statistical hypothesis are: H0: Mu(P) == mu(X) Ha: Mu(P) either '' or '' mu(X) [the one tailed version] Depending on the tail used, it will be '' or ''. The 2-tailed version is Mu(P) '!=' mu(X). I haven't followed the discussion recently, so I am unable to tell exactly what is computed. But I very much suspect that it compares mu(X) against a population mean Mu(P). Also, the phrase dose not specify what statistics is used. It might be obvious that the z-statistic is used, but I would rather specify it explicitly. There are a lot of different statistics out there. Sincerely, Leonard Mada Original-Nachricht Datum: 25 Aug 2009 07:20:41 - Von: drk...@openoffice.org An: disco...@openoffice.org Betreff: [Issue 90759] ZTEST not same as Excel To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=90759 --- Additional comments from drk...@openoffice.org Tue Aug 25 07:20:40 + 2009 --- Our proposal for the function wizard:- function description: calculates the probability of a sample mean greater than the mean of the given sample. first parameter The given sample, drawn from a normally distributed population second parameter The known mean of the population third parameter The known standard deviation of the population. If omitted, the standard deviation of the given sample is used. - Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] OOo2.4.2 takes of:=FDIST(a,b,c) as if it is ooc:=FDIST(a,b,c)
Dear Regina, this brings me to an older issue: http://www.openoffice.org/issues/show_bug.cgi?id=85328 It was suggested within that issue, that the cell was formatted as text, although I would have expected that anything starting with = is interpreted as a formula, while strings get marked as: '= I believe this is a more deeper problem within Calc that needs to be addressed. Sincerely, Leonard Mada -- Neu: GMX Doppel-FLAT mit Internet-Flatrate + Telefon-Flatrate für nur 19,99 Euro/mtl.!* http://portal.gmx.net/de/go/dsl02 - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] Need UI name for new FDIST function
Hi Regina, I would suggest DISTF(). It adds the benefit that all distribution functions could be grouped at some point under a DIST...() name. DISTGAMMA() DISTPOISSON() DISTGAUSS() (reluctantly DISTNORM) There could be a shorthand form: DISTF DISTG DISTP DISTN (it is for this that I would accept DISTNORM() ) Sincerely, Leonard Original-Nachricht Datum: Tue, 07 Apr 2009 22:48:43 +0200 Von: Regina Henschel rb.hensc...@t-online.de An: ux discuss disc...@ux.openoffice.org CC: Calc dev dev@sc.openoffice.org Betreff: [sc-dev] Need UI name for new FDIST function Hi all, (CC dev@sc.openoffice.org) with OpenFormula v1.2 we will get a new FDIST function. The current FDIST function calculates the _right_ tail cumulative distribution function of the F-distribution. The new function will calculate the _left_ tail cumulative distribution function and the density function in addition and will have an optional parameter to distinguish between cumulative and density. The old function is called LEGACY.FDIST and the new one is called FDIST internally. I'm going to implement the new function and need an UI-name for the new function. I think, it would be very confusing to use the internal names in the UI too, because than we would have different meanings of FDIST in OOo3.1 and OOo3.2. I suggest to use FDISTLEFT. But perhaps there is a spreadsheet application that has got this left tailed version already? EXCEL 2007 and Gnumeric 1.6.3 do not know this function. Any suggestions? kind regards Regina - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01 - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
Re: [sc-dev] Automatic date recognition
Hello Stefan, Stefan Weigel wrote: Hi Leonard, Leonard Mada schrieb: it should work with: http://loinc.org/downloads See the link with title: LOINC Table File - ASCII Text Format (2.9 MB). I got it. I can´t see any problem. Did you set the data type of the columns in the import options to text? When opening a 54,000+ rows csv-file the user does NOT usually browse through the table to see if there will be problems and does NOT usually chang row-formattings [please also notice that there is a clearly text-header at the beginning of the file]. The user just expects that everything works fine. That was my expectation, too. After importing the document, I made specific changes (it is unlikely that any lab/health system will use all the LOINC-defined codes), and customized the data even further and used that data subsequently. Only after experiencing strange behaviour did I start to investigate what went wrong and only after a lot of debugging did I notice the culprit. How many hidden errors arise because of such automatic formatting? I presume no one knows for surely, but it is probably much higher than permissible. Sincerely, Leonard Mada [By the way, I have installed OOo in the en-locale on Win2k SP4 en-locale.] Stefan - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
[sc-dev] Automatic date recognition
Dear developers, this issue slowly drives me crazy. Unfortunately it is after *much work* that I discover again and again that OOo Calc transforms perfectly valid code string into dates, like in the example below. These are international lab-codes from the LOINC database, but upon importing Calc decies to transform them into DATES. Manually verifying 54,000 rows for OOo quirks is NOT going to be practical. I still have some hope that Calc will once and for all remove the automatic date identification. Sincerely, Leonard LOINC table from: http://loinc.org/downloads/files/loinc-table-ascii-text-format/gotoCopyrightedFile Imported in Calc (after sorting and a lot of headache to find what was wrong): LOINC_NUM 01/08/09 08/03/09 11/07/09 03/04/09 05/09/09 12/05/09 06/07/09 02/06/09 09/01/09 10/09/09 04/02/09 07/05/09 13-3 [...] - To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org For additional commands, e-mail: dev-h...@sc.openoffice.org
[sc-dev] Re: Data Grid Size element proposal
Dear TC Members, while reading the discussion, I have to express my dismay to the proposal and more broadly to the various spreadsheet concepts. It seems little has been learned from past mistakes. Warren Turkal wrote: [...] This doesn't seem useful to me. If I never address anything in those cells that are outside my range, I'd say it's pretty likely I don't care about them, and just knowing the max row/col size of the saving application doesn't tell you that. [see http://lists.oasis-open.org/archives/office/200811/msg00138.html] I must strongly back up Warren on this one. Lets say it this way: suppose I have a spreadsheet program WITHOUT any grid-size limit (because it implements a very clever iterative mechanism - making it virtually infinitely wide). I create a 2 columns by 2 rows spreadsheet. BUT this one is virtually non-openable in any other program! So, basically THIS spreadsheet program, WHILE FULLY ODF-CONFORMANT, does create spreadsheets that are NOT READABLE by ANY other application. So, why the fuss with ODF than? This is BAD standard design, depending on an implementation detail. Lets move further. Putting the constraints on the application is definitely wrong. Even if an application supports 1E+30 rows, it is still MOST likely that 90% of users will use less than 10,000 rows, and that 99% of them will use less than 30,000 rows. So, WHY put a MAX_GRID_SIZE at 1E+30 then? A different measure is useful! And Apple solved this nicely in Numbers. What is needed is the ACTUAL size of the current spreadsheet. If there are only 2 rows by 2 columns, than THIS is needed. If the user adds more rows/columns, than the spreadsheet program shall automatically increment as necessary (this is a nice feature in Numbers). We don't have any problems with named ranges and general row/column references anymore. WE have now a portable value - and a clearly defined one. Referencing a value outside this range shall generate an error and NOT silently accept 0, if it falls within the MAX_GRID_SIZE. This is currently a design error. I have yet to see such a use that is not an error, and I have enough auditing experience to say this. If the user really needs this (which I really doubt), he will notice the error and explicitly write something on the corresponding row or column (to define that row or column, e.g. a label). This is much more transparent and error-proof. Eike wrote: see http://lists.oasis-open.org/archives/office/200811/msg00155.html Now if this file was loaded in an application that supported more columns without knowing the original maximum grid size, the formula in A2 would yield 0 because the range wrapped pointing to then empty cells. What applications actually can or should do about this is beyond the scope of the file format standard, nevertheless it seems to be a good idea to include the hint of the original grid size. This is again wrong logic. The application does NOT want to know the MAX_GRID_SIZE, BUT the *actual spreadsheet size*! So IF the TC is clever it will follow Apple's approach, and limit the spreadsheet size to its actual size, and not limit the application. [By the way, I consider Numbers much better than all other spreadsheets combined, and I feel Apple is brewing even more things.] Sincerely, Leonard P.S. Please move the discussion to [EMAIL PROTECTED] or cc me directly in case of a relevant response. Thank you. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Return 0 on Search Instead of Zero
Hello Eike, Eike Rathke wrote: Hi Leonard, I just noticed the following formula in one of the issues: =IF(AND((ISERROR(SEARCH(Pass,$F8))), (ISERROR(SEARCH(Warn,$F8))), (ISERROR(SEARCH(Closed,$F8,0,1) IIRC we already mentioned the to be implemented IFERROR() function in this context somewhere. Well, I will let you simplify this formula using IFERROR(). However, I sincerely hope that implementing my proposal will take less time than simplifying the above formula using IFERROR(). ;-) And take my hint, there might be a remote chance that you will fail. Lets assume now that SEARCH() will return 0 if the string is NOT found. Then this whole formula ends up as: =IF(OR(SEARCH(Pass,$F8); SEARCH(Warn,$F8); SEARCH(Closed,$F8)); 1; 0) We got rid of 3 functions and a lot of additional brackets. Now, this is a simplification. The only way to go even further, is to implement arrays within SEARCH(), but that's another story. I'll be back with more comments when I have some more time available. Sincerely, Leonard And this only because SEARCH() returns an ERROR if the string is not found. [http://www.openoffice.org/issues/show_bug.cgi?id=91146] That issue has a different topic. How many examples should I provide to get the developers implement an alternative Search function that returns 0 IF the string is not found. I don't think it is a matter of the amount of examples. Usually no issue is. Actually the process would be: 1. Propose the new function to the OASIS formula subcommittee, using the comments form at OASIS. You know where it is. 2. Wait until the proposal is discussed and accepted. 3. Find someone who will implement it. But, once IFERROR() was implemented, a different SEARCH() would only be necessary to distinguish between not found and propagated error. So for the overall enhancement it may be better to skip steps #1 and #2 and start with #3 to implement IFERROR(), as that may be useful in all context where an expression may return an error. The explanation that this is different than Excel is strange considering issue 5658, which is definitely a more severe inconsistency than this one [http://www.openoffice.org/issues/show_bug.cgi?id=5658]. Nice to throw in an unrelated but one of the most controversially discussed issues with the most useless comments we have :-/ This really does not help. Eike Issue 5658 is the single most severe issue regarding interoperability with MS Excel. Anything else can be circumvented, because people will know that something went wrong when they see some ERROR popping up. And correcting an error is possible once you spot it. But issue 5658 is different, because the user will never suspect that something went wrong. Please accept my expertise, I have seen many things. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Return 0 on Search Instead of Zero was Informing users about ODFF was [sc-dev] Rebasing CWS odff05
Hello everyone, I just noticed the following formula in one of the issues: =IF(AND((ISERROR(SEARCH(Pass,$F8))), (ISERROR(SEARCH(Warn,$F8))), (ISERROR(SEARCH(Closed,$F8,0,1) And this only because SEARCH() returns an ERROR if the string is not found. [http://www.openoffice.org/issues/show_bug.cgi?id=91146] How many examples should I provide to get the developers implement an alternative Search function that returns 0 IF the string is not found. The explanation that this is different than Excel is strange considering issue 5658, which is definitely a more severe inconsistency than this one [http://www.openoffice.org/issues/show_bug.cgi?id=5658]. I can live even with a different name for the function, but forcing users (which are often unsuspecting of better alternatives) to use such complex and error prone formulas is rather strange. Sincerely, Leonard Cor Nouws wrote: Hi Eike, Eike Rathke wrote (26-11-2008 19:50) On Tuesday, 2008-11-18 17:31:03 +0100, Eike Rathke wrote: I started migrating CWS odff05 from CVS (currently resynced to m31 there) to SVN, and then will rebase it to m36 when available. I'll give you a heads up when the CWS will be ready for further development. m36 took longer than I thought it would, but now it's ready and CWS odff05 is rebased to m36. Compiled fine on Linux/x86 and Solaris/SPARC, Daniel is currently building Windows. I thought (ignorance ;-) ) that odff implementation was finished in 3.0 Now I see this mail and in eis: http://tools.services.openoffice.org/EIS2/cws.ShowCWS?logon=trueId=7310Path=DEV300%2Fodff05 As you know, I've done some work to inform Dutch users about the changes coming with odff. It is however not mentioned on http://www.openoffice.org/dev_docs/features/3.0/ ... And now I understand there will be more. Can you pls tell what you expect about features / time line / impact for users? Then I it is time for the marketing documentation project, to prepare better info for the users. Thanks, Cor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Formulas interpreted as text
Hello, Regina Henschel wrote: Hi Leonard, Leonard Mada schrieb: Dear develoeprs, Please have a look at iisue 85328: http://www.openoffice.org/issues/show_bug.cgi?id=85328 Formulas entered into column A will be interpreted as text. This is a very strange behaviour. I do not have the underlying file that started the problem and I did not encounter this problem any more, BUT it might point to some Calc underlying problem. Please note that the problem persists after resaving. So, I would like to have a developer look into the troubled file to exclude any serious flaw/error. People tend to hastily close the issue as WONTFIX. The whole column A is set to cell format text. Indeed. Though I was sure that Auto-number recognition and Auto-formula recognition has always precedence. Strange. Also, the formula autocompletion is activated, although the resulting entry is a text string. Even entering a function using the fx button keeps the resulting entry as a text string, and doesn't evaluate to a function. I would say that this last feature is definitely a bug. Also, the missing apostrophe at the beginning of the text did mislead me. Also, changing the cell to a different type, does NOT evaluate the formula. It will be still displayed as =..., without the apostrophe in the beginning (see cell A78). This clearly misleads one. And this makes Calc very inconsistent: SUM() will evaluate these text-numbers!!! But it doesn't evaluate numbers entered as '1. Therefore, is the 1 entered as text now text or number? Sincerely, Leonard Therefore, when you enter or alter cell content it will be treated as text. Only cell content that was already inside the cells when you have changed the format of the column, while preserve its own format - as long as you do not alter it. For me it is a worksforme. Kind regards Regina - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] z-Test
Hi Regina, What did you decide with the z-test? I lost slightly the track. I recommended extending the t-test. And I still believe this is the better solution. While for z-test, you may want to implement another parameter specifying the number of tails: lesser, greater or two-tailed So, older spreadsheets could be imported using the new function by setting the appropriate tails (I believe the tails was the problem of the original function - but did not test this assertion). Then, there would be only a problem when saving a new document in an old format. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Re: What should ZTEST calculate?
Hi Regina, I believe this discussion fits better the sc-mailing list. I will describe below, how I would handle this issue. I would not dwell deep into the current z-implementation. The z-test is very limited. So, I would actually want to extend the t-test to cover one group of data. Calc fares currently bad, as it offers a semi-robust test only for 2 groups of data. So, I would redirect all efforts to extend Calc's capabilities both to 1 group of data and to 2 groups of data. Step 1: Extend the t-test to accept a single group of data. =TTEST( range , number , tails = 2 , variance = NULL ) Compares the mean of the data group range to the value number, using x-tails, and assuming the variance is equal to the variance of range. tails = 2: 2-tailed (alternative: two.tailed) tails = 1: one tailed less (alt: less) tails = 3: one tailed greater (alt: greater) optional parameter variance = NULL: use the variance of range variance = number, use this variance instead Step 2: Implement ANOVA to cover 2 or more groups of data. I posted some c++ code to issue 4921, see http://www.openoffice.org/issues/show_bug.cgi?id=4921 [It implements only the simple one-way ANOVA, skipping the block-design.] =ANOVA( range , design = 1) # EVERY column = one set of data =ANOVA( range1 , range2 , ... , design = 1 ) # EVERY RANGE = one set of data design = 1: one way ANOVA design = 2: two way ANOVA (factorial block design) design = 3: two way ANOVA (randomized block design) See also http://www.statmethods.net/stats/anova.html Hi Leonard, Leonard Mada schrieb: [...] I have found one discussion in http://lists.oasis-open.org/archives/office-formula/200702/msg00047.html and Eike reminds on it in http://lists.oasis-open.org/archives/office-formula/200806/msg00050.html But the spec has still a red ToDo in that place. See below. The z-test is a simplified t-test. So, for groups larger than 30 values, it should be quite close to the t-test. The first thing to strike you is the fact that you can't use in Calc the z-test or the t-test simultanously. This is because, in Calc (I don't know of Excel), the t-test works ONLY on 2 groups of data, while the z-test works on a SINGLE group of data. This is a design flaw in the statistics engine. I do not see any attempt to change that, not even an issue. BOTH tests should work both on a single group of data, and on 2 groups of data (while the ANOVA works on 2 or more groups of data). This is a MAJOR shortcoming of Calc. You can't use a somewhat more robust test (t-test) to compare a single group of data against a reference value. For less than 30 values, the t-test is preferred, and actually is the only test in R (there is a special package that has the z-test implemented for teaching purposes, I forgot the name but Google will probably get it). You can use more than 30 values and compute the t-test in R. It should yield the same results as the z-test, e.g.: x-rnorm(30) t.test(x, mu = 0.5) I don't have R. I have only got Excel and Gnumeric. R is open source. Google for R, or go to http://cran.R-project.org, and you can get R. It runs under almost every platform (support for Win9x was dropped in the latest R, but I can confirm that it runs on Win2k). Be warned, the learning curve is steep. Basics: Creating a vector: x- c( number1, number2 , ... ) 30 random numbers: x- rnorm(30) t.test: t.test( vector1 , vector2 ) # two.sided t.test( vector1 , vector2 , less ) # one sided, less t.test( vector1 , vector2 , greater ) # one sided, greater t.test ( vector , mu = number ) # one group of data # don't forget to write the string 'mu=' # less and greater apply similarly There is also a z-test available in package 'TeachingDemos' (you need to download first this package), see: http://rss.acs.unt.edu/Rdoc/library/TeachingDemos/html/z.test.html Sincerely, Leonard In this instance, we compare the mean of the sample x against another mean mu = 0.5 (don't forget the 'mu', otherwise you get an error). If the z-test in Calc gives a different result, then it is wrong. It would be nice to get a test spreadsheet with dummy data and the results which R returns. As with t-test, z-test can be one-sided or 2-sided, but the standard should be 2-sided. In the spec it is now 2-sided. I hope this helps. Not really. When we will implement ZTest in the 2-sided way, as it is now defined in the spec, than it would differ from the current behavior. Therefore going to ODF1.2 there will be a new ZTEST which gives other results than the old one. How should Calc handle this? Or should we try to get OASIS to define a 1-sided way? But even than it would be different from now, because the 1-sided way is not correct implemented in Excel and Calc; at least I understand the comments on the mailing list in that way. kind regards Regina - To unsubscribe, e-mail: [EMAIL PROTECTED
[sc-dev] Please add CSV-export to Export-Dialog
Dear developers, Please add the CSV-format to the Export-Dialog as suggested in issue 32684. http://www.openoffice.org/issues/show_bug.cgi?id=32684 After a long absence from the statistical horizon (I basically did not have any scheduled work with advanced statistics over the last 2 years), I began over the past 2-3 weeks to dwell again in statistics. There are obvious reasons why I do all my work in R, so I need to export a lot of csv-files. Unfortunately, there is NO such option in Calc. You can only 'SAVE' as csv, which is nonsense. As the csv is really an intermediary transport file used to transfer *one sheet* of data from the spreadsheet to another program, I strongly support adding the CSV-format to the EXPORT-possibilities. The csv can't support formatting nor formulas, BUT most importantly, only the *active sheet* is exported, so it is really ONLY an export, NOT a SAVE AS-functionality. I therefore strongly suggest to add this option to the EXPORT-dialog. This has some practical consequences, because it is impossible to perform any sound statistics in Calc. At the same time, the ODF-format has become too complex to implement accurate file-readers (that mimic all its bugs and limitations) in professional statistic packages. It is therefore imperative to use a simple format for data-transfer, and csv is simply the best suited one (tab-delimited data is another one). I therefore strongly hope that this enhancement gets implemented in Calc. Basically, the export-dialog should have both PDF and CSV as options. Selecting CSV should export the active sheet, using the sheet-name as default name. The status of the ods-file should not change (i.e. do not set changed status). That's it. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Filling dates in Calc
Hello Niklas, I will present a more global solution addressing the FILL on the OASIS list. Unfortunately, I am rather limited in free time, but I still try to do it over the weekend. Niklas Nebel wrote: On 10/24/08 12:35, Leonard Mada wrote: Oh, I forgot to press shift. Though, selecting 2 cells with the same entry should have worked as well. I actually feel that automatic date increment is less useful (because one does not have control on what changes), so that keeping the date constant, when 2 cells are selected is the far better alternative. Two equal dates are already recognized as simple fill. What do you mean as simple fill? If I select 2 or more equal dates, and try to fill, the date still gets incremented. This is not the case with ordinary numbers. This is the reason, I supposed that the fill is broken (and basically it is, because it doesn't work as for simple numbers). Strangely, filling using the menu works as expected (though you can't trigger increment on). I do not see any use case to select 2 or more equal dates and to expect that filling will increment these values (especially the day in the date). In the rare instance that something like this is desired, let the user activate increment using CTRL. [I made a mistake in my previous post, it is CTRL, not SHIFT.] By the way, CTRL doesn't trigger INCREMENT, when used on simple numbers. And there is no way to do an increment, if selecting 2 equal numbers, a much more common scenario than incrementing the day when selecting 2 or more equal dates. Sincerely, Leonard But simple fill always uses the Ctrl key to enable/disable the increment. This is done the same way for all kinds of source data, to keep consistent. Niklas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Filling dates in Calc
Oh, I forgot to press shift. Though, selecting 2 cells with the same entry should have worked as well. I actually feel that automatic date increment is less useful (because one does not have control on what changes), so that keeping the date constant, when 2 cells are selected is the far better alternative. Sincerely, Leonard Leonard Mada wrote: Hello everyone, I worked late last night and needed to fill in dates in Calc. Actually I needed to fill the same date in a variable number of cells. I entered the same date in 2 adjacent cells and tried to drag the right corner (after selecting both cells), but Calc continued to increment the date in pairs of 2. Is this bug already reported? A quick search on bugzilla did not yield any results. Using the Menu entry fills as expected, but this is cumbersome. Why shouldn't this work in a single step? I need to drag manually because I do not fill the whole column. I am using OOo dev300-m29. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Filling dates in Calc
Hello everyone, I worked late last night and needed to fill in dates in Calc. Actually I needed to fill the same date in a variable number of cells. I entered the same date in 2 adjacent cells and tried to drag the right corner (after selecting both cells), but Calc continued to increment the date in pairs of 2. Is this bug already reported? A quick search on bugzilla did not yield any results. Using the Menu entry fills as expected, but this is cumbersome. Why shouldn't this work in a single step? I need to drag manually because I do not fill the whole column. I am using OOo dev300-m29. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] =CELL(Width;A1) does not recognize changes of column width ?
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
Re: [sc-dev] ODFF_Implementation/Schedule Table Style
Hello Eike, I sent an e-mail to Chris Schlaeger. I don't have a response yet, and working with TaskJuggler on Windows under Cygwin is much too painful (NO GUI), so it seems I'll have to pass this up. Sincerely, Leonard Eike Rathke wrote: Hi Leonard, On Friday, 2008-10-10 22:19:04 +0200, Leonard Mada wrote: I made some changes to the following page, basically changing the background-color for alternating rows: http://wiki.services.openoffice.org/wiki/Calc/ODFF_Implementation/Schedule It surely looks better and easier readable than before. However, I noticed the explanatory text: *do not edit as your changes would get overwritten*. Yes, they will. Can this table-style be implemented somewhere upstream? As the page mentions it is generated from TaskJuggler via Html2Wiki, if you find a way to hook in let me know. TJ2 does not provide means, AFAIK, only TJ3 will. Eike - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] ODFF_Implementation/Schedule Table Style
Hi everyone, I made some changes to the following page, basically changing the background-color for alternating rows: http://wiki.services.openoffice.org/wiki/Calc/ODFF_Implementation/Schedule However, I noticed the explanatory text: *do not edit as your changes would get overwritten*. Can this table-style be implemented somewhere upstream? The table looks definitely better using alternate colours. I hope everyone agrees. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Ugly DATE bug
Dear developers, when are you going to fix the DATE-autoformat BUG? I have a highly official document with various statistics split on age groups and this is what I get: 1 01/04/08 05/09/08 10/14/08 15-19 20-24 25-34 35-44 45-54 55-64 65-74 75-84 85 I hope that anyone agrees that these are official age groups, and I can't select a different number only because Calc does not properly handle strings. As these statistics are generated automatically, I can't simply go through dozens of reports and format the cells as string. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Regular Expressions in FIND()?
Hello David, Indeed, that did the trick. Thank you very much. There is NO mention in the Help that comes with OOo-m29 that FIND() does not search for regular expressions, and I was fairly convinced that it actually works. Moreover, I had the firm impression that the error lies within the negation ( [^something] ) and did not even check a simple regexp. The only trouble I have with the new formula is that the already ugly formula got another 4 chars wider (making at 20 formulas another 80 chars). I desperately hope that the #VALUE! error gets fixed. ;-) Sincerely, Leonard David King wrote: does Calc permit regular expressions in the FIND() function? I think FIND doesn't, SEARCH does http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Re gular_Expressions_in_Calc#Regular_expressions_in_Calc_functions David - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Number Autoconvert
Hello Niklas, Niklas Nebel wrote: On 09/02/08 23:10, Leonard Mada wrote: try to paste this table into Calc: http://www.rush.edu/webapps/rml/RMLRangesHem.jsp How do I turn *Date recognition* off?! I have yet to see something as 5-16 being a date! Depending on the browser you use, you may be able to paste unformatted text, where you can select column types. Otherwise, the issue number is 33723. Niklas Indeed, I completely forgot about pasting unformatted text (it was by the way quite late, like always). Though, I still find it loony that OOo Calc tries to interpret something as 5-16 as a date. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Number Autoconvert
Dear all, try to paste this table into Calc: http://www.rush.edu/webapps/rml/RMLRangesHem.jsp How do I turn *Date recognition* off?! I have yet to see something as 5-16 being a date! Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Calc Very Slow
Hello Eike, I did install OOo-dev m29 and this version is clearly more responsive. I opened the same files, SeaMonkey is active again, and the Internet is also on the point of continously crashing, so basically same conditions, but the Menu pop ups fast enough, and scrolling is also more fluent (processsor speed jumps up to 92%, but this is probably an MS Win2k issue). With WinAmp paused, it is quite OK. Though I noticed something else: when a row is selected, the scrolling speed drops substantially. I cannot accurately quantify, but it is clearly visible (and I would say it is a couple of times lower). So overall, I would say m29 is faster. If the previously failed install was the culprit, I have no idea. Even so, it seems a good idea to improve the speed even further. It bothers me that the row selection has such a dramatic effect. Sincerely, Leonard Eike Rathke wrote: Hi Leonard, On Monday, 2008-08-11 23:12:36 +0300, Leonard Mada wrote: Also I noticed that most of the 2,700 entries are redlined as unknown text (the words are medical terms so are not found in the dictionary). That may make a difference. Try disabling AutoSpellcheck for that document. Eike - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Calc Very Slow
Hi Niklas, I must say the trend is similar today. I do have Winamp playing (but an older version, did not change it in a lot of time) and a Seamonkey session. Nothing spectacular. I do have open only 2 documents: a native ods and an xls. Not very complex. Can't be the formulas, the documents are more text-tables. Calc was always slightly slower on this computer, but I perceive it even slower now. In particular, it takes 1-2s for the context-menu to appear, when it should pop up instantly. Also, scrolling is painfully slow. CPU cycles in task manager jump to 42-44% with Winamp playing and to over 70% with Winamp paused just by scrolling (and sometimes up to 81%). I feel the bottleneck is somewhere else and it does not have anything with formulas to do. [By the way, the first time I had this feeling was when I tested that awful document with a lot of medians and percentiles and the formula recalculation did complete, but it took longer to adapt the row heights and the like.] Memory consumption is 94,400 KB, well below my physical RAM. SeaMonkey uses another 164,600 KB. Maybe I am just easier to upset during the last days, though improving the speed would be highly welcomed. Sincerely, Leonard Niklas Nebel wrote: On 08/10/08 23:41, Leonard Mada wrote: Hello everyone, Calc has become very slow on my computer. My system is an older one: processor 1 GHz with 1 GB RAM. Win2k SP4 Calc-dev m28 is very slow. I noticed it already after installing it some 2-3 weeks ago. When working with a spreadsheet with ~2.500 rows (4 columns, 2 text, the other 2 containing very simple formulas: length of text string in one, and a simple IF() to test if previous string is identical to current string): - during scrolling, processor activity jumps up to 50% - during right-click on row, processor jumps from 00-02% up to 24% it takes up to 2s for context-menu I feel this is substantially more slow than previous versions of Calc. Is there any reason? I don't see such behavior. Maybe there is something special about your formulas? Or do you have any unusual tools running on your computer? Niklas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Calc Very Slow
Hi cor, Cor Nouws wrote: Leonard Mada wrote (11-8-2008 21:58) [...] Calc was always slightly slower on this computer, but I perceive it even slower now. In particular, it takes 1-2s for the context-menu to appear, when it should pop up instantly. Also, scrolling is painfully slow. [...] Happen to have more extension installed? (Remember an issue with that, but OTOH think it should have been solved in the mean time ...) Indeed, forgot about that. Not many though: Writer's Tools and CT2N and a few others, but it seems they are malfunctioning and Calc just terminated unexpectedly when I hit the update button, without any Crash Report. I did had some trouble when installing this version [I firstly uninstalled previous Calc versions and made a fresh install, without deleting though the directory and some script errors pop continuously up when starting OOo.]. I will have to investigate this more thoroughly. Also I noticed that most of the 2,700 entries are redlined as unknown text (the words are medical terms so are not found in the dictionary). Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Calc Very Slow
Hello everyone, I deinstalled Cor's extension and reinstalled it (v1.0.1), but the extension continuous to be marked as status unknown. When I click the update button in the extension manager, Calc crashes with: C++ Runtime error. This application demanded the runtime to terminate it in an unusual way (something along these lines). No Crash Report window pops up. Is this known? I do not think it has to do with the extensions. Sincerely, Leonard Leonard Mada wrote: Hi cor, Cor Nouws wrote: Leonard Mada wrote (11-8-2008 21:58) [...] Calc was always slightly slower on this computer, but I perceive it even slower now. In particular, it takes 1-2s for the context-menu to appear, when it should pop up instantly. Also, scrolling is painfully slow. [...] Happen to have more extension installed? (Remember an issue with that, but OTOH think it should have been solved in the mean time ...) Indeed, forgot about that. Not many though: Writer's Tools and CT2N and a few others, but it seems they are malfunctioning and Calc just terminated unexpectedly when I hit the update button, without any Crash Report. I did had some trouble when installing this version [I firstly uninstalled previous Calc versions and made a fresh install, without deleting though the directory and some script errors pop continuously up when starting OOo.]. I will have to investigate this more thoroughly. Also I noticed that most of the 2,700 entries are redlined as unknown text (the words are medical terms so are not found in the dictionary). Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Calc Autoformatting Error
Hello everyone, I try to enter in a Calc cell: Diabetes mellitus Typ IIb but the last part gets converted automatically to Typ Iib. How do I undo this? This is obvious a serious bug. Why does Calc autoformat something it is NOT supposed to do? Tested with OOo-dev m28. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Calc Very Slow
Hello everyone, Calc has become very slow on my computer. My system is an older one: processor 1 GHz with 1 GB RAM. Win2k SP4 Calc-dev m28 is very slow. I noticed it already after installing it some 2-3 weeks ago. When working with a spreadsheet with ~2.500 rows (4 columns, 2 text, the other 2 containing very simple formulas: length of text string in one, and a simple IF() to test if previous string is identical to current string): - during scrolling, processor activity jumps up to 50% - during right-click on row, processor jumps from 00-02% up to 24% it takes up to 2s for context-menu I feel this is substantially more slow than previous versions of Calc. Is there any reason? Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Issue for #VALUE! on Excel import ?
Hello everyone, Eike Rathke wrote: Clear that an operator treats text numbers different than a function. Actually that's not a difference between operators and functions, but a difference between operators or functions expecting a scalar value as argument, such as the result of a single cell reference, and a cell or range reference that results in a number sequence. To illustrate Excel behavior: A1: '-3 A2: 4 =ABS(A1)= 3 =SUM(A1:A2) = 4 =SUM(A1,A2) = 4 =A1+A2 = 1 Eike And this is what I hate in Excel. The 2 sensible options are: - raise an error BUT then implement a mechanism to detect these string-cells and easily convert them to numbers - 2nd option: less adequate, but sometimes needed: interpret everything as number, in every function The bigger problem is however different: *Why* do people write numbers as strings? This question is relatively easy to answer. By a large margin, strings get used because the spreadsheet reformats their input in a way they do NOT want it. Auto-detection algorithms (especially for date) play here a role, but also sometimes a trivial formatting may trigger the user to use a string instead of a number. Fixing these issues would largely prevent the use of strings as numbers (of course not in the competitor's applications, so one should still support the 2nd option and the first one). Also, the first option is only feasible IF this bigger problem is fixed, because otherwise the user can't fix his number problem. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] development status of BETADIST
Hello Eike, all these tricks are applicable under slightly different conditions. I am no expert and can't tell when to apply the first one and when the second one. That's the domain of numerical analysts. Sometimes it is even not important to be accurate during intermediary operations, e.g.: IF we compute both x and y slightly inaccurate, but the inaccuracy is similar, then we may well compute (x - y) quite accurate [or (x/y) accurate]. However, for this trick to work, one needs to know to optimise the x-calculations and the y-calculations in such a way, that their inaccuracy cancels out (that the 2 partial results are *similarly* inaccurate in the *same direction*). This is actually what numerical analysts do most of the time to increase the accuracy of a calculation. Another feature I noticed in the R-code: - it consistently avoids combining terms of the form (x) and (1-x), because inaccuracies would not cancel out - rather, it re-groups terms so that to always have either x or 1-x [or equivalent variables] - e.g.: a - (a + b) * x - and calculate all terms like this, so that inaccuracies will be equivalent [and cancel later out] There are surely many other tricks that I do not understand. Eike Rathke wrote: Hi Leonard, On Wednesday, 2008-08-06 00:47:00 +0300, Leonard Mada wrote: It seems to be one of the most difficult functions. To get an idea of that function, have a look at the file toms708.c of the R-project. There is some hidden beauty in that file. ;) Without being of much help, I just want to draw attention to some features I spotted during a peak in the code: - often, (1-x) is decomposed into more stable terms, - from the simple 0.5 - x -0.5 [and x - 0.5 - 0.5 (for x-1)], - to more complex terms like: return w * (0.5 - 1.0 / w + 0.5); [this is actually w - 1] Indeed, nice tricks. Though the second one also already has some cancellation error for w=0.9 and the result (in C/C++) bitwise is not identical to w-1, but that's after the 20th decimal digit in this case, neglectable. The code was well written by numerical analysts, and optimised for accuracy over a large domain of values. Is it feasible to use this library Unfortunately not, R is licensed under GPL. In the meantime, I think that this licensing issue is one of the biggest impediments in open-source. I still think that finding a proper library is probably the better way. Sincerely, Leonard or a similar library (if one is found)? The license would have to be compatible with LGPLv3 and pass Sun Legal department. Rewriting this code isn't going to be easy. I'd say for a non-totally-expert on that field a true rewrite is nearly impossible. Eike - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Statistical Articles
Hello everyone, I recently stumbled upon 2 potentially interesting articles: On the accuracy of statistical procedures in Microsoft Excel 2007 http://www.sciencedirect.com/science/article/B6V8V-4S1S6FC-5/1/153b556411c3508840f66b38f74c6186 and The accuracy of statistical distributions in Microsoft® Excel 2007 http://www.sciencedirect.com/science/article/B6V8V-4S1S6FC-4/1/53635212da2eb0e5750feb645a1d032e These could be interesting for Calc, too. Unfortunately, I do not have access to Computational Statistics Data Analysis at home. I won't be able to try accessing these articles from the university during the next few weeks due to staggering work elsewhere and pending holidays. [I am unsure that I will succeed at the university, by the way - I feel the journal is not subscribed.] However, I hope that someone will be able to access those files and post any relevant informations to this list. Many thanks, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] development status of BETADIST
Hi Regina, Regina Henschel wrote: Eike Rathke schrieb: Hi Regina, I postponed this reply far too many times.. sorry. Too many questions at once ;-) It seems to be one of the most difficult functions. To get an idea of that function, have a look at the file toms708.c of the R-project. There is some hidden beauty in that file. ;) Without being of much help, I just want to draw attention to some features I spotted during a peak in the code: - often, (1-x) is decomposed into more stable terms, - from the simple 0.5 - x -0.5 [and x - 0.5 - 0.5 (for x-1)], - to more complex terms like: return w * (0.5 - 1.0 / w + 0.5); [this is actually w - 1] The code was well written by numerical analysts, and optimised for accuracy over a large domain of values. Is it feasible to use this library or a similar library (if one is found)? Rewriting this code isn't going to be easy. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] just found ;-)
Hello everyone, Loiseleur Michel wrote: Hi *, Leonard Mada a écrit : [...] *Thirteen Prevalent Misconceptions about Floating-Point Arithmetic:* 3. Arithmetic much more precise than the data it operates upon is needless, and wasteful. I have concerns that double is not appropriate anymore. It is not appropriate for Sun, nor for IBM, nor for Google, nor for any financial institution nor any government. Is it possible to use greater precision? And still attain reasonable speed? The answer is definitely yes. R, Matlab, other financial tools are orders of magnitude faster than Calc. And work usually with greater precision than Calc anyway. Therefore, it should be at least theoretically possible to speed up Calc to work fast enough with higher precision. My 2 cents : it's also technically possible. There is, among others, the libgmp : http://gmplib.org/ . Thank you for pointing this out. I am really amazed: both: 1 + gcd(87324,78263148,7896) * (10^1989879887 mod 471!) and 1 + gcd(9998560,6898880) * (2^1989879887 mod 471!) computed in 2 ms! That is fast. That is almost (or probably) instantly. The fact I mentioned R earlier stems from my experience both with R and Calc: I believe Calc is much too slow, orders of magnitudes slower than R. I do complex calculations in R, and these are performed almost always instantly even on my slow PC. Similar calculations take tens of minutes (and sometimes more than an hour) in Calc. Also, I often see messages like Adapting row height, operations which take longer to complete than the calculation proper. Therefore I am fairly convinced that the computation time in Calc is only a fraction the total time needed to complete an operation, and even this could be improved dramatically. Sincerely, Leonard P.S. Maybe splitting the underlying spreadsheet model in a separate data-table/matrix and the view-matrix would speed up computations significantly: A.) data-matrix: data[x,y] will contain only the values - computations would be very fast, because: -- there is NO overhead for other things -- would iterate only through the values proper - because I insist on type checking, there would be a type identifier, too ;) B.) view-matrix: cell[x,y] contains all other things, including styles, view-options, formatting, ... Its job is precisely to adapt storage to the kind of computing needed and to scale well, whatever the number of digit is. [...] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] just found ;-)
and computes intermediate results, then he will loose ALL the digits. How many users are numerical analysts to devise numerically stable formulas? I am for sure NOT one of the analysts.) If there are some things to be aware of, then I would like to quote Prof. Kahan: *Thirteen Prevalent Misconceptions about Floating-Point Arithmetic:* 3. Arithmetic much more precise than the data it operates upon is needless, and wasteful. I have concerns that double is not appropriate anymore. It is not appropriate for Sun, nor for IBM, nor for Google, nor for any financial institution nor any government. Is it possible to use greater precision? And still attain reasonable speed? The answer is definitely yes. R, Matlab, other financial tools are orders of magnitude faster than Calc. And work usually with greater precision than Calc anyway. Therefore, it should be at least theoretically possible to speed up Calc to work fast enough with higher precision. I am looking forward to see at least the double-extended data type. And hopefully a long long (long64) integer. And some D(ecimal)-type functions. And then maybe some IEEE 754r features. And maybe some other great ideas. And probably I'll complain even then. ;) Sincerely, Leonard * “ 95% of the folks out there are completely clueless about floating-point.” ( J.G., 28 Feb. 1998 ) (Maybe more than 95% ?) [Prof. Kahan] ** For a lot more details, please visit Prof. Kahan's website: http://www.cs.berkeley.edu/~wkahan/ I added some comments in-line, like a DSUM() function for decimal floating point. Eike Rathke wrote: Hi Leonard, On Friday, 2008-07-04 03:07:04 +0300, Leonard Mada wrote: Well, 15-digits might have been intended behaviour in the '70s, but is hardly appropriate in 2008. IEEE 754 double did not change. I am really amazed, that programs still cling to 15-digits. Well, the 64-bit double precision might limit calculations to ~16-digits, but professional programs should do better. Even MS Windows calculator ouperforms both Excel and Calc (as the previous article states). So what? Comparing apples (e.g. BCD arithmetic or IEEE 854) with oranges (IEEE 754) does not help at all. I assume you do not want Calc to use BCD or other arbitrary precision instead of floating point at the cost of multiples of calculation time? Yes, I do want special functions (e.g. a DSUM) to perform exactly these high accuracy operations. Users who depend on accurate results might invest the time in using these functions. Knowing that such functions do exist will also remove some of the misconceptions and errors. I sincerely hope that OOo increases the precision, at least if an operation overflows, Calc should increase the precision to 24-32 digits. [Most existent processor designs implement the IEEE754 Exceptions, including the OVERFLOW exception, therefore it is possible to catch such an overflow. In this case, Calc should either display a warning that the result is wrong - and offer a robust calculation mode, or just use a greater precision. Hiding the error is not very rational.] Detecting overflows doesn't help against roundoff errors. And there are many other issues. Floating points spring undoubtedly to mind. I hope that the floating point arithmetic improves drastically, too. With IEEE 754 most improvement is about curing symptoms and cheating the user because she expects 0.3-0.2-0.1 to equal 0.0 I advise the interested reader to read some of Kahan's papers, see: http://www.cs.berkeley.edu/~wkahan/ [e.g. http://www.cs.berkeley.edu/~wkahan/Mindless.pdf] [Kahan is the architect of IEEE 754 - he is also known as The Father of Floating Point.] Well, yes, nice read, it states what can go wrong and at the end the best solution is he hopes that popular programming languages will support Interval Arithmetic of arbitrarily high (within limits) precision variable (coarsely) at run-time. Then programmers may use it to prove most of their numerical software free from roundoff-induced anomalies even if it runs sometimes slower than usual. I don't see where we'd currently benefit, but maybe I browsed too quickly. For the interested reader: Numerical Computation Guide http://docs.sun.com/source/819-3693/index.html 2005, Sun Microsystems Studio 11. What Every Computer Scientist Should Know About Floating-Point Arithmetic http://docs.sun.com/source/819-0499/ncg_goldberg.html Appendix D of the Numerical Computation Guide. Numerical Computation Guide http://docs.sun.com/source/806-3568/index.html 2002, may be better suited for high resolution displays, mathematical formulas have better readable graphic images. Eike - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] just found ;-)
Hello Niklas, hello everyone, Niklas Nebel wrote: Oliver Brinzing wrote: have a look: http://www.spiegel.de/netzwelt/web/0,1518,563637,00.html seem's oo has the same problem with: 29513736 * 92842033 = 2740115251665290 Output is rounded to 15 significant digits. That's intended behavior. Well, 15-digits might have been intended behaviour in the '70s, but is hardly appropriate in 2008. I am really amazed, that programs still cling to 15-digits. Well, the 64-bit double precision might limit calculations to ~16-digits, but professional programs should do better. Even MS Windows calculator ouperforms both Excel and Calc (as the previous article states). I sincerely hope that OOo increases the precision, at least if an operation overflows, Calc should increase the precision to 24-32 digits. [Most existent processor designs implement the IEEE754 Exceptions, including the OVERFLOW exception, therefore it is possible to catch such an overflow. In this case, Calc should either display a warning that the result is wrong - and offer a robust calculation mode, or just use a greater precision. Hiding the error is not very rational.] And there are many other issues. Floating points spring undoubtedly to mind. I hope that the floating point arithmetic improves drastically, too. I advise the interested reader to read some of Kahan's papers, see: http://www.cs.berkeley.edu/~wkahan/ [e.g. http://www.cs.berkeley.edu/~wkahan/Mindless.pdf] [Kahan is the architect of IEEE 754 - he is also known as The Father of Floating Point.] See also: http://en.wikipedia.org/wiki/IEEE_754r Sincerely, Leonard Niklas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] [Issue 90759] ZTEST not same as Excel
Hi all, David King wrote: Moving discussion of this issue here, at Regina's request. Hi Regina The function ZTEST should depend on NORMSDIST. Therefore NORMSDIST has to be controlled and improved first. I'm afraid I disagree pretty whole heartedly :) NORMSDIST may or may not be more accurate in Excel, but in Calc it is accurate enough to use. For example for our 2nd example the stats package R gives a p- value of 0.02275 or 2.275% - accurate to just 4 digits - that's good enough for practical purposes. Both Excel and Calc have more digits. R works internally with 64 bits internal precision if I am correct. You can obtain more digits, but the truth is, you almost never do need them for a p-value. From a very old version of R: The floating-point control setting has been changed to use 64-bit rather than 53-bit mantissas, which will result in slightly different but generally more accurate results. I only added the accuracy of the SD-algorithm into the discussion, because the naive single pass algorithm used inside the ztest routine may give very wrong results, invalidating the whole z-test (wrong by orders of magnitude in either direction and even negative variances, therefore raising an error). Sincerely, Leonard The point is that Calc ZTEST is *broken* and has probably always been - there is a clear and simple bug that I've found (and even provided a couple of lines of code to solve). Where Excel calculates 2.275%, Calc calculates 0.00039%. The idea of this statistic is to compare it against a preset significance level of maybe 5%, or 1% or 0.1%. Calc is giving the wrong answer; in a situation that demands a simple pass/fail answer Calc gives the wrong one. That's serious and needs action - it can't wait until someone figures out how to improve NORMSDIST's accuracy from 7 places to 13 or whatever. The good news is that it's very easy to fix :) All the best David - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] any performance test-cases ?
Hello everyone, I have attached a 2nd spreadsheet to issue 89976, please see: http://www.openoffice.org/issues/show_bug.cgi?id=89976 The column C is unfilled (except for the first 3 cells). Everyone can now fill as many cells in the C-column as he wishes. Filling the first 1,000 is likely to be computed in real-time, filling the first 10,000 will take some time on slower computers (like mine), filling 30,000 cells will likely take quite long even on a fast PC, and filling ALL 65,535 cells will take some ages. ;-) I decided to recreate the spreadsheet, because, IF Calc uses a naive algorithm to update downstream cells, updating them ALL will need to perform 6.6 million sort operations on 65,535 elements (column C). That will take a huge amount of time and will basically render the original spreadsheet unusable. [I am actually unaware on Calc's internal workings, BUT a naive algorithm seems likely.] One has now greater flexibility in choosing how many elements column C will contain. Open the 2nd spreadsheet, select cell C3 and fill down as many cells as you wish. I would recommend starting with 1,000 -10,000 cells and moving slowly towards more cells. The process is likely O(n^2) or worse, so every slight increase will have a dramatic effect. By the way, I noticed a BUG in OOo-dev 3.0 beta m14: - you cannot have $B$1:B1, it will automatically convert to $B$1:$B$1, BUT $B$1:B2 is still possible (I depend on this functionality) Please also note, that Calc takes a long time to update formulas, BUT somehow I feel that most time is wasted on unknown processes: e.g. Calc will display Adapt row height and this operation will take on my computer as much as (or more than) the proper sort-operation. I have written additional comments on the naive algorithm vs more advanced algorithms in the spreadsheet document. Please read carefully. I hope that this spreadsheet will make it a good test-case to test future performance. Sincerely, Leonard Leonard Mada wrote: Dear Caolan, I have attached a test-spreadsheet to issue 89976. [http://www.openoffice.org/issues/show_bug.cgi?id=89976] Please have a look. It took me over one hour to generate the spreadsheet (basically waiting for over one hour to fill in column C). Now that I closed the spreadsheet, it seems I need another hour to re-open the spreadsheet (due to the recalculations, and I do NOT have the patience to wait for it). The spreadsheet is not finished, but unless I have a lot of time to open and work with it, I do not think I will try it again. Have fun in debugging Calc. Sincerely, Leonard Caolan McNamara wrote: Does the calc team have anything like a performance testcase spreadsheet ? Something along the lines of a computational performance test as opposed to something IO bound. C. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] 2008 Q2 Review of Spreadsheet Project
Hello Niklas, dear list-members, sorry for the late reply. I am trying to work out my backlog. Niklas Nebel wrote: Niklas Nebel wrote: Niklas Nebel wrote: There is now a preliminary planning section on the wiki page. It can be discussed for a week, and will be finalized in an IRC meeting on May 12th. Same mistake again. It's May 13th. The second meeting took place, and we have a final list now, *without any real surprises*, [emphasise added] This is what bothers me. Unfortunately I could not join the direct chat, and as I get more involved with my own work (primarily academic but also my work with a company daily from 14.00-18.00 o'clock), I will have less and less time for OOo. [Is the chat transcript / log somewhere available?] The issues mentioned are already in work (3, 4-6, probably 1 and 2 as well). Feature 3 (Calc collaboration) is definitely an important one, but it is in work, too. And 1 and 2 are work in progress. What I really miss are new features, a true roadmap. And I miss the long-term planing. What will OOo Calc evolve to? Apple's iNumbers made some bold move by separating the spreadsheet layer from the Canvas. MS Office made some important moves, too, and my informations tell me that 2009 (with Office SP2) will bring more radical changes, too. How can Calc compensate for this? Sincerely, Leonard at http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project. Niklas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Request some assistance
Hello everyone, I am revisiting an old BUG. Leonard Mada wrote: Hi Bruce, Hi Leonard, Leonard wrote: Now, to give a usefulness to the 'CTRL'+'A' key, I return back to my previous post: give the user 2 choices 1.) first choice: display window where user can select the number of columns and rows (for default values see paragraph 2.) ) 2.) select everything UP TO: - the right-most column that is NOT empty - the bottom-most row that is NOT empty This way, the user can make a better selection. This idea is very too bad.The user use 'CTRL'+'A' is for fast,if you force user selecting paste range,then it is counterproductive. I'm looking forward to your suggestion. Best Regards Bruce Well, from my experience I do not believe that it is bad. Actually most times I want to select something I would opt for 2.) and sometimes I would like to specify the dimensions more accurately (i.e. 1). This is at least usable. As I said, I never encountered the situation that one wants to select everything up to 65,536 x 256 and nobody showed me an example to the contrary. I needed today to fill up exactly 30,000 rows. How do I do this? Selecting manually those 30,000 rows is - well - a big pain. I tried to select one column and paste within one column, BUT Calc crashed (this would have created 65535 rows, but I could have worked with the first 30,000 - though it would have been a monumental waste of resources). Again, I never encountered the situation where I need exactly 65,535 rows. But I often need an accurate amount of rows which I can specify. How do I specify it? Sincerely, Leonard As said, this is for the *PASTE* feature after a *CTRL+A* selection, so I do NOT mind IF CTRL+A behaves the same as before. It is just after one presses *PASTE* that this new feature should be implemented. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Request some assistance
Hi Regina, Regina Henschel wrote: Hi Leonard, Leonard Mada schrieb: I needed today to fill up exactly 30,000 rows. How do I do this? Enter the cell range - for example A2:AMJ30001 - into the 'Name Box' (the drop down list on the left side of the formula bar) and hit enter. Now the range is marked. Yes, I remembered some 5 minutes later, but unfortunately Calc has already crashed - which made me very angry. Now, I tried this same with A1:A3 and pasted just a simple formula on an empty spreadhseet, BUT Calc crashed again. [Though it works with simple numbers.] But why did it crash with the formula? It repeatedly crashes. [The original formula evaluates to a Value! error - but nevertheless it should not crash.] This is a rather limited range 1 x 30,000 = 30,000 cells in an otherwise empty spreadsheet. I do have 1.3 GB physical RAM + additional swapfile. Win2k SP4 In the Win TaskManager, Calc changes immediately to Not responding. Waiting 10 minutes did not result in a response. Now I tried it again with a formula that does NOT evaluate to an error, and it crashed over again. It becomes immediately non-responsive. This is a major limitation, because any serious work involves more and more data.We are in the year 2008 and 30,000 cells is not a rarity anymore. I already feel the 65,535-rows limitation, BUT actually Calc even doesn't reach that theoretical limit. Sincerely, Leonard If you do not know, that the last column is AMJ, then click the head of the first row. The Name Box will show the last column. Then you can adapt the line number to your needs. kind regards Regina - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] 2008 Q2 Review of Spreadsheet Project
Hello Niklas, hello list members, I have outlined a medium and long-term strategy for Calc, see: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project#mid.2Flong_term_planning This is work in progress and I did not finish writing everything I planned to cover (especially the SUMMARY() function and many aspects useful for general users). But it should be pretty clear how I see the future. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Re: [sc-dev] 2008 Q2 Review of Spreadsheet Project
Hi Eike, hello list-members, Eike Rathke wrote: Hi Leonard, On Tuesday, 2008-04-29 22:54:02 +0300, Leonard Mada wrote: 1.) AUTOMATIC DATE RECOGNITION I strongly support modifying this behaviour in a short timeframe and implementing a date input-mask, where every user can accurately direct OOo as to what input is a date (e.g. only dd.mm.yy, and not xx.xx) short timeframe and implementing a date input-mask are contradicting in itself. [...] In a medium time frame [...] Implementing some date recognition mask would need a configuration item, [...] Unfortunately, this time is not available anymore. There was time in 2005, there was less time in 2006, there is non left in 2008. MS Office - and I was never a big fan of MS Office - did make big jumps forward with Office 2007. And they are not alone. The real problem is, OOo lags behind MS Office. The more daunting task is not just to catch up with the current MS Office, but to catch up with the next release of MS Office. I have a *very strong gut feeling* that MS will release a much improved version (implementing some new concepts as well) in 2009, and all the informations I have point in the same direction. During the past 2-3 months I was faced with using MS Office 2007 at my 2nd work (I do not have a working copy at home), but I must confess, MS did a lot of work over the last few years. [It is far from perfect and I surely can find dozens of things to critique, but it definitely got a more professional look]. Added to this, iWorks introduced some new concepts in their spreadsheet program, too: http://www.apple.com/iwork/numbers/ There is NOT much time left, both companies work already on new features, NOT the thing of I add this feature, you add that one that happened e.g. during the browser war. These are really user-centric features with global implications on how users perform their daily-work. I feel OOo moves too slowly to catch up. Evolution teaches us: those who can adapt can survive. (NO, the fittest survive is not the correct interpretation; it might apply on a very short time frame, but on a longer time-frame, only those who can adapt will survive.) And MS did show over and over again that it can adapt, and Office 2007 supports this even more than ever. We are talking here about breathtaking new features, but I can't see any innovation during the past years. = 2.) TEXT-vs-NUMERIC 1.) convert text-to-numbers automatically I don't know why you come up with this again, I thought I lined out in issue 5658 why that is not a good idea because results would be locale dependent. Simply put, because the distinction between numerical values and textual numbers is a very artificial one created by programmers, not by spreadsheet users. Let's ask a simple question: === When does a spreadsheet user insert a number as text? How are these string-numbers generated? What is their purpose inside a spreadsheet? I invite everyone to think about these questions. I will detail my experience in the next post. As these are not trivial questions - but with very far reaching consequences - it might help to analyse real users and how they behave/interact with the spreadsheet. Sincerely, Leonard === [...] Btw, cross-copying to more than one mailing list quite assures that the discussion will be scattered over the lists. Well, the announcement was made both on the SC list, as well as the UX-list, and the subject seemed relevant enough and touching both fields for posting this comment on both lists. I agree that it is more difficult to follow the discussion on both lists, so I invite everyone to post only on the UX-list. Apologies if it creates any inconveniences. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] 2008 Q2 Review of Spreadsheet Project
Hello everybody, this is good news. I will add some important strategic suggestions in a later post. This is only a short comment. TOC === 1.) AUTOMATIC DATE RECOGNITION 2.) TEXT-vs-NUMERIC 1.) AUTOMATIC DATE RECOGNITION == Regina Henschel wrote: Hi, Niklas Nebel schrieb: Hi, [...] quarterly reviews for each project. There is now a wiki page at http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project to collect issues, and on May 5th there will be the first IRC meeting (check if all important issues are raised and sort out the unimportant ones). If you think an issue should be included, please add it to the wiki page. I've added issue 87999. kind regards Regina Indeed, issues 87999 (11 votes and a lot of duplicates) and 33723 (31 votes) are related and cause a lot of pain. Both involve more or less the automatic date-recognition mechanism in OOo.* * http://www.openoffice.org/issues/show_bug.cgi?id=87999 http://www.openoffice.org/issues/show_bug.cgi?id=33723 I strongly support modifying this behaviour in a short timeframe and implementing a date input-mask, where every user can accurately direct OOo as to what input is a date (e.g. only dd.mm.yy, and not xx.xx) 2.) TEXT-vs-NUMERIC === Issue 5658 (http://www.openoffice.org/issues/show_bug.cgi?id=5658) covers this scenario. My comment could easily slip into bashing, so my apologies IF this happens. PROBLEM How should as-text-stored numbers be handled? Well, from all the possible handling-options, my personal impression is that OOo has chosen the worst one: It *silently* ignores these numbers. The following problems exist: - NO user notification that something went wrong [aka Raise Error / Flag result] - NO easy user action to convert text to numbers - NO easy way for user to detect which numbers are stored as text So, the proper implementation should: 1.) convert text-to-numbers automatically 2.) raise error / flag results where such text occurs 3.) offer easy recognition and conversion of text to numbers ALL 3 mechanisms should be implemented. ADDITIONAL COMMENTS == The text-to-number issue needs some additional comments. Please note, that I am the guy with the strong-typing in spreadsheets request (http://www.openoffice.org/issues/show_bug.cgi?id=79924 - I'll return to this request in a later post). Unfortunately, separating numbers from strings is a very *artificial* separation. It is NOT the way spreadsheets and humans function. 3 is always 3, independent of it being addressed in a numerical or textual context. This distinction is the way classic programmers think and programming languages like C/C++/java function, BUT not the way spreadsheets should function. The 2 worlds are very distinct ones and - unfortunately - the early programmers did NOT understand much of spreadsheets (and probably this did not change much over the years). Therefore, I would completely drop this distinction in spreadsheets: this is only important for how the data is stored and handled internally, BUT this is *nothing* the user should care about. It is more like a formatting option (BUT - strictly speaking - it isn't a a very good one, because it misses a lot of issues - so users will apply their own formatting anyway). On the other hand, it does make perfect sense to distinguish: scalar numbers (stored as number proper or text) from currency, from date and from other units (including time units). A strong typing model is needed, not a formatting option to display a number with a currency symbol. Instead, formatting a number as text or number proper is actually a *formatting option* - in strong opposition to the true units I mentioned earlier. I will discuss typing more broadly in a new post - as it is a very important and strategic concept. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Re: Date Input
was: Date input (was: Re: [sc-dev] Pasting from HTML as unformatted text adds space t... from December 2007 Dear all, as previously discussed: determining whether the input is a date without any other clues has been (almost) proven to be error-prone... [see http://sc.openoffice.org/servlets/ReadMsg?listName=devmsgNo=2751] I stumble over and over again upon this issue. To make the story complete, others stumble on this issue, too, see: http://www.openoffice.org/issues/show_bug.cgi?id=87999 I sincerely hope that a solution will be implemented to improve Calc in this respect. My proposal looks like this: It is my belief, that OOo needs a mechanism to explicitly set the date-interpretation algorithm (aka which numbers are dates): - e.g. should 'dd.mm' be interpreted as date, - yet others might choose only 'dd/mm' - and still others only 'mm/dd' - or only 'dd.mm.yy' and leave 'dd.mm' as a number Indeed, this would give complete control *back to the users*. Especially users in multi-locale environments would benefit from this feature, but many others would be grateful, too. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Multidimensional Spreadsheets and OLAP Servers
Hi all, After a long period of inactivity, I have recently filed the 3rd issue in my *Major Spreadsheet Design Flaws* series: see http://www.openoffice.org/issues/show_bug.cgi?id=85825 This issue was long overdue and, although I mentioned it a couple of times, I never found the time to finish it. It was quite long in the making, and I omitted many things (but otherwise I wouldn't have finished it yet). Unfortunately, some major recent developments convinced me to proceed with this issue (even if unfinished). Sun aquired MySQL, while many other vendors consolidated their multidimensional/OLAP products. Both increase the pressure on Sun/OOo/MySQL to offer similar products, and, most importantly, could offer the possibility to converge these eforts for both Sun and MySQL. In the next paragraphs I will attach the text of this issue. However, I recommend to everyone to read first the introduction on the history of OLAP products on http://www.olapreport.com/origins.htm to get a better understanding of the multidimensional concepts and usability of these products. A more comprehensive description of the features can be found on various other internet sites, like for specific products, and on wiki-pedia. I omitted to mention these sites in the original issue, but one can Google for multidimensional spreadsheets, e.g. see: http://en.wikipedia.org/wiki/Quantrix_Modeler and http://www.lumina.com/ana/whatisanalytica.htm Sincerely, Leonard ISSUE = This issue is part of a more comprehensive issue describing *major design flaws* of existing spreadsheets. I have posted already major design flaws part (1.)(issue 79924), part (2.) (issue 80139) and a subissue of part (4.) (issue 83767). This is the Part (3.) of Major Spreadsheet Design Flaws continuing the design flaws in the Spreadsheet Structure. The implementation of this issue will also depend significantly on the Part (4.) issue in this series and on splitting the spreadsheet into 2 components (as described briefly in this issue and further on in a future post). SUMMARY === With the recent acquisition of MySQL by SUN Microsystems and the various developments in the IT world, a new appraisal of *multidimensional software* has become necessary. TOC - INTRO: Multidimensional Spreadsheets -- Problem - SOLUTION -- Concept - UTILITY - LESSONS A. INTRO So, what are Multidimensional Spreadsheets? I will begin with a small description of the problem. A.1. PROBLEM Lets say, a business takes account of its sales using a spreadsheet. Monthly data is analysed and used for various calculations. Now, a new month has started, and the data begins to be entered in the spreadsheet. The problem is, the user has to copy all the formulas from the previous month to the new month (and correct/adapt eventual copy-errors), then correct the formulas yielding yearly data/analysis (like yearly sales, ...) to use the data from the new month. This process is cumbersome and error prone. B. SOLUTION === The solution would be to make the spreadsheet automatically aware of this new data and: i.) apply all formulas correctly on this new data, while ii.) correcting any global formulas to take account of this data. This is already possible using so called multidimensional spreadsheets. A good account on the history of multidimensional software can be found at: http://www.olapreport.com/origins.htm. B.1. CONCEPT What is the concept of multidimensional software? The data is split into multiple dimensions. Basically, in this simple scenario, we have 2 dimensions: one describing the revenues, and the other the time (basically the months). When we add data for a new month, basically we extend the time dimension with one month. All formulas applying to monthly data, will apply to this new month as well. The global formulas will similarly consider the values for the new month. C. UTILITY == So, what is the utility of this software. There are many use cases. Please note, that this is a feature most useful in high-end and professional settings. It is likely that casual spreadsheet users (like most home users) won't benefit from this feature. This is why I advocate splitting the spreadsheet application into 2 separate entities: I.) a general canvas/table/grid-formatting program (this is what most home users do most of the time – only minor spreadsheet functionality needed – current spreadsheets fail miserably at this point), and II.) a professional multidimensional spreadsheet module. (I will deal with this splitting requisite in a future issue – for a glimpse of this requirement, you could read further details on the wiki page: http://wiki.services.openoffice.org/wiki/User_Experience/Grand_Concept#Calc but please note that this is work in progress and not ready yet for further discussions). The utility resides in corporate environments where complex spreadsheets are in use. Adding new dimensions to a spreadsheet is a rather
[sc-dev] Re: SC - ANOVA
Hi, Per Eriksson wrote: Dear Leonard Mada, I have received an inquiry about our Calc component and ANOVA features. I do not know if anyone works on implementing natively ANOVA in Calc. I did some preparatory work (see http://www.openoffice.org/issues/show_bug.cgi?id=4921), but I am not a programmer. and I am not able to finish it. It is probably best to ask the question on the spreadsheet mailing list. There is another possibility to compute an ANOVA test, using the R-extension, see: http://wiki.services.openoffice.org/wiki/R_and_Calc_Documentation#ANOVA_Submenu I haven't tested the R-plugin code in a while, but it did work at least on a previous version of OOo. I hope to have some more time in the new year. Unfortunately, these last few weeks will be pretty overcrowded with shedules, so I won't be able to test it further. Sincerely, Leonard Do you have an idea about how far we've gone? Thank you! :.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Best Regards Per Eriksson Marketing Contact Sweden OpenOffice.org Phone: +46 70 560 10 33 Email: [EMAIL PROTECTED] Web: http://sv.openoffice.org/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] New features about Data Filter
Hi all, Mathias Röllig wrote: Hello! Am 12.12.2007 20:59 schrieb Leonard Mada: 1.) I do not have a strong opinion on this. Somehow, I find autofiltering very limited and useful only in very simple situations, not on really big tables. (See my issue http://www.openoffice.org/issues/show_bug.cgi?id=66588 for a better solution. Thats why I basically have switched to MS Visual FoxPro for such situations - not an ideal solution.) Calc isn't and shouldn't be a RDBMS. So you have find out by yourself that a database is the better solution for you. While Calc is not an RDBMS, it offers some great advantages over a full DB-solution. 1.) a DB needs special software (I haven't tried Base yet, but this is not the sole point) 2.) a DB needs skilled users that know to work with a DB - create the DB - use the data inside the DB - this is much more complex than a simple SELECT statement 3.) to create a DB one needs to know beforehand how the data is organised Conversely - a spreadsheet is cheap, no special software - can be easily created - in NO time (INSTANTLY) - most users know to work with the spreadsheet (and could easily learn an additional SELECT statement) - data can be edited easily within the spreadsheet - the data does not need to be structured (especially in the beginning, when the user doesn't yet know how the DB structure should look like) There are so many good reasons to use a spreadsheet, and indeed, I can confirm that spreadsheets are still the most used mediums. In the institution I work (5000 - 1 employees), spreadsheets are still the backbone. But many tasks are indeed replaced with custom DB-solutions. We are also implementing a custom solution (cost 2 Million $, and NO, it is not MS). It is a great loss for the spreadsheet market, but it was somehow foreshadowed, because non of the developers showed any interest in improving the spreadsheet concept over the last 30 years. [There were some brilliant ideas back in the early 90s, but non of the big vendors persuaded this path. I have already witnessed numerous other organisations migrating to custom solutions, that I now believe that the damage to the spreadsheet market is final.] Extending the spreadsheet concept with some SQL-commands would only make them better and more usable. So you can switch from FoxPro to Base. I don't know if Base supports FoxPro but any other type of database. I do not want to switch. I can use Excel to edit the spreadsheet and import the spreadsheet in VisualFoxPro to perform some advanced searches. BUT, this is still an additional step. Why not have both steps inside Calc? ... 2.) A big problem for autofilter are big tables, like with 50,000 rows. The user might have 10,000 entries and it becomes very impractical to search a value in the autofilter within this big list of entries. Some brainstorming is needed to solve this one (I am aggressively pointing to my previous issue). In my opinion your issue and autofilter are two different things. ... To make my point of view clear: Think about if there also no Lehmann in Hamburg ... Far more often (at least in real business), one does not have to cope with NO 'Lehmann' in 'Hamburg', but rather with 'Lehmann' is one entry between other 10,000 distinct entries. Searching the right entry in the filter drop-down list is really cumbersome. That's my point. And I have no idea how to solve this issue. SQL-statements would make it so much easier. Sincerely, Leonard This is a problem that many users have with Excel. And there is no way to reset *all* autofilter columns at once, or is there any (except to delete the autofilter)? There are many users which will not find any Hempel in Dresden if they got the sheet with active autofilter from anyone. Greetings Mathias - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Date input
Hi all, Issue 77973 further discusses the Date-issue (see http://www.openoffice.org/issues/show_bug.cgi?id=77973). That issue is still unconfirmed, but in the meantime I think it is really justified to confirm it. Therefore, someone with the appropriate rights should change the issue to NEW. Niklas Nebel wrote: Leonard Mada wrote: Formatting dates based on the system locale is BAD, BAD, BAD in a *mutli user environment*, because you end with both MM/DD and DD/MM and a lot of confusion. Whatever step is taken, please consider that multi-user environments contain usually also multiple locales, en-US being frequently one of the locales. Please also note, that even the date is often not stored correctly, because when you enter new data in a new cell (or old cell), cells might be formatted like MM/DD/ although the user expects DD/MM/. If you don't explicitly assign a number format with a locale to cells, Well, but I do not like the default formatting. And many of my colleagues don't like it either. And considering that I am usually working on a US-en locale (to avoid countless of other problems), the default date-formatting will be really weird, a format that I never encountered in any of the countries where I worked. And considering that my tastes differ sometimes from those of my colleagues, it always ends up in a mess. I could impose some strict use of a date format on a user level. But this is the wrong path. Having better control over Calc is the better solution. Sincerely, Leonard every user can view and edit numbers and dates according to his configured locale. I consider that a good thing and don't want to change it. [Do you believe that users have always full access to set their locale in a business environment? And how many do really set their locale correctly? Experience teaches me that almost non of the users sets the locale on his work-computer - just as a side note.] Niklas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] New features about Data Filter
Hi all, Mathias Röllig wrote: Hello! ... Imagine that you have a big table with many columns and data rows. Now you set a autofilter in 3 or 5 columns. Which was the first that you have set? OK, you can remember it. But if you save the file and another person want to work with it? The person must reset all autofilter columns to get all data visible. 1.) I do not have a strong opinion on this. Somehow, I find autofiltering very limited and useful only in very simple situations, not on really big tables. (See my issue http://www.openoffice.org/issues/show_bug.cgi?id=66588 for a better solution. Thats why I basically have switched to MS Visual FoxPro for such situations - not an ideal solution.) What if the filter data that is not found, is grayed instead of hiding it completely? 2.) A big problem for autofilter are big tables, like with 50,000 rows. The user might have 10,000 entries and it becomes very impractical to search a value in the autofilter within this big list of entries. Some brainstorming is needed to solve this one (I am aggressively pointing to my previous issue). Sincerely, Leonard Another little example, only with 2 filter criteria. First you select Hamburg and want to get all data rows for Lehmann. OK. At second you want select all Hempel in Dresden. But there is no Hempel in Hamburg ... And now imagine, that you want to do this with many more columns ... It isn't a good idea always to have Excel as the only guide ... Greetings Mathias - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Date input
Hi all, Cor Nouws wrote: Hi *, ... The current system allows fast working. With typing in dates, once you know how it works and /or set your cell formatting / styles correctly, it is OK. No, it is not OK. If you work in a multi-user, multi-locales environment, you will feel the heat burning you fingers and mind. Unfortunately, I do work in a mixed environment (both MS and OOo), and here dates will always go wrong. [Frequently, depending on how many users worked on the file, some dates are in one locale, others are in another, so that one has cells with dates in both the MM/DD/ form and nearby in the DD/MM/ form.] In order to avoid formatting errors with numbers, I use non-localised versions of OOo (or MS Office). At least this way, numbers are consistent. However, this is not the case with dates. I mainly blame the office packages for trying to detect a date with partial strings (like xx/xx or xx-xx or xx.xx). It is less so a problem with whole strings, except that en-US programs try to format the date as mm/dd/, although this is only used in the US. [Decimal numbers are somehow less of a problem, because everyone knows that x.x is a decimal number - even in locales where the decimal is written like x,x - BUT this is different for dates and more annoyingly, a date might be represented as MM/DD or DD/MM and this is NOT something one can easily detect.] I could blame the Americans for having this date format, but, as Murphy's law says: Washing your car to make it rain doesn't work. I would therefore prefer a clean way to enter dates, and not based on a guessing mechanism that is accurate some 20-30% of the time. Having the ability to specify, what is a date (e.g. only mm.dd.yy or mm.dd.), is the best alternative. I am open to a brainstorming session, and if anyone comes with a better solution, I would be glad to support it. But I am also certain, that an automatic guessing mechanism can never have adequate precision and therefore strongly recommend against such a mechanism. Sincerely, Leonard The only problems, AFAIAC, arise when pasting data in a spreadsheet... Which columns or cells will be OK, which not ??? If this is the real problem in general (thus valid for most of the complaints) that could influence a possible solution. Kindest regards, Cor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Date input
Hi all, Kohei Yoshida wrote: ... * determining whether the input is a date without any other clues has been (almost) proven to be error-prone, can never reach an acceptable level, and leads to the wrath of users, largely due to localized date formats. So, what can we do? The rough idea I've just come up with is to do what web applications do: provide a date input box. That was basically my idea, too. Though slightly different. Aloow the user to specify, what should be interpreted as a date (and how). E.g., IF the user specifies that dates are only strings of the form: dd.mm.yy or dd.mm., then only such strings would be converted to dates (also note the *dd.mm* which is different from the American *mm.dd* format!). Then only such string would be converted to dates, and NOT *xx.xx*, which would most probably be a number and NOT a date. Just my thoughts. [This should be settable either at OOo level, or at document level.] Sincerely, Leonard This way Calc also knows that the user is about to enter a date, and try not to even parse an input as a date if the date input box is not used or be more strict about what format is considered a date in that scenario. That would also allow us to localize the date format too without causing too much headache. Other ideas are welcome. Kohei - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Pasting from HTML as unformatted text adds space to cells
Hi, my vote for turning the Auto-Date OFF! I hate this date thing. I use the english version of OOo, because I hate localised versions, and also their handling of number/date-formats (and other problems with localised versions). But I often write dates as non-standard dates. However, OOo insists to reformat things, although that makes no sense, and even worse, it tries to format things that are NOT dates as dates. He SHOULD leave that as is. I always hated this Auto-Date bullshit. One possible solution is to have a global setting, to turn ALL date-autoformatting off. Other advanced features would be to be able to set a date-format at OOo level: - only specially formatted strings shall be interpreted as dates (e.g. only dd.mm.y* where '*' is a wildcard) - and the date shall be formatted as pre-specified (e.g. dd.mm.) It should be possible to use this settings *document-wide*, too. Therefore, the user should be able to define a date-format for a specific document and also to define string-styles that should be interpreted as dates. This would be great. Many thanks to whomever implements this. Sincerely, Leonard Cor Nouws wrote: Hi Niklas, Eike, *, Niklas Nebel wrote (6-12-2007 11:49) Cor Nouws wrote: I can't find any spaces in the HTML. But pasting in a text editor or another spreadsheet appl. indeed gives the same result ... So probably something strange with the HTML-page? Or Firefox bug 303597 (https://bugzilla.mozilla.org/show_bug.cgi?id=303597), assuming that you copied from Firefox. Thanks for the replies. Indeed, it must be a FF bug, since the paste result is the same in other progs as well (didn't check that before posting, sorry). But, the question arose because of difficulties pasting numbers that become transferred to dates. E.g. pasting 14.60 (measured time in minutes and seconds) will result in 14-12-07 with the locale Dutch. Changing the locale to English VS makes that the number stays the same. And the changing the locale back again ... Has there ever been thought about a possibility to turn that feature off (some might call it an option, but you won't here me say that word ;-) ) Kindest regards, Cor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Name for RCalc - tentative R4Calc
Wojciech Gryc wrote: Okay! So I guess this means we're going with R4Calc and R4Calc - R Statistics for OOoCalc... Yay! Niklas -- can we set up a CVS, in this case? Thanks, Wojciech Hi Wojciech, Great! I still don't understand why the paste is broken (and some other things - it's still broken in m233). It is probably my dev-build. I will need to test it in the latest build (m234), but I am a little bit short of time. Maybe I will wait for a forthcoming build (m235?). Unfortunately I did not have time to test it further. Does anyone on this list experience the same problems? Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] RCalc (R4Calc) OOo-java issue?
Hi all, I mentioned already the many problems that I have with RCalc on OOo dev2.3 m231. There are also problems on OOo 2.3 stable, and commands that did work do not work anymore, while some obvious NON-RCalc things are broken, too. I came across this message: http://www.openoffice.org/servlets/ReadMsg?list=interface-announcemsgNo=1012 Does this have to do anything with my problems? I have currently very limited time to test it further and hope to have some more time during the weekend. Sincerely, Leonard -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] R/Calc 0.1.11
Hi, Wojciech Gryc wrote: Hi Leo, Thanks very much. This is baffling me. I don't have access to Windows for a few days, and I don't experience the problem on Linux (Ubuntu Feisty Fawn, OO.o 2.3)... Anyone else seeing this message? The paste-problem does NOT happen with OOo 2.3 stable, but only with the development branch (dev 2.3 m231). I haven't had time to test it with a newer development branch (dev 2.3 m233 is latest). However, in the m231 branch almost nothing works: NO paste in the coding window, hand written commands do NOT work either, menu-commands are broken (crash RServe). I tend to believe that - at least the paste-problem - is related to the m231 branch. I hadn't had time to test it with OOo 2.3 stable, but various things seem to be broken on my machine, too (though pasting works fine; IF I paste the 'Multivariate Linear Regression'-code from http://wiki.services.openoffice.org/wiki/R_and_Calc_Documentation, however, it will crash RServe!). [Win2k SP4, R2.5.0, OOo 2.3 stable and OOo 2.3-dev m231] Hope to have slightly more time next week and test it more extensively. B. NAME = Regarding the name, I prefer the R4Calc name, though if you feel R_Calc is OK, I do not mind using it. [However, I feel that R_Calc and RCalc are too similar, and underscores are usually not used in package names because they tend to confuse users.] Sincerely, Leonard Thanks, Wojciech On 10/10/07, Leonard Mada [EMAIL PROTECTED] wrote: Hi, I found a very annoying bug with the Coding Window: Whenever I try to paste something inside the coding window, the following message is displayed and nothing gets pasted: The inserted text exceeded the maximum length of this field. The text was truncated. Actually, nothing gets pasted, irrespective how long the text is (not even a single character gets pasted.) Tested with OOo 2.3-dev m231 and RCalc 0.1.11 on Win2k SP4. [R and the like are irrelevant for this issue.] Hope this gets a speedy fix. And thanks for the good work. It is nice to see your work on this project going on. I contacted the UX-mailing list today to ask for some guidance on naming RCalc. [The mailing lists were broken in the last several days, so I decided to wait until that issue got fixed first.] I hope to get some useful suggestions. Sincerely, Leonard Wojciech Gryc wrote: Hi everyone, A month since the last release, I figured it was time to let you know there's a new version. It only contains a few minor changes... One of them being that error output is a bit more formal now rather than non-existent. http://wiki.services.openoffice.org/wiki/R_and_Calc Let me know if there's other things I should add. The features page on the wiki is definitely growing, so I'll do my best in the next few weeks. Thanks, Wojciec - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] R/Calc 0.1.11
Hi, I found a very annoying bug with the Coding Window: Whenever I try to paste something inside the coding window, the following message is displayed and nothing gets pasted: The inserted text exceeded the maximum length of this field. The text was truncated. Actually, nothing gets pasted, irrespective how long the text is (not even a single character gets pasted.) Tested with OOo 2.3-dev m231 and RCalc 0.1.11 on Win2k SP4. [R and the like are irrelevant for this issue.] Hope this gets a speedy fix. And thanks for the good work. It is nice to see your work on this project going on. I contacted the UX-mailing list today to ask for some guidance on naming RCalc. [The mailing lists were broken in the last several days, so I decided to wait until that issue got fixed first.] I hope to get some useful suggestions. Sincerely, Leonard Wojciech Gryc wrote: Hi everyone, A month since the last release, I figured it was time to let you know there's a new version. It only contains a few minor changes... One of them being that error output is a bit more formal now rather than non-existent. http://wiki.services.openoffice.org/wiki/R_and_Calc Let me know if there's other things I should add. The features page on the wiki is definitely growing, so I'll do my best in the next few weeks. Thanks, Wojciec - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: R / Calc Name ?
Hi, This has proven a difficult task! :) I definitely prefer debugging... Sometimes. I really like RCalc -- it was a natural way to go, but Google's search results made it pretty clear we'd be way too similar to the currently existing RCalc program. Aside from that, roose is good... It sounds like ruse which has the following definition: a deceptive maneuver (especially to avoid capture). I thought about this. Maybe Rose is a better alternative? [Though see also http://www.llnl.gov/CASC/rose/ ] I think a poll might be useful: 1.) CalcStaR 2.) Rose (or ROOSE, though I now think 'Rose' is nicer) 3.) RO-USE Any other ideas? Sincerely, Leonard -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Re: R / Calc Name ?
Hi to everyone! Wojciech Gryc wrote: Hi, ... Finally... Should we name the add-on? I'm not very good at this, but I'd like to get your thoughts. Thanks, Wojciech I was thinking more thoroughly about a filename and I came up with the following proposals: 1.) RCalc = current name; simple and descriptive 2.) oofiRe = OpenOffice R Extension; simple; in the spirit of R 2') OOFIRE [though slightly inaccurate: only one 'f'] 3.) OORE = OpenOffice R Extension 3') ooRe 4.) RooT = R-OpenOfiice Tool 4') ROOT 5.) Rstate = R Statistical Engine/Extension Does anyone has a better idea? I am eagerly awaiting further suggestions. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: R / Calc Name ?
Hi, Sorry for the many posts, but I became even more creative: 1.) ROOSE = R-OpenOffice Statistical Extension/Environment 2.) iCaRuS = integrated Calc-R utility for Statistics 3.) StatOOR = Statistics in OpenOffice with R Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: R / Calc Name ?
Leonard Mada wrote: Hi to everyone! Wojciech Gryc wrote: Hi, ... Finally... Should we name the add-on? I'm not very good at this, but I'd like to get your thoughts. Thanks, Wojciech I was thinking more thoroughly about a filename and I came up with the following proposals: 1.) RCalc = current name; simple and descriptive 2.) oofiRe = OpenOffice R Extension; simple; in the spirit of R 2') OOFIRE [though slightly inaccurate: only one 'f'] 3.) OORE = OpenOffice R Extension 3') ooRe 4.) RooT = R-OpenOfiice Tool 4') ROOT 5.) Rstate = R Statistical Engine/Extension Some more thoughts: 6.) StatistikeR [German for statistician; in the spirit of R] 7.) computeR = compute with R 8.) CalcStaR = Calc statistics with R Does anyone has a better idea? I am eagerly awaiting further suggestions. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] R/Calc - 0.1.6
Hi, Wojciech, Niklas Nebel wrote: Wojciech Gryc wrote: From an administrative point of view, I am supposed to stop working as a Google Summer of Code student today at 3 pm EST. As such, I've leased a new version of the package: 0.1.6, with links at http://wiki.services.openoffice.org/wiki/R_and_Calc Great to see graphics from R. Someone might want come up with a more colorful, shiny example, for the wow effect. First, I should say that I will continue working on this package and won't stop just because the Summer of Code period is over. :) However, I would be grateful for any advice about this new release prior to 3 pm, so I can make any changes before submitting this work for evaluation. Some comments (the first two seem to be the same problems that Leonard had): - The dialog txt files aren't found without strange CLASSPATH settings. - On Windows, R doesn't seem to like the backslashes in the path to the jpeg file. Replacing them with forward slashes seems to work, I didn't check if it's supposed to be that way. Well, now that the culprit is found, I hope in a fast solution. One small note with R: - on Windows, IF you want to use a path with '\'-slashes in R, you need to escape the slashes, like: '\\' - R accepts the '/'-slashes on windows, too - I don't know IF one needs to additionally escape the '\'-slashes in Rserve, too (like in some regexp's, having to write '' for a single literal '\') Hope this helps to solve the Windows problems. And keep the good work. Sincerely, Leonard - Compiling is still a bit painful. SDK sets environment variables for its own and office's path. Why don't you use those? - I also had to add . to CLASSPATH do get it to compile. - I'm not sure if the RServe client sources should be in the source package. Niklas - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] A problem need assurance!
Hi, maoyg wrote: Hello all, 1.create a new Calc. 2.input characters into any cell,select part of the characters , click 'background color'. Result: 1).The cell can be colored. 2).When undo, characters disappear,the cell color still exists. I'm looking forward to your help! this is indeed a bug in OOo 2.2, BUT it has been fixed in OOo 2.3 dev (m222?). In the OOo 2.3 dev version, 'Undo' is disabled. When clicking outside the cell, the 'Undo' becomes available again and it works correctly. However, I agree, that the 'Undo' should be really fixed and not just disabled! Sincerely, Leonard Thanks Best Regards Yonggang maoyg 2007-08-20 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] R/Calc - 0.1.6
Hi Wojciech ,
I did some further tests this afternoon. The results are encouraging,
though I found additional errors:
1.) spaces before {$OUT:...} prevent any output, e.g.
_{$OUT}-...something...
where '_' is a space won't work.
2.) I still have problems with the PLOT. Both with Win XP SP2 and Win2k SP4.
Where is the temporary file stored? Both machines I tested on do not use
standard MS Win directories, so this may be an issue.
Here, I have:
OOo 2.3 dev installed in L:\Programs\Text\OOo-dev 2.3, and
Rserve in L:\Programs\Science\Statistics\R\R-2.5.0\library\Rserve.
Needless to say, R is in:
L:\Programs\Science\Statistics\R\R-2.5.0
Maybe there is an issue with the program-path. (The Win %PROGRAMS% path
is also set to L:\Programs\.) This is the Win 2k machine.
The Win Xp machine has also some special settings, though I don't
recall exactly what they were. (Though the Win %PROGRAMS% should be the
standard one. However, I installed R on a different path.)
3.) This brings me to the 3rd issue: the R-Addon Menus do NOT work. They
actually never worked on my machine. Not even the GUI is displayed.
When I manually insert the path to the GUI *.txt-file downloaded from
the source zip-file, the GUI shows up (and the non-graphical commands do
work - at least some of them).
Should the user download separately these GUI-files? Or should they
have worked when installing the uno-package?
Hope this information will allow pinpointing the problem.
Sincerely,
Leonard
Wojciech Gryc wrote:
Hi everyone,
From an administrative point of view, I am supposed to stop working as a
Google Summer of Code student today at 3 pm EST. As such, I've leased a new
version of the package: 0.1.6, with links at
http://wiki.services.openoffice.org/wiki/R_and_Calc
First, I should say that I will continue working on this package and won't
stop just because the Summer of Code period is over. :) However, I would be
grateful for any advice about this new release prior to 3 pm, so I can make
any changes before submitting this work for evaluation.
Below are some comments to specific people, based on advice they gave last
week.
Eike -- this new version uses make rather than sh scripts... Please have a
look at the makefile, because this was the first time I've actually coded in
make. To make life easy for people using the package and those coding in it,
I moved the Rserve clients (licensed under LGPL) into the package itself,
giving appropriate credit and information where necessary. I also
reorganized the code a bit to make life easy for anyone else working on the
project.
With regards to the bulky image embedding code, my apologies! I didn't
realize there was a Calc tutorial for this already.
Leo -- I implemented the Chi Square, Fisher Exact, Wilcox tests, and also
the Kaplan-Meier curve. The other two (from the survival analysis package)
will be harder to implement because there is currently no easy way to accept
an unlimited amount of formula variables as inputs using the GUI windows. I
will work on this, as well as dynamically setting array sizes (e.g.
{$A1:A(length+1)} -- this is a pretty big change, so I wanted to fix some of
the bugs and problems from version 0.1.5 before addressing this.
I also created an About button in the Advanced sub menu which gives all
sorts of diagnostic information. Please let me know if this works.
Also, I think the problem with plotting had to do with where I stored the
temporary image file... I changed this, so please let me know if it works
for you! I tested the tool on Ubuntu, Fedora Core, and Windows XP and it
worked in all cases, so I hope Windows 2K will also cooperate now.
Again, comments are welcome! Thank you to all who have already sent them.
Thanks,
Wojciech
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: Exciting News
Hi,
Eike Rathke wrote:
Hi Leonard,
On Wednesday, 2007-08-15 23:04:55 +0300, Leonard Mada wrote:
be careful when pasting from the wiki, as the wiki-features required to
insert a space between 2 braces, changing '[[' to '['_space_'[' which
breaks the code. One has to convert back these double-braces.
Embed such code examples in pre tags instead of space-indenting
them. See correction I applied.
Eik
I have improved the code even further: more descriptions and NO need to
break any line. Everything fits now easily on one line.
Leonard - Wojciech
I encountered a possible bug: I was NOT able to import text/strings
from the spreadsheet into R. This is useful to have the variable
names/annotations in the output.
And a last question: in order to have a generalization of the
3-dependent variables linear regression to 'n'-variables, I need a way
to have dynamic output matrices, like:
{$OUT#C(_some_column_)R(y):C(_some_column_)R(y+length)} - _the_output_
e.g. len - length( _input_ )
{$OUT#G1:G(1+len)} - _some_output_
Is this achievable?
Thanks and keep the good work,
Leonard
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: Exciting News
Hi Wojciec,
I detected some further potential bugs:
Can you please look into the following code: [in the coding window]
1.) IF there is NO input from the spreadsheet first, the script won't run:
e.g. {$OUT#E1}-HELLO WORLD
or x-25
{$OUT#E1}-{$BASE}
2.) for some reason, the PLOT command does NOT work on my installation.
It crashes Webserv.
System: W2k SP4
R version 2.5.0 (2007-04-23)
i386-pc-mingw32
locale:
LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods
[7] base
I hope you can find the culprit.
Many thanks,
Leonard
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Re: Exciting News
Hi Wojciech,
Indeed, I am eagerly awaiting to see the graphics implemented.
A.) In the meantime I have added another example on the
http://wiki.services.openoffice.org/wiki/R_and_Calc_Documentation#Coding_Window
wiki page.
The last example shows a multivariate linear regression model (including
model testing using the ANOVA-test). Should work fine. However, please
be careful when pasting from the wiki, as the wiki-features required to
insert a space between 2 braces, changing '[[' to '['_space_'[' which
breaks the code. One has to convert back these double-braces.
While the model is written for 3 independent variables, one can easily
extend it to more variables (and even add non-linear cross-effects like
x[,2]*x[,3]). One just has to be careful with the indexes of the return
object.
B.) When I have a little bit more time (maybe during the week-end), I
wish to move the R-pages to /Calc/To-Dos/Statistics/... in order to have
the site more structured.
C.) I will think also about various language-design details later on. As
an idea, maybe it would be useful to have a 'print'-method and a
'summary'-method, that outputs exactly the 'print(R-object)', as when
writing
_my_R_object [ENTER] - the print method is internally called
... some output
Another method to know the names in the resulting R-object is to call
the function names('R-object') which will output all names, e.g.
fit-lm(x~y+z)
names(fit)
[1] coefficients residuals effects rank
[5] fitted.values assignqrdf.residual
[9] xlevels call terms model
For (S3)-methods, one can use (one has to know the class):
methods(class=lm)
[1] add1.lm* alias.lm* anova.lm
case.names.lm*
[5] confint.lm*cooks.distance.lm* deviance.lm*
dfbeta.lm*
[9] dfbetas.lm*drop1.lm* dummy.coef.lm*
effects.lm*
[13] extractAIC.lm* family.lm* formula.lm*
hatvalues.lm
[17] influence.lm* kappa.lm labels.lm*
logLik.lm*
[21] model.frame.lm model.matrix.lmplot.lm
predict.lm
[25] print.lm proj.lm* residuals.lm
rstandard.lm
[29] rstudent.lmsimulate.lm* summary.lm
variable.names.lm*
[33] vcov.lm*
Non-visible functions are asterisked
One can see, that there is a 'summary.lm'-method, which is internally
called when one writes summary('fit_model').
Hope this helps further. I will also think more thoroughly about further
improvements.
Sincerely,
Leonard
Wojciech Gryc wrote:
Hi Leonard,
Yes, right now the Launch Rserve command doesn't work... I have no
clue why, and so far talking with OO developers hasn't yielded
anything useful. I will keep trying!
Worse comes to worst, I'll make a script that launches Rserve along
with a Spreadsheet, though this is a suboptimal solution in my view.
I will add the diagnostics this week. I first want to get image
imports to work, and then I'll definitely do this, along with adding
the tools you mentioned.
THANK YOU SO MUCH for updating the wiki with coding examples. This
really made my day. :) How did you find coding in it? If there's any
way I can make the coding syntax easier to use, or even improve the
user interface, please let me know...
Right now my biggest concern is that you still need to now the R
object structure fairly well to code in the window, but I'm not sure
how to show people what's available as they code. Any ideas?
Thanks,
Wojciech
On 8/14/07, *Leonard Mada* [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
Hi Wojciech,
nice to see further development.
I had some problems to run the package and did not have time until now
to look into the issue any further. But I installed today v 0.1.4 and,
although I had again problems running it, I was fortunately able,
after
some tweaking, to get it to work. It is really mysteriously, though I
have no clue what was going on (RCONNECT() returned always 0, although
the dialog box kept saying Rserve started. I start it now manually.)
I played a little bit around and by the way, wrote some *coding
window*
examples on
http://wiki.services.openoffice.org/wiki/R_and_Calc_Documentation#Coding_Window
http://wiki.services.openoffice.org/wiki/R_and_Calc_Documentation#Coding_Window
.
In addition, I would like to have some menus for the following
R-commands:
[Menu Entry] - [Sub-Menu]: [R-Syntax]
Count Data
- Fisher-exact Test: fisher.test({#R1C1:R2C2})
- Chi-Square Test: chisq.test({#R1C1:R2C2})
where R1C1:R2C2 is the 2x2 contingency table (aka 4 cells are passed;
one can test more than 2 groups, but probably 2x2 contingency
tables are
the most frequent)
Non-Parametric Tests
- Wilcoxon Rank Sum Test: wilcox.test({#R1C1:RnC1},{#R1:C2
Re: [sc-dev] Duplicate Data
Hi Eike,
Eike Rathke wrote:
...
Nice idea, but I doubt we'd implement that soon. Apart from that, a
hard link to a cell containing a formula should be prevented, and
entering a formula in A1 would have to break all hard links pointing
to A1 and change them to soft-linked usual cell references. To me
this appears as a new source of user confusion.
Well, I see, there are some difficulties in implementing hard-links, but
they are solvable. I came up with the following idea:
a.) with array-formulas, one has: '{=_some_formula_}'
b.) basically, hard linked cells should be _displayed_ similarly,
using e.g. '[]'-braces
i.) non-formulas, i.e. proper values, should be displayed in the
'Input line' as:
[ _the_value_ ] when clicking on the cell (without actually
editing the value)
_the_value_ when editing or clicking inside the formula
input line editor
ii.) for formulas, display:
[= _the_formula_ ] when clicking on the cell
= _the_formula_when editing the formula
iii.) for array-formulas, display:
[{= _array_formula_}] when clicking on the cell
= _arraay_formula_ when editing the formula
So, basically:
- add the '[]'-braces when displaying the result in the 'Input
line'-editor
-- this avoids confusion and clearly identifies hard-links
- remove the '[]'-braces when editing the values/formula
- accept changes with simple 'ENTER' for non-array-formulas
- accept changes with 'CTRL+SHIFT+ENTER' for array-formulas
The '[]'-braces are nothing special, they are just here to avoid
confusion. The actual changes are made in the original cell. [Creating
and removing hard-links should be done through the menu/context-menu.]
With this solution, I do not see why entering a formula inside 'A1'
should break the hard-links (and convert them to soft links).
Implementing this is surely a complex task, but I am sure it is doable.
If we link to a cell that contains a formula or a reference, the result
is devastating. Lets say, in document 1 we have a column named MySum,
where each cell is =SUM(Ax:Bx)
Now we link from a different document 2 to this column. What we actually
get is =SUM(AxBx), but Ax and Bx refer to this new spreadsheet, not the
original one, where these values are stored.
I don't follow here. When linking to another document you get copies of
the results of the cells linked to, not the formulas themselfs. So you
still get SUM(Ax:Bx) of the original data.
There are two ways to link to external data:
1.) The Menu-entry : 'Insert' - 'Link to External Data...'
- this does NOT work with formulas. Maybe I am doing something
wrong, but I could not get it to work.
- tried it with OOo 2.3 dev (222?) and still no success.
- formulas (and named ranges) point to current document cells,
and NOT to the original-document cells,
therefore they are non-functional
2.) Linking directly through writing a reference to the particular
cell in the original document, e.g.
type in a cell in the new document: '=
_original_document_#_cell_to_be_referenced_'
- this works, but is more cumbersome
REASON:
- when one creates a spreadsheet and wants to export some data,
than it is easier for the
*original* *developer* to create some named ranges that SHOULD
be exported
- the end user, that needs that particular data for a 2nd
spreadsheet (he is not necessarily the same developer)
just links to these named ranges (using the 'Link to External
Data' mechanism
- he does NOT need to know the structure of the first
spreadsheets and
search for the relevant data fields
Well, I hope this clarifies some of the issues.
Sincerely,
Leonard
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] i20496 : Enhanced formula input
Hi all,
there is one thing I definitely want to avoid - at all cost: namely
converting non-formula input into a formula.
Lets say the user enters: +some_string
IF 'some_string' is NOT a valid formula, then:
A.) IF this would be converted to a formula, Calc would evaluate it
and display
#NAME?, #VALUE? or the like
So, the user has to *come back ANYWAY* and correct it, IF he
indeed wanted a formula.
B.) However, IF he did NOT want a formula, then he is forced to come back
and undo the formula changes back to non-formula
= these are 2 additional steps!!!
= AND actually the formula code would convert it back to formula
So, the user who wishes a formula does NOT pay any penalty IF the
ERROR-formula is NOT automatically converted to a formula (as he has to
correct it anyway, and after that it would be converted automatically),
BUT the user who does need the actual string pays a very high price (up
to the point that he cannot enter strings starting with + or -).
I do use extensively strings in my work, so I hope this gets fixed.
(including strings starting with mathematical signs: +, -, *). (') is
not an option because on various keyboards it is not easily accessible.
Sincerely,
Leonard
Eike Rathke wrote:
Hi Muthu,
btw, this would be sorted into the corresponding thread if you replied
on the original mail instead of some digest.. anyway.
On Thursday, 2007-08-09 13:55:13 +0530, Muthu Subramanian wrote:
Have you tried the patch that I have attached (in issue 20496)?
No, that issue went out of my sight until recently when it came to life
again.
It would (really) be easier if we can comment on the changes required on
the patch (I think).
I think we should first clarify the behavior we really want. Otherwise
talking about necessary changes to the patch is moot.
1. +func( should also cause a formula input (exception for +number).
If so, then any formula should be evaluated, not just +number or
+func( Doing so would evaluate +string input as a formula and lookup
whether string happens to match a defined name, and result in #NAME?
if it doesn't. Which is what Excel does.
2. And w.r.t to the ++ - =++ its for the similarity with excel (and also +
- =+).
Excel converts +number+number to =number+number and ++number
to =+number, but converts +name to =+name
Note that Excel does not convert single numbers like +1 or -1 but does
convert ++1 to =+1, --1 to =--1, +-1 to =+-1 and -+1 to =-1 (all without
any other trailing operators) which I find pretty disturbing.
Eike
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] i20496 : Enhanced formula input
Hi all, sorry for another post. Eike Rathke wrote: ... 2. And w.r.t to the ++ - =++ its for the similarity with excel (and also + - =+). Excel converts +number+number to =number+number and ++number to =+number, but converts +name to =+name Note that Excel does not convert single numbers like +1 or -1 but does convert ++1 to =+1, --1 to =--1, +-1 to =+-1 and -+1 to =-1 (all without any other trailing operators) which I find pretty disturbing. What is the purpose of '--1' = '=--1' --1 is really +1, so, IF someone is concerned with typing fast a formula, it defies my logic to enter '--1' instead of '1'. IF I do use somewhere '--1', than that is because I need the '--' preserved, and therefore I need it as a string. [converting to '=--1' would preserve the '--', BUT I question the applicability of a formula in this instance.] I have serious reserves in converting '--1' and '++1' into formulas. Maybe there should be an additional option where one can turn this on (beside an option to turn the formula thing completely off), but I am yet to convince that this is useful. [Or at least don't convert to formula IF the cell type is set to text!] I also fear, that when units (and strong typing) will be implemented in spreadsheets (a thing I predict from the research going in the field will come rather sooner than later), such automatic formula conversions will make life very hard. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] all the icons are on at the same time
Can reproduce this bug with OOo 2.2.1 (en locale) on Win XP. When the 'Design Mode' is ON, clicking sequentially on the 'Forms Control' icons will highlight them all (select them all). [Altough, when drawing the control inside the spreadsheet only the lastly selected control is actually inserted.] Sincerely, Leonard Original-Nachricht Datum: Fri, 3 Aug 2007 11:10:55 +0800 Von: yunjingtao [EMAIL PROTECTED] An: dev@sc.openoffice.org Betreff: [sc-dev] all the icons are on at the same time Hi,all 1.Create a new Calc document 2.View-Toolbars-Form Controls 3.Design MOde On/Off-Check Box-Text Box-Push Button-List Box-... In other words,make sure design mode is on,only click icons and do not execute their actions,click check box,text box,push button,formatted field,option button,list box... 4.You can find that all the icons are on at the same time. I know that all icons are enable,but the status shows they are all on. 5.There is no such phenomenon in Writer. It happens to 2.1 and the latest 2.3.0 Is this a bug?If it is,how to fix it?Please elaborate on it. Thank you! I don't know whether I depict it clearly,if you can't understand it,please email me and I'll try again. -- Open WebMail Project (http://openwebmail.org) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Is printing 0-values from formula's normal?
Hi, I would vote for BUG. But lets ask other folks, too. Sincerely, Leonard Cor Nouws wrote: Hi *, Cannot find an issue, so maybe by design? A speadsheat has formulas that (somtimes) result in zero. Settings ar to hide zero-values, so those cells are empty. However print preview and print do show them. 1.1.5 / 2.2 Bug or design? Thanks, Cor - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Is printing 0-values from formula's normal?
Hi, Regina Henschel wrote: Hi Cor, Cor Nouws schrieb: Hi *, Cannot find an issue, so maybe by design? A speadsheat has formulas that (somtimes) result in zero. Settings ar to hide zero-values, so those cells are empty. However print preview and print do show them. 1.1.5 / 2.2 Bug or design? No bug, but feature. The settings for display and printing are independent. The setting for printing is in the page style in the tab sheet, the setting for display is in Tools Options. However, shouldn't the print command print primarily what is *seen on the screen*? So changing the Display-details should overwrite the printing details. And only IF the printing details are then SET MANUALLY to allow printing of hidden zero-values, then this should take precedence? Kind regards, Leonard kind regards Regina - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Duplicate Data
DUPLICATE DATA [was: Re: [sc-dev] MOVE Calc Usability Page?] Hi Eike, I have partially rewritten the wiki in the hope to clarify this issue (see http://wiki.services.openoffice.org/wiki/Calc/To-Dos/Usability#Duplicate_Data). Eike Rathke wrote: Hi Leonard, On Tuesday, 2007-07-31 21:35:07 +0300, Leonard Mada wrote: For i80139 please note that for 3.2 DATA LINKS external references are already implemented (could be enhanced though to not create an internal sheet and handle things a bit different) Nahh, a bidirectional-link does not work (neither for 2 different spreadsheets, nor for intra-spreadsheet links; the latter link-mechanism does not exist at all). If I link to a cell having a formula, it does not work, either. So it is basically never usable, because most of the time there will be a formula somewhere in the spreadsheet blocking this. I may have misunderstood what you actually wanted to achieve. Are you speaking of referring external data, or links in the sense of URLs that take you to some different location when clicked? Please elaborate. Lets say, we have in cell A1 = 50. In cell B1 we write: =A1. Now, lets assume, we find out later that '50' is wrong, it should have been '20'. In this trivial example, one may change the value in cell A1. BUT in a non-trivial example, A1 is hidden in a 100,000 cells spreadsheet and one is actually working with the data copied from the original table (after some filtering) in a second table. One sees the cell B1 and wants to operate the change here. The change in B1 should be ported then back into cell A1. What we need is a bidirectional reference that links cells A1 and B1, so that when I change B1, this change is reflected in the value stored in A1, too. This is like a Unix Hard-Link! A1 and B1 should be hard-linked. Please note, that we may have many B1-cells (copies of the original data), so we need to change every single value (IF we do not have references in place, BUT copied only the data). For different spreadsheets, we may use the link-function. BUT: If we link to a cell that contains a formula or a reference, the result is devastating. Lets say, in document 1 we have a column named MySum, where each cell is =SUM(Ax:Bx) Now we link from a different document 2 to this column. What we actually get is =SUM(AxBx), but Ax and Bx refer to this new spreadsheet, not the original one, where these values are stored. So I will have to link to ALL data. Then what is the simplification of doing the analysis in a new spreadsheet? I need to copy everything into the new spreadsheet. I don't think there is a complex spreadsheet *without formulas*, so basically this functionality is useless for me, too. 3.3 TRACK CHANGES is already implemented (see menu Edit.Changes), and 3.4 VERSIONING is already implemented (see file save/load dialog). Which leaves only 3.1 COLLABORATIONS that is already handled by some other issue. Nahh, mediawiki has a far better versioning/changes functionality than every spreadsheet application. And that is rather a poor alias for spreadsheets. Therefore, I am eagerly awaiting to see something more advanced, something really mind breaking. Well, you should have pointed that out that more clearly it seems. Eike I have posted another issue (see http://www.openoffice.org/issues/show_bug.cgi?id=80325) detailing some of the track changes issues. This is only a glimpse of what is doable and even I may not yet think of everything that is needed. Though functional areas and tracking changes in a computed value (downstream) are surely high priority. [By the way, I do not use the existing track changes, because in its current state it is largely useless in my work.I would need years to track every change.] Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Formula import for OOXML
Hi, Kohei Yoshida schrieb: Hi there, ... Also, how are we dealing with Excel's shared formulas? I have implemented shared formula import for the OOX and BIFF filters. ... What are shared formulas? Is it the same as the concept I presented in a previous e-mail, where one can have a default formula for a column? This is indeed a very useful feature to have in OOo Calc and is one issue that gets into my TOP 5 of major design flaws of existing spreadsheets. It definitely helps reduce the spaghetti code inherent to spreadsheets and is also an important concept when extending the simple spreadsheet to multidimensional spreadsheets. I will file a new feature request when I will have some time. Sincerely, Leonard Mada -- GMX FreeMail: 1 GB Postfach, 5 E-Mail-Adressen, 10 Free SMS. Alle Infos und kostenlose Anmeldung: http://www.gmx.net/de/go/freemail - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Formula import for OOXML
Daniel Rentz wrote: Leonard Mada schrieb: What are shared formulas? Is it the same as the concept I presented in a previous e-mail, where one can have a default formula for a column? This is indeed a very useful feature to have in OOo Calc and is one issue that gets into my TOP 5 of major design flaws of existing spreadsheets. You cannot create shared formulas by yourself, neither in Calc nor in Excel. Excel creates them automatically e.g. when pasting a formula cell to a range selection. They are not visible in the user interface. A similar concept to have a named formula definition for multiple cells are defined names (Insert-Name-Define). *Default Formulas* (defined for a column) was a new concept of the late '80s-early '90s and are important in multidimensional Spreadsheets. Sun even has bought the rights for such a next-gen spreadsheet in the mid '90s but never developed it further. This is one major BUG that makes it into my TOP 5 of major design flaws of existing spreadsheet applications and I will file such a feature request later (in my series of major design flaws). It definitely helps reduce the spaghetti code inherent to spreadsheets and is also an important concept when extending the simple spreadsheet to multidimensional spreadsheets. I will file a new feature request when I will have some time. Shared formulas are intended to reduce memory usage and file size, don't know if they would help you. What do you mean with spaghetti code? Daniel One effect of shared-formulas is surely to reduce memory usage, BUT the most useful one is to reduce ERRORS. You write only once: C[] = A[]*B[], or E[] = C[]+D[]. You avoid in this void the spaghetti code inherent to spreadsheet programming. Also , this concept is very useful for multidimensional spreadsheets. Lets say we have the following dimensions the name of the retailer quantity of items sold in this month price of every item the months: January, February till August Then the multidimensional formula would be: revenue[retailer]=SUM(quantity[]*price[]) for the monthly revenue global_revenue[retailer]=SUM(revenue[January:August] Risk of error is greatly reduced. And you have all the data fully organized and easy to analyse. Adding another month simply recalculates the values for the additional month: *NO formula change needed*!!! Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] MOVE Calc Usability Page?
Hi, there is a new Calc Usability wiki page (see http://wiki.services.openoffice.org/wiki/Calc_Usability_Activities). However, I think the page should be moved to /Calc/Usability to bring some structure to the OOo site. Should I proceed with the move operation? By the way, I have filed 2 issues that make it in my *TOP 5 of major design flaws* in existing spreadsheet applications. Both can be read at the bottom of the 'Calc_Usability_Activities' page. When I will have some more time (maybe later in the week), I will file the next 3 issues (including a.) the default formula per column I already mentioned, b.) a restructuring of the spreadsheet view area/data area/document structure and c.) separation of data/content from style). I have added 2 additional issues on the Talk page which do deserve to be implemented as fast as possible into Clac. I have to point again at the SQL-feature request. Although it is a niche requirement, as it is basically one that brings most of the MS Visual Fox Pro utility into play, it is likely to be useful in enterprise settings and therefor will generate revenues. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Updating Statistical Wiki Page
Hi, Eike, thanks for the help. Eike Rathke wrote: ... 1. Move (! by using the wiki's move mechanism, visible when going to edit mode) the Statistical_Data_Analysis_Tool page to Calc/To-Dos/Statistical_Data_Analysis_Tool Moved! Unfortunately, I did not manage to create a new page, despite countless attempts and searches on the site for such help. A new article can be created by either attempting to direct the browser to a non-existing article, for example ... I knew there must be a reason why NO 'Create New Wiki Page'-button was displayed! Well, I began reworking the page. It will take some time to finish it. Thanks, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Updating Statistical Wiki Page
Hi, I would like to update the statistical wiki page a little bit, and started yesterday to think about some enhancements. [see http://wiki.services.openoffice.org/wiki/Statistical_Data_Analysis_Tool] The reason is, that the page looks already too cluttered and I would like to reorganise slightly the information. In this respect, I consider that it becomes imperative to move some of the content to a new page. I think of moving the *Descriptive statistics* to a dedicated page(something like *Descriptive Statistics*), and - later - moving other parts as well to their dedicated pages. Unfortunately, I did not manage to create a new page, despite countless attempts and searches on the site for such help. [I am maybe too ignorant and have overlooked something. My apologies in that case.] I would be grateful for any help on this issue. Sincerely, Leonard -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] stable algorithm for statistic functions issue 78250
Regina Henschel wrote: ... I'll work on CORREL, PEARSON, RSQ, STEYX, SLOPE, INTERCEPT and FORECAST (all in interpr3.cxx), which I think all suffer from the same square-problem. The function GetStVarParams in interpr1.cxx is OK in this respect and therefore also the DEVSQ, STDEV and VAR. kind regards Regina Great! Let me know if there are any questions regarding the algorithms or the statistics. I was pretty busy lately (and still am :-( ), but I would try to free some time to solve the problem if there is one. Many thanks, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Table Highlighting
Hello Niklas, helle Eike, I posted some time back a feature-request to implement a special spreadsheet-view in which rows get *highlighted* (see http://www.openoffice.org/issues/show_bug.cgi?id=78181). Virtually every table on the net is displayed this way. Also, most web applications have implemented it (see e.g. phpMyAdmin for one, but there are countless others). The code for the net applications is a mere 20-line java-script (I used it, too). Would it be possible to implement this in Calc? And who is most responsible for these UI-related issues? Thanks, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Slow copy in SC
I believe, too, that this should be handled better. Though I haven't thought about a better way to do it. When you have dozens of pictures in the spreadsheet and begin to copy/move cells around, it becomes a mess. Maybe one should be able to lock somehow a picture to a particular cell? Sincerely, Leonard Mada Original-Nachricht Datum: Thu, 28 Jun 2007 11:13:17 +0800 Von: zhaosheng [EMAIL PROTECTED] An: dev@sc.openoffice.org Betreff: [sc-dev] Slow copy in SC Hi,all I found something in SC, IMHO, it may be annoying to some people. it is the inserted picture. if you insert a picture into a cell, it acutally doesn't insert the picture to the cell, right? it seems to be inserted floating on the whole spreadsheet, you can move it anywhere. but the thing is if you select the cell under the picture, you also select the part of the picture together, and then if you copy the selected cells, sometimes you can copy the picture too.sometimes not. Is it easy to operate? Regards zhao sheng - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Request some assistance
Hi Bruce, Hi Leonard, Leonard wrote: Now, to give a usefulness to the 'CTRL'+'A' key, I return back to my previous post: give the user 2 choices 1.) first choice: display window where user can select the number of columns and rows (for default values see paragraph 2.) ) 2.) select everything UP TO: - the right-most column that is NOT empty - the bottom-most row that is NOT empty This way, the user can make a better selection. This idea is very too bad.The user use 'CTRL'+'A' is for fast,if you force user selecting paste range,then it is counterproductive. I'm looking forward to your suggestion. Best Regards Bruce Well, from my experience I do not believe that it is bad. Actually most times I want to select something I would opt for 2.) and sometimes I would like to specify the dimensions more accurately (i.e. 1). This is at least usable. As I said, I never encountered the situation that one wants to select everything up to 65,536 x 256 and nobody showed me an example to the contrary. As said, this is for the *PASTE* feature after a *CTRL+A* selection, so I do NOT mind IF CTRL+A behaves the same as before. It is just after one presses *PASTE* that this new feature should be implemented. Sincerely, Leonard -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Something about Filter function
Wouldn't issue 66588 (see http://www.openoffice.org/issues/show_bug.cgi?id=66588) make for a great filter enhancement? Sincerely, Leonard Kohei Yoshida wrote: On Thu, 2007-07-05 at 10:42 +0800, gaozm wrote: Hello all: I'm Gao Zemin, I would like to do some contribution to the Filter function of the Calc, I want to know that which parts of the Filter function in our Calc are still need to improve, I hope you can give me some suggestions on Filter function, thank you. I've also started some work on Excel 2007 style multi-string matching filter (or whatever people like to call it). It's basically an autofilter extension where you can select multiple items to display; rather than just one. The detail is in here: http://qa.openoffice.org/issues/show_bug.cgi?id=77677 For now, it's just an interal data structure change with no UI. But I'd like to expand on this as time permits, to make it visible at UI level. Kohei - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: R Questions
Hi, I did some further testing. LM - Niklas: please read point 3. below! (a question about caching) The connection is OK, both om W2k and XP with both R2.4.1 and 2.5.0, though I have some strange errors: 1.) Win XP BUG both RDump and the cell-function DO work under W2k BUT: on Win XP: RDump functions beautifully, yet all cell-functions give a #Name! ERROR - even RTESTCONNECTION() returns this ERROR, although, as I sad, RDump works, so the connection MUST be present I think this has something to do with the Calc - package integration (I am using OOo 2.2) 2.) the R-Addon Menu entries do NOT work (neither on W2k, nor on Win XP) 3.) you mentioned that rnorm(10) gives the same numbers and point on the wiki that this is due to the fact that R uses the same seed. I question this explanation. rnorm(10) will work beautifully inside RDump(). LM - Niklas: I believe this is more a Calc issue. IF I understad correctly, Calc tries to cache information and already calculated values. So, IF Calc encounters the same formula a 2nd time, then it possibly never runs the funciton again, BUT rather outputs the value from the Cache. Am I right? IF YES, how can one avoid this caching issue. Sincerely, Leonard Original-Nachricht Datum: Sun, 3 Jun 2007 23:03:30 -0400 Von: Wojciech Gryc [EMAIL PROTECTED] An: dev@sc.openoffice.org Betreff: Re: [sc-dev] Re: R Questions Hi Leonard, Thanks so much for trying this out. First, I'm glad the connection problem has been fixed. I also fixed the search() and (.packages()) bug. This was my fault mainly because Rserve has a number of different ways it deals with R objects. I've been doing my best to deal with all the structures (Vectors versus Lists versus other objects, filled with numbers, strings, and other structures), so by trying different methods that I can't think of, you're letting me become more familiar with the structure of the R objects. Regarding installation, if you already have RAddon.uno.pkg installed then you need to remove it before adding the new one -- this is why you got an error, and it's more a unopkg (OpenOffice) thing than mine as far as I know. What I'll do in the next few days is create some sort of basic installation script so that people can check. Until then, I updated the wiki accordingly. With regards to the range, you're inputting it correctly and it works perfectly on Linux but crashes in Windows. At this point I do not know why, but this is my first priority... I am asking around on the IRC channel right now and will let you know as soon as possible about a fix. Thanks, Wojciech On 6/3/07, Leonard Mada [EMAIL PROTECTED] wrote: Hello, I have some additional questions: I tried the following R-commands inside RDump(): search() should output attached packages (.packages()) print(.packages()) Neither (.packages()), nor print(.packages()) work. They should output the attached packages [ '.packages()' won't print anything, you need either (.packages()) or print(.packages()) for this.] .packages(all=TRUE) should output all *installed packages* Neither of these commands did work inside RDump! Does this relate to the way Rserve connects to R or is this a coding problem? sessionInfo() seems to work correctly and to report the packages. I tried therefore: (inside RDump) library(coin) = attaches the package 'coin' (needs to be installed on the machine) sessionInfo() = package 'coin' is displayed (together with the base and helper-packages). So, I do NOT believe that this is an Rserve issue. I insist on implementing debugging functions and working these issues out, because this will ease future development. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- Five Minutes to Midnight: Youth on human rights and current affairs http://www.fiveminutestomidnight.org/ -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Re: R Questions
Hello, I have some additional questions: I tried the following R-commands inside RDump(): search() should output attached packages (.packages()) print(.packages()) Neither (.packages()), nor print(.packages()) work. They should output the attached packages [ '.packages()' won't print anything, you need either (.packages()) or print(.packages()) for this.] .packages(all=TRUE) should output all *installed packages* Neither of these commands did work inside RDump! Does this relate to the way Rserve connects to R or is this a coding problem? sessionInfo() seems to work correctly and to report the packages. I tried therefore: (inside RDump) library(coin) = attaches the package 'coin' (needs to be installed on the machine) sessionInfo() = package 'coin' is displayed (together with the base and helper-packages). So, I do NOT believe that this is an Rserve issue. I insist on implementing debugging functions and working these issues out, because this will ease future development. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sc-dev] Re: R Questions
Hi Wojciech,
this issue is really tough. Indeed, my first response probably still
holds. It is still the most simple solution to use:
s - summary(the_nls_object)
and retrieve the information from 's'.
Yes, I know this is probably a suboptimal solution. In the meantime, I
have serious doubts that there will be a universal solution which will
cover every scenario.
The closest possibility might look like this (BUT it will be definitely
more complex than the method used until now):
Using either the PRINT-METHOD or the SUMMARY-METHOD
1.) PRINT
Every (is it really every object???) object can be printed by R.
If we type x - c(1,2,3,4) and then
x
R invokes the print function which will display x
Similarly, out-nls(y ~ x^beta, start=list(beta = 3)), then when typing
out
R invokes a special print and outputs the result.
2.) Varieties of PRINT
Unfortunately, there is NO unique print. What looks trivial, is a set of
complex methods different for various objects.
To get an idea of the many print-methods, type at the R prompt:
apropos(print)
Depending on the number of packages attached, it will display a number
of print methods (54 on my base-install, much more IF any packages are
attached).
For the default data-structures in R (atomic data), the 'print.atomic'
or 'print.default' methods will be invoked. Actually, 'print.atomic'
calls 'print.default', see:
getAnywhere(print.atomic)
Similarly:
getAnywhere(print.nls)
will show you the details of the function that prints the output for an
object of class nls, i.e. returning to your example:
out
R invokes the 'print.nls' method from the object 'out' and outputs:
out
Nonlinear regression model
model: y ~ x^beta
data: parent.frame()
beta
1.984
residual sum-of-squares: 10.78
Number of iterations to convergence: 6
Achieved convergence tolerance: 1.518e-07
From 'getAnywhere(print.nls)', we can see that beta is retrieved through:
print(x$m$getAllPars(), digits = digits, ...)
i.e. out$m$getAllPars() will contain/compute the value for beta.
One can see, that the value is NOT stored inside a variable, BUT is
retrieved by a function: 'getAllPars()'
In the case of 'nlm' objects, the methods/functions are stored in
'object'$m and this is the first object in the 'object'-list, i.e.:
out$m
will output the list of methods/functions (out[[1]] is identical).
CAVEAT
I really do NOT know if the methods are ALWAYS stored in 'object'[[1]]
or 'object'$m for the various objects. Objects might exist that do NOT
follow this rule.
You can get the methods applicable on an object with the following syntax:
methods(class = nls)
[1] anova.nls* coef.nls*confint.nls* deviance.nls*
[5] df.residual.nls* fitted.nls* formula.nls* logLik.nls*
[9] predict.nls* print.nls* profile.nls* residuals.nls*
[13] summary.nls* vcov.nls*weights.nls*
Methods marked with (*) are hidden methods.
[The class of an object can be identified using class('object_name'),
e.g. 'class(out)' ]
You see, 'nls' has both a 'print.nls' and a 'summary.nls' method, which
get invoked by the corresponding generic 'print' and 'summary' methods.
Unfortunately, as you see form 'getAnywhere(print.nls)', the print
method does NOT produce a list, therefore extracting the useful
parameter is a little bit awkward.
You see, summary() is far better as it exports a list. So, my suggestion
is to test IF the class has a method 'summary', and then use the s -
summary('our_object')' code to retrieve the information. You can surely
hard-code some parameters for a couple of functions, BUT as I think more
thoroughly, I see no easy solution to the more general approach.
I will try to solve this puzzle, but currently I do NOT have any better
idea.
Of course, iterating through 'object'[1:length('object')] is an
acceptable (and easy method) for most objects, BUT more complex objects
(that have functions and methods defined) pose a problem.
As already pointed out,
out[[1]][[12]]()
will work, too and it will output beta (please note, that '()' is used
at the end; also neither out[1][[12], nor out[[1]][12]() will work; you
need the '[[]][[]]()')
However, it is dangerous to iterate through functions, as some may need
additional arguments (and NOT simply '()' ), while other may change
something.
Sincerely,
Leonard
Wojciech Gryc wrote:
Hi Leonard,
My apologies for the silence with regards to your bugs. I will be
addressing them shortly, trying to figure out what's happening. I just
had one more question: if you use the built-in cell functions to call
R, does it also crash after the first attempt? Maybe it's just an
RDUMP issue.
I've been improving the GUI scripting interface I made for Calc and
it's been working very well. I'll be releasing yet another version
soon, but want to address some bugs and smaller features first. One of
the things I've been looking at is implementing some sort of
[sc-dev] Calc/R GUI Crash
Hi Wojciech, Wojciech Gryc wrote: Hi Leonard, Thanks very much for your thoughts. First, if you want to try the GUI package it's all up now on the wiki ( http://wiki.services.openoffice.org/wiki/R_and_Calc). ... 1. I began playing around with the package. While the RDUMP() is working, I had some trouble to run R cell-commands, although they do now mysteriously run. [NO idea what caused the trouble, as RTESTCONNECTION() returned #NAME!] OOo 2.2; W2k; R: sessionInfo() R version 2.5.0 (2007-04-23) i386-pc-mingw32 locale: LC_COLLATE=English_United States.1252;LC_CTYPE=English_United States.1252;LC_MONETARY=English_United States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252 attached base packages: [1] stats graphics grDevices utils datasets methods [7] base other attached packages: Rserve 0.4-7 2. I managed to crash OpenOffice trying to run the following command into RDump(): any R-command = new sheet is created = delete the new sheet = new R-command = CRASH [Issuing data.frame(a=1:5,b=5:1) the first time seems to crash OOo when called a 2nd time, even IF the temporary sheet was NOT deleted!] 3. I just wondered how RDump() handles a matrix, so I issued: cbind( c(1:5), c(5:1) ) Similarly, df-data.frame(a=1:5,b=5:1) returns a coerced list. Matrix and matrix-like objects (frames) seem to be represented as plain vectors. We will need to discuss a little bit about R-data types. I will try to write something during the weekend. (One can use is.matrix(...) and is.data.frame(...) to test for these data-types.) Nevertheless, nice to see how the code expands. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Request some assistance
jim wrote: 开源技术部-唐全发 wrote: Hello jim Thank for your suggestion! It's highly possible that the bug is introduced by AutoSpellcheck. The bug will disappear if AutoSpellcheck is disabled. If AutoSpellcheck is enabled, paste can work well if the string is just one word. But paste can eat out of memory when the string is not word. I'm looking forward to your suggestion. I dont know what you mean by autospellcheck. What is the menu item? I can also see this problem if I open a new spreadsheet, copy from the empty cell A1 and then select-all and paste. As stated below, this bug was reported already a long time ago and has a low priority because there is a simple work-around: dont't select-all and paste. There is not much more to be done, unless you have a patch - then attach it to the issue. I think it is more interesting what this bug reveals about the underlying design. It is strange that a value x is stored in every cell, when it should be sufficient to just store the fact that all cells have value=x. = This last paragraph brings me back to the following issue: http://www.openoffice.org/issues/show_bug.cgi?id=66817. Although that one deals not exactly with this bug, it touches on the inner representation of the spreadsheet (and I mentioned somewhere memory usage, too). Would be nice to see an improvement of the inner workings of Calc. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Request some assistance
Hello Tang, unfortunately I do not really know what causes the crash. However, what worries me is the usefulness of 'Select All' (CTRL+A) in spreadhseet applications (both OOo and Excel). LM - ALL developers ( user experience) What are your opinions on 'Select ALL'? I performed the following tests: A.) 'CTRL+A' = 1.) in MS Excel: selects A1:?P?37 (selection as drawn seems to extend beyond shown boundaries) 2.) in OOo Calc: selects A1:Z68 (selection seems NOT to extend beyond these boundaries) B.) paste = 1.) In MS Excel, this paste operation filled all cells in the range A1:IV65536 (in a matter of 10 s). 2.) OOo Calc = Crash *However, does ANY user need exactly the A1:IV65536 range*??? I have serious doubts that I will ever need EXACTLY this range. A more sensible approach, giving the user some control over the range selections, was presented in my previous post. I would welcome any comments on that proposal. Sincerely, Leonard 开源技术部-唐全发 wrote: Hello Leonard Mada Thank for your suggestion! It's highly possible that the bug is introduced by AutoSpellcheck. The bug will disappear if AutoSpellcheck is disabled. If AutoSpellcheck is enabled, paste can work well if the string is just one word. But paste can eat out of memory when the string is not word. I'm looking forward to your suggestion. Best Regards Tang Quanfa On Tue, 15 May 2007 23:59:03 +0300, Leonard Mada wrote I have added a comment to issue 59453. It goes like this: Crashing is definitely a BUG. However, what wonders me is that when I select everything (ctrl + A), Calc would select only the range A1:Y68 on an empty sheet, yet when I paste, it tries to paste well beyond this range. Actually, the range gets incremented over and over again. What is the limit? What should this maximum range be? I would opt for something like: 1.) either A1:Y68 as currently (or some other small value) 2.) in case of a non-empty cell: up to the last row/column that is non-empty 3.) open dialog box and ask user how many rows/columns should be filled I would strongly prefer options 2 and 3. Kind regards, Leonard Mada jim wrote: 开源技术部-唐全发 wrote: Hello Niklas: I'm Tang Quanfa, a developer in Beijing Redflag Ch2000 Software Co.,Ltd . I work in the SC team, my main work is fixing bugs. Recently，there is a new crash bug that we found in the OOo2.1 . The bug can be described as follows: 1. Open a new Calc document; 2. Select a cell,and input some English characters; 3. copy the selected cell. 4. at last,select all cells(Ctr+A),Paste(Ctrl+V); Result: Crash. I tried this on linux while running top to watch the memory used. All swap space was filled first, then all memory was used. When there is no memory left, OOo is terminated, but I do not think it crashed - possibly terminated by the operating system? See issues http://www.openoffice.org/issues/show_bug.cgi?id=59453 http://www.openoffice.org/issues/show_bug.cgi?id=39839 http://qa.openoffice.org/servlets/BrowseList?list=devby=threadfrom=1299769 To find these I searched the OOo website for example: calc memory copy paste jim If it is chinese characters,the program will be executed normally. I'm looking forward to your suggestion. Best Regards Tang Quanfa 2007-05-15 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- Open WebMail Project (http://openwebmail.org) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sc-dev] Easy moving of rows and columns in Calc
Hi, Thomas Benisch wrote: Currently in Calc neither rows nor columns can be easily moved manually. It is possible to drag drop e.g. a column into a new position, but the dropped column is not inserted, but overwrites the existing cells. ... 3.) ask user when dropping -- Another approach may be to ask the user, that means opening a dialog after releasing the mouse button. The user may choose between overwriting existing cells or simply inserting. cons: - No visual indicator for the exact drop position. - The dialog may be bothersome. Any ideas and comments are welcome. I usually disfavour approaches involving user action. However, IF the move overwrites existing data, I believe that at least a warning is warranted. [For people who do NOT like such warnings, an option may be implemented to disable this.] IF such a warning message is implemented, then a more advanced Messagebox seems the logical consequence: There should be a small message box (quite small, not very big), with 4 buttons (radiobuttons involve an additional click, so there should be really 4 buttons): 'Overwrite existing data.' 'Shift cells right.' 'Shift cells down.' 'Cancel' The 'Shift cells' action would insert additional cells and shift the old cells accordingly. Therefore, cell ranges are handled correctly with this approach. And overwriting existing data is avoided (which Calc currently does). Of course, there remains a problem, IF the move DOES NOT overwrite any data. I think that in this situation a simple move is warranted (without prompting any user action). Well, I will think more thoroughly about this feature in the next few days. Sincerely, Leonard - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
