Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Formatting failure leads to calculation error|
|Status:||CLOSED NOT_AN_OOO_ISSUE||QA Contact:||issues@sc <issues>|
|Priority:||P3||CC:||issues, jleowski, rb.henschel|
|Version:||OOo 2.0 Beta||Keywords:||oooqa|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
|Issue Depends on:||26225|
Description andy999 2005-08-26 16:12:40 UTC
Attached spreadsheet has 3 cells; A2, B2, and C2. C2 subtracts A2 from B2. * Cell A2 is type Text, format @. * Cell B2 is type Numer, format General. * Cell C2 is also type Numer, format General. Important: While C2 shows the value of B2 -- properly -- as described in issue 27241, the problem can not be corrected using block formatting. Because of that, this is a formatting issue and not a calculation issue. Specifically... * If you highlight A2 through B2 and change the Format to Number, A2 will not change from Text and the calculation will fail. * If you highlight from B2 through A2 and change the Format to Number, A2 will not change from Text and the calculation will fail. * If you select A2 and change the Format to Number, C2 is correct. This is similar to but not the same defect as issue 27241. It is closely related to issue 5658. Attachment with example will be provided shortly.
Comment 1 andy999 2005-08-26 16:15:07 UTC
Created attachment 29085 [details] Block formatting and calculation example spreadsheet
Comment 2 jleowski 2005-09-04 01:57:17 UTC
Open Office Build 1.9.125 Complete Installation OS: Windows XP Pro SP2 Platform: PC We managed to successfully replicate this issue. Replication: 1) Format an empty cell (cell X) to Text 2) Input a number into cell X, for example the value 2 3) Input an equation a third cell (cell Z), similar to the following â€œ=X+3â€ If step 2 is done before step 1 then the operation works as expected meaning the value in cell Z is 5. Will attach a â€œTest case issue 53833.odsâ€ that demonstrates the replication steps. Problem: The result treats the text cell X as 0, so for our example the result should show 5, but it shows 3. Additional problem, possibly a separate issue: Furthermore when the cell Xâ€˜s format is changed from text to number the problem is not fixed. In the spreadsheet itself it shows as a number, but in the equation cell a single quote (â€˜) is present in front of the number. In out example cell X formatted back to number type would have a value of 2, but when clicked on and viewed in the equation editor it shows up as â€˜2. Why itâ€™s an issue: Similar problems have been dismissed in the past see issue 27241 and issue 27241. In general the problem shouldnâ€™t be encountered very often through normal use. When steps 1 through 3 is done in Excel the operation yield a proper output, meaning they perform the operation on the value regardless of input type. Hence, when moving from Microsoft based Excel sheets to Open Officeâ€™s, creates a problem in migrating files, since the Excel sheet no longer works in Open Officeâ€™s spread sheet (Calc) as it did originally in Microsoft Excel. Further Testing: Use a different math operation -, *, /, SUM etc. Use another cells as input instead of the value 3 we use. Import Excel documents into Calc that use text type cells to perform operations. Steve Wu and Jacek Leowski
Comment 4 jleowski 2005-09-04 02:10:12 UTC
One more thing in andy999's attachment changing C2 type to number doesn't correct the calculation failure. See: "Additional problem, possibly a separate issue" in my comment from Sat Sep 3 17:57:17 -0700 200
Comment 5 Regina Henschel 2005-09-04 12:56:50 UTC
The behaviour is not a bug and sure not priority P2. But it is a well known problem for those who exchange with Excel. If it is importend for you, vote on 5658. You can see whether digits are numbers or text, if you turn on View -> Value Highlighting. *** This issue has been marked as a duplicate of 5658 ***
Comment 6 andy999 2005-09-04 14:45:27 UTC
Regina, I have reopened this for review as I think you may have have misunderstood this specific issue and the severity of the problem. In summary; * Issue 5658 deals with importing an XLS file and calculating the results of a text cell. No formatting changes are made. * This issue (issue 53833) deals with an OpenDocument spreadsheet -- created from-scratch and not necessarily saved. Block formatting changes from text to numeric are ignored. Therefor, this issue is not a duplicate of issue 5658. The first note I posted and the follow on notes by Steve and Jacek document the steps required to reproduce this problem. To eliminate any potential confusion, here are the exact steps you can perform to see that this is not the same issue as issue 5658; * Open a new Calc spreadsheet. * Click on cell A2. * Change the formatting of A2 to "text, @". * Click on cell B2. * Verify format of B2 is "numeric, general". * Click on C2. * Enter the formula "=sum(a2:b2)". * Click on A2. * Enter 1 for the value. (NOTE: The value in the Input Line is just 1 not '1 or similar.) * Click on B2. * Enter 2 for the value. * Click on another cell. (This will commit the value of 2 in B2.) * C2 shows a calculated value of 2. * IMPORTANT: Select both A2 and B2 at the same time. * Bring up the format dialog. It shows "numeric, general". * Click OK to issue the change. * C2 shows a calculated value of 2. It should have been 3! * The format of A2 is still "text, @" not "numeric, general" as Calc was instructed to do. Note: Selecting column A and changing the formatting also has no impact on cell A2 being in "text, @" format. In summary: selecting a block and changing the format of the block from text to numeric does not change the cell from text to numeric -- the cell remains text. Please read through the steps required to reproduce this as there are specific conditions required to see this problem and they are easy to miss. This is a high or critical issue by definition; * It is a break in functionality. -- Changing formatting from text to numeric does not work over blocks.(!!!) * The defect can easily be missed by both novice and expert; it is not a cosmetic or medium problem. -- Block formatting is something that should reliable, and in this case it is ignored. On moderately complex sheets, it is not reasonable that a novice or even an expert would notice that the calculation is incorrect. * Data loss or a miscalculation is the result. -- Unlike issue 5658, this is a formatting problem but the results still are that the calculation performed is not what Calc is instructed to perform. As such, a higher priority (top or second from top) is appropriate. (I tend to use 4 severity levels -- ISO, IEEE, and IBM use similar definitions -- instead of the 5 used here, and under those this would be a critical aka 'showstopper' as data loss / miscalculation is involved and the workaround can not be expected to be performed by experts consistently let alone casual users.)
Comment 7 Regina Henschel 2005-09-04 18:26:52 UTC
Hi andy999, your are right, I missed your original request, because severeal issues are mixed here. I hope I get it now. It seems that the main problem to you is, that OOo doesn't change the format for some cells in a marked range. But OOo works here as designed. You don't like this behaviour, so your issue would be a request for enhancement. But you need no new issue, please look at issue 26225. I think it fits to your wish. Your comment in the attached document is wrong. The entry 222 will not change to a number, when A2 gets the format "number - standard", but it will get an apostroph in front and remains text (German 1.9.125 on WinXP). OOo was designed to work this way, so no bug. Issue 23348 handles the fitting request for enhancement. The third thing is, that OOo doesn't calculates with texts, but that is issue 27241 and 5658, as you already said. Altogether there is no real new issue here and you should close it therefore.
Comment 8 andy999 2005-09-04 19:13:17 UTC
Regina, as suggested I've voted for related issue 26225. While I disagree on this being a feature request, I'll leave it as is and allow you or others to close it. (I don't have access.) * Current behavior is not standard for any other application that has a block format option. * Current behavior can lead to data loss; it's a bug not a feature.
Comment 9 frank 2005-09-21 11:53:41 UTC
Hi andy999, sorry to say, but you're wrong. Changing a2 to format numbers doesn't make the text a number as the possibity exist that this 'number' is supposed to be a text. To make it a real number you have to remove the apostrophe in front of the number to make it a 'real' number. All other stuff is covered by the other Issues. Frank
Comment 10 frank 2005-09-21 11:54:08 UTC
Comment 11 andy999 2005-09-22 03:52:48 UTC
"sorry to say, but you're wrong. Changing a2 to format numbers doesn't make the text a number as the possibity exist that this 'number' is supposed to be a text. To make it a real number you have to remove the apostrophe in front of the number to make it a 'real' number." Your're right. You're right. A few things puzzle me, though. As you have thought out this so throughly, maybe you could fill in the blanks for me? 1. Why are numeric fields that are changed to text fields kept as numeric fields for calculations? 2. If a numeric cell contains a formula using a reference to a non-numeric cell, why isn't an error message generated?
Comment 12 frank 2005-09-22 07:06:59 UTC
Hi, to ask your questions it wasn't necessary to re-open this Issue. 1.) It's the same as for text cells converted to numbers. The first used cell format rules as long as the cell isn't Edited in the way that you've entered it by either doubleclicking it or using F2 and change something and close the cell. Entering new content to the cell will change it immediately. 2.) Because text cells have the value zero. Frank
Comment 13 frank 2005-09-22 07:07:20 UTC
Comment 14 andy999 2005-09-22 12:45:42 UTC
>>1. Why are numeric fields that are changed to text fields kept as numeric fields for calculations? >"1.) It's the same as for text cells converted to numbers. The first used cell format rules as long as the cell isn't Edited in the way that you've entered it by either doubleclicking it or using F2 and change something and close the cell. Entering new content to the cell will change it immediately. So, the true value of the cell is invisible to the user? >>Q. 2. If a numeric cell contains a formula using a reference to a non-numeric cell, why isn't an error message generated? >"2.) Because text cells have the value zero." The cells don't contain zero. They contain the value entered, and if that's anything but null it's something, numeric or not. (Note, I'm not talking about any invisible-to-the-user values, but what they can see.) [[Side note: I've just this week had to explain to my boss and a co-worker that the highlighting in an OOo text file when saved as a Word .doc can't be removed with the highlighting tool in Word since Word has 2 levels of highlighting and the highlighting tool in Word doesn't act on the same level OOo saved (OOo has 1 level of highlighting). ((Checked the DB here, and just like this report, the highlighting issue isn't technically a defect in OOo. In that case it's a defect in MSO...yet, it's still causing me problems in getting OOo adopted (see below). If a calculation doesn't match what Word says (or can't be proven to be more accurate as Gnumeric is), there's no way I can explain it to the boss.)) This has made me consider putting together a seperate 'OOo to MSO coverter' just so I don't have to get routinely embarrased even when does things correctly, though that would probably require me to create a macro for Word or Excel to clean up the differences, and nobody is going to install and run that macro so I'll have to do it before exchanging any documents. An 'insert MS quirks mode (incompatable)' for saving a file would be a good idea in these situations. ((A search of the DB here came up with no hits on this issue, though I might have used the wrong search paramaters.)) The usual comment after I explain the situation is 'Well, we just can't rely on that [OpenOffice.org] -- use MSO' or 'why do you insist on using OpenOffice?'. Beyond the embarasements, this is a big problem since I can't add OOo/OpenDocument to requirements documents on the contracts I'm on once people encounter a few of these these differences no matter if the implementation is technically correct or not.]]