On Sun, 08 Jan 2006 07:45:34 -0600, "James Plante" <[EMAIL PROTECTED]> said: > John Kane wrote: > > > > >>>> > >>>> > >>>As far as I am concerned it is clearly an error and potentially a very > >>>serious one but then I am the one who brought it up. I have reported it > >>>as Issue #: 58903 . It does not seem to be getting a lot of attention. > >>>This may be because Excell and Quatro Pro are just as guilty of this > >>>type of behaviour. > >>> > >>>Below is an excerpt from the Calc Help: (this is almost the same as the > >>>text in the OOoAuthors' Getting Started with Calc chapter. > >>>----------------------------------------------------------------------------------------------------------------------------- > >>>You can format numbers as text in OpenOffice.org Calc. Open the context > >>>menu of a cell or range of cells and choose Format Cells - Numbers, then > >>>select "Text" from the Category list. Any numbers subsequently entered > >>>into the formatted range are interpreted as text. The display of these > >>>"numbers" is left-justified, just as with other text. > >>> > >>>TIP When numbers are formatted as text, they cannot be used in > >>>calculations or formulas. > >>>operations will not work on it. It will either be ignored or will > >>>produce an error of some kind. > >>>---------------------------------------------------------------------------------------------------- > >>> > >>>Unfortunately this is not true. Some mathematical operations on a text > >>>or string value will return an incorrect number with no warning. As a > >>>simple example type cat into cell A1 and dog into B1 > >>>Let C1 = A1* B1. C1 = 0. Or if A1 is 5 and B1 is fud then if C1 = > >>>A1 + B1 then C1=5. > >>> > >>>Somehow I have a problem believing that cat + dog = 0. > >>> > >>>You can see my calculations at http://www.mytempdir.com/360489 > >>> > >>>----- > >>> John Kane > >>> Kingston ON Canada > >>> [EMAIL PROTECTED] > >>> (613)888-2399 > >>> > >>> > >>> > >>> > >>> > >>Cat + dog = 0 may not make much sense. But 3 + 2 + equivalent + missing > >>+ 6 = 11 makes perfect sense in some contexts. > >> > >> > > Assuming I have undertood you corrrectly, I agree. The difficulty is > > knowing that you are doing it. however it appears to me that we need a > > missing not a string variable that resets itsel to missing. You made me > > think of a simple test > >I tried A1='23 (text) , A2=5, A= 2 > > > >C1 = A1+A2+A3 = 7 > >C2=sum(A1:A3) = 7 > >Count(A1:A3)=2
----clip--- > Perhaps the correct solution lies in doing exactly what you're doing: > Warning users of the limitations of a tool. Any time a program is > written in C, Python, awk, etc., one usually proves the reliability of > the program by using several test data sets, the results of which are > already known. It may be the best solution currently available but I still think that it is a serious fault; not just a fault of Calc but of Excel and Quatro Pro the other two spread sheets I have checked. Come to think of it, the situation first came to my attention when I saw a posting about an Excel to Calc translation that was not working. Excel and Calc handle these operations differently but both may give mistakes in some circumstances. So an operation that may work in Excel will not in Calc or Quatro Pro. I have not worked out examples but it seems a fair bet that I could set up and test a Calc spread sheet and sent it to a colleauge with Excel and find it either does not work or gives wrong results. > > Anybody generating a spreadsheet template should also do this. For > example, I ran into a problem with rounding while building a spreadsheet > for some real estate appraisal analyses. Unless you tell the program to > use "precision as shown," dollar amounts in the hundreds of thousands of > dollars will be taken to full precision when calculating depreciation. > The small differences introduced by rounding can amount to several > hundred, or even a few thousand dollars when that small difference (it's > not an error) is compounded over several calculations. So to prove my > spreadsheet, I had to push aside the $4,000 computer and rely on the $60 > financial calculator. People round their calculations; computers don't, > unless they're told to do so. This is very true but as spread sheets get larger and one does 'maintanence' on them the potential for error increases. A single spread sheet has just over 4 billion cells (245 columns X 65536 rows X 256 individual sheets) and some people complain because they need bigger spreadsheets! Mind you I think this is often a case of using the wrong tool for the job; a sort of "if the only tool you have is a hammer, all problems look like a nail". I have seen people write résumés with a spreadsheet. > > So perhaps a tutorial or help document whose topic addresses the > limitations of spreadsheets in general would be in order. Looks like > you've got a good start. Good point. Thanks ----- John Kane Kingston ON Canada [EMAIL PROTECTED] (613)888-2399 -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html
