On Thu, Aug 15, 2013 at 9:47 AM, Rob Weir <robw...@apache.org> wrote: > On Thu, Aug 15, 2013 at 9:41 AM, janI <j...@apache.org> wrote: >> On Aug 15, 2013 3:06 PM, "Rob Weir" <robw...@apache.org> wrote: >>> >>> https://issues.apache.org/ooo/show_bug.cgi?id=122927 >>> >>> It boils down to how an IF() statements are evaluated. >>> >>> Remember, the typical form is IF(Condition;X;Y) where you give a >>> return value for the case where Condition is TRUE and another value >>> when Condition is FALSE. >>> >>> But it is also possible to leave out the last parameter and have a >>> formula like this: >>> >>> IF(Condition;X) >>> >>> So what does the formula evaluate to if Condition is FALSE? >>> >>> The behavior in 4.0.0, returning FALSE, is correct according to the >>> ODF 1.2 specification and is the same as what Excel does. However, it >>> is different than what earlier versions of OpenOffice did, namely >>> returning 0.0. >>> >>> We obviously cannot do both. I think the AOO 4.0.0 behavior is >>> correct and should remain. >> >> I dont understand why we cannot do both, most programming languages >> interpret falase==0 and true==1, that allows the use of boolean functions >> in calculations. >> > > If the user takes the results of the IF() calculation and uses it in > another formula, then FALSE is automatically treated as 0 in any other > formula where a number is expected. You are correct in your > assumption there. So no one gets a wrong answer in a calculation > because of the change. > > What is different is what appears in the cell that actually has the > IF() statement in it. AOO 4.0 and Excel show FALSE. Earlier versions > of AOO showed 0. In this sense we can have one default behavior or > the other, but not both. >
I should mention one other subtlety. Cells have values, types and formats. Types are really numeric and string. Things like currency, date and boolean are just formats. You can type in a number like 123456 and format it as a number, a date, a boolean, etc. The tricky part is that some formulas automatically set a format, without requiring user intervention. So if I type =TODAY() into a a cell I see "08/15/13" instead of "41501". The spreadsheet is smart enough to know that with that formula the cell should be formatted as date. Pretty cool, yes? Similar thing occurs with booleans. Type FALSE() into a cell. It automatically is formatted as a boolean, not a number. So with the IF(Condition;TrueValue) case, the ODF spec says that FALSE() is returned if Condition is false. So that is essentially why the user sees FALSE in the end. The spreadsheet determines the appropriate format based on the return value being boolean. There are ways for the user to override this. For example, if they use the full form, IF(Condition;TrueValue;FalseValue) then they can simply set FalseValue to be equal to 0. That will ensure that they get the numeric return value. Or, they can wrap the IF() to force the outcome to be numeric, e.g., =N(IF(Condition;TrueValue)). The N() function will explicitly convert the boolean return value into a number. Regards, -Rob > -Rob > > >> rgds >> jan i >>> >>> I'd like to close the issue as NOTABUG. But I'd like to get a few >>> more thoughts on this first. >>> >>> Regards, >>> >>> -Rob >>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org >>> For additional commands, e-mail: dev-h...@openoffice.apache.org >>> --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org For additional commands, e-mail: dev-h...@openoffice.apache.org
