On Wed, 07 Dec 2005 13:28:23 -0500, [EMAIL PROTECTED] said: > I've been reading this thread with interest, but until now, I didn't go > into Calc myself to give myself a first-hand understanding of the > situation. > > Now that I've done so, I'm not convinced this is a major problem. -----------clip--------
> If I have text (including a number formatted as text) in A1, and the > number 5 in B1, then the sum of these cells will be 5, the product of > these cells will be zero, and the average of these cells will be 5. The > exact same answers will occur if cell A1 is empty. You are correct about the average() and the sum() functions. They behave as we would expect. I was mistaken. However I am still horrified that I can add or multiply two cells and get impossible results. Let A1 = 5, & B1 = FOO, C1 = Harry then A1 + B1 = 5 and B1-A1 = -5 and B1+C1= 0 However it is nonsense under normal mathematical rules to say that 5 + FOO =5 or that FOO-5 = -5 much less that FOO + Harry = 0. In fact if you enter 5* FOO into a spreadsheet cell you get 5*FOO just as you would expect. But A1* B1,which should be the same thing equals 5. Quite honestly I think this is totally unacceptable. > > As far as my meagre reasoning goes, that's how I would like it. If I > have empty cells or text in the range of data I'm doing a sum of, or > doing statistics on, for example, I would want these cells to be > ignored. I wouldn't particularly want an error message. Agreed. What you might not want to do is multiply a number by a text variable and then do the statistics on the result. You might not be to happy to find out that rather than multiplying blood pressure by a person's age you have been multiplying by their apartment number or case id number both of which you had carefully formatted as text so such mistake could not happen. > I cannot find a situation were Calc tries to convert a number entered > as text, back to that number. This is a good point. Excel does but thankfully Calc does not. > > Now, there WOULD be issues when things like phone numbers and social > security numbers are entered as numerical data, which I'm guessing > would be the most common way of entering them. Then someone, if not > careful, could wind up doing meaningless computations like the sum of > phone numbers of the standard deviation of social security numbers. This is a constant worry and a major reason why one formats such things as text variables. Conceptually one cannot do arithmetic on text. Therefore if I know that I have formatted a set of numbers as text I should not be able to get a usable answer when I do multiply them. Statistical packages that I have used do this routinely. It is an excellent error detection device. I learned this after I accidentally ran a correlation on the subjects' ID numbers. Calc, and Excel & Quatro Pro, all do math on text variables. The careful or knowledgeable user knows that this cannot happen so he or she does not look for it and has no good way to detect such an error. > But > I don't know any real way around that. If someone is so sloppy with > what their ranges are for computations, there are all kinds of other > errors that could be made. Probably a much more serious error, because > it would be tougher to detect from the answer, would be doing a > computation on numbers that make sense but which are, for example, one > column removed from the intended target. This is quite true. However, say we multiply 10 telephone numbers by a person's height. We get a sum, a mean, a standard deviaton etc. It may be almost as hard detect as the example above. Actually in a small spreadsheet it is probably something that we would detect in most cases fairly easily. The problem arises with large or complex spreadsheets One may quite easily multiply by a telephone number or a social insurance number or any other text formatted number. If one has perhaps perhaps 50,000 or 100,000 rows of data and operations it may not be at all apparent that this has happened. It may not always be the initial calculation that is the problem but rather the results that we can get once that first calculation is fed into another calculation. Another possible problem is for an IF condition where we have a number of possibilities. We could be intermittently multiplying or adding a text variable. Egregiously large deviations are likely to show up but smaller ones might persist practically forever. This is what worries me and, I believe, worries Spencer Harpe. Spreadsheets are used by engineers, scientists, financial and policy analysts among others. I have heard of spread sheets with hundreds of pages. In fact I believe a not uncommon complaint is that Calc and Excel spreadsheets are too small! I believe a Calc spreadsheet only can have 65,536 rows and 245 columns in a sheet and only 256 sheets for a spreadsheet. This only gives us about 4 billion cells ( 4,110,417,920, I believe). Frankly, the thought that someone could add FOO + 5 and get 5 , or possibly worse, Subtract FOO-5, somewhere in that large a spreadsheet scares me. > > So unless I'm totally missing something, I don't see this as a problem. > I do agree it should be documented, of course. > > Something on this general topic that I DO think may be a problem (or at > least it's an annoyance) is that if I have a numerical entry, and THEN > try to format it to text, it refuses to be re-formatted, and it remains > a number. In this case, if someone isn't alert, he or she could THINK > that they've formatted these numbers as text, when in fact they are > still numerical data. This is certainly another problem, possibly even more serious than the ones that I raised, since tell-tale results that one might get from multiplying by FOO (e.g. a standard deviation of 0 or a flat line graph) would make error detection even more difficult. The only good thing I can say about it is that at least it is documented in Help. I have filed an issue on this (Issue 58903) and I would be happy to add this matter to the issue though it might be better to file it as a separate one . Do Ooo Authors have an opinion on this? ----- John Kane Kingston ON Canada [EMAIL PROTECTED] (613)888-2399 -- http://www.fastmail.fm - A fast, anti-spam email service.
