To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=109524 Issue #|109524 Summary|Cells in Calc formatted as Boolean (True) are treated |as integer when "fill down" is used Component|Spreadsheet Version|OOo 3.1.1 Platform|PC URL| OS/Version|All Status|NEW Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|editing Assigned to|spreadsheet Reported by|atdsm
------- Additional comments from at...@openoffice.org Mon Feb 22 22:12:21 +0000 2010 ------- Sorry if the summary is confusing; it is hard to distill. Here is the issue in more detail. When cells in Calc are formatted as Boolean and explicitly given a value of TRUE, they display "TRUE" in both the cell itself and the formula bar. When this type of cell is copied down via the corner box (the "Fill Down" feature), each cell that is copied displays "TRUE" in both the formula bar and the cell itself. However, the cell values themselves are treated as being non-boolean numbers and the values are propagated down as "1", "2", "3", ... etc. In other words, when Boolean cells are filled down, they are filled as integer values instead of Boolean values. This gives unexpected results when using formulas such as "SUMPRODUCT" to control a summation with a true/false value. EXPECTED BEHAVIOR: 1) Boolean cells which are filled down should not obtain values other than 0 or 1. 2) A Boolean formatted cell should contain only a boolean value, that is, treated only as a 0 or a 1 for numerical calculations. ACTUAL BEHAVIOR: 1) A Boolean formatted cell which is filled down via a corner drag populates down as increasing integer values: 1, 2, 3, etc, even though the cell still says and contains only "TRUE". (This is a new issue, documented here.) 2) When numerical operations are performed on Boolean formatted cells with values other than 0 or 1 (eg 2), the number is not treated as a boolean but rather as a number. (This, I believe, may be related to Issue 58572.) RESULTING INCONSISTENT BEHAVIOR: Depending on the number "behind" a given Boolean formatted cell, a numerical operation will therefore give different results. EG, a result can occur like line 3 below: 1 * TRUE = 1 (Expected) 1 * FALSE = 0 (Expected) 1 * TRUE = 23 (Unexpected) STEPS TO REPRODUCE: 1) Open a new Calc document 2) Format cell A1 as "Boolean Value" 3) Enter "True" in cell A1 4) Using the box in the cell corner, fill down from cell A1 to cell A10 Result: A1:A10 all display "TRUE" 5) Format cells A1:A10 as "number / 0" Result: A1:A10 will contain "1,2,3,4,5,6,7,8,9,10" Expected: A1:A10 should contain "1,1,1,1,1,1,1,1,1,1" ...or (continuing from 4 above)... 5) In column B, enter "1" in cells B1:B10 6) In cell C1, enter formula: "=SUMPRODUCT(A1:A10;B1:B10)" Result: Cell C1 contains 55 Expected: Cell C1 should contain 10 (the product of 1*TRUE summed 10 times) SIGNIFICANCE: This issue might be viewed as two issues: (1) the way that Boolean values are propagated down and (2) the way Boolean values are treated for numerical calculations. (1) is a new issue, documented here; (2) has been documented elsewhere (confer Issue 84226 and Issue 58572). However, it is the combination of these two issues which in this case creates a significant problem for a user: the creation of non-1 Boolean "TRUE" values unexpected by the user and is transparent to the user, making the error hard to catch and causing significant issues when array functions such as SUMPRODUCT are 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 --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org For additional commands, e-mail: issues-h...@sc.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org