Issue 109524 - Cells in Calc formatted as Boolean (True) are treated as integer when "fill down" is used
Summary: Cells in Calc formatted as Boolean (True) are treated as integer when "fill d...
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 3.1.1
Hardware: PC All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-02-22 22:12 UTC by atdsm
Modified: 2013-01-29 21:39 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description atdsm 2010-02-22 22:12:21 UTC
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.