Issue 114430

Summary: OOo Calc: 0^0=1 !!! Zero power zero equal one!!!
Product: Calc Reporter: lebendre <lebendre>
Component: editingAssignee: AOO issues mailing list <issues>
Status: REOPENED --- QA Contact: Rob Weir <robweir>
Severity: Trivial    
Priority: P3 CC: hdu, issues, orcmid, pescetti
Version: OOO320m9Keywords: vetoed
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: 3.4.1
Developer Difficulty: ---
Attachments:
Description Flags
Proof of concept patch
none
Try to make it compile + cleanup.
none
Proof of concept - another iteration
none
Test file
none
Additional tests
none
A simpler patch.
none
Proof of concept patch
none
Final patch none

Description lebendre 2010-09-09 21:09:29 UTC
I found a big bug in OOo Calc. The program does not calculate correctly: it's
well known that 0^0 doesn't exist, but OOo says that 0^0=1!!!

To see the but, just put =0^0 in a cell.

I've read this discuss, but since 2006 the bug is not fixed!
http://www.openoffice.org/servlets/ReadMsg?list=discuss&msgNo=56476
Comment 1 Andrea Pescetti 2010-09-10 07:57:10 UTC
OpenOffice.org implements OpenFormula. You should raise the issue there:
http://lists.oasis-open.org/archives/tc-announce/201007/msg00004.html

The latest OpenFormula draft
http://docs.oasis-open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part2.odt
does the following:
- in 6.4.6, it defines a^b as POWER(a,b) with the provision that a and b
shouldn't both be zero.
- but in 6.16.46, it allows both a and b to be zero, stating that 
POWER(0,0) is implementation-defined, but shall be one of 0,1, or an Error.

So OOo respects the standard here, even though the result is clearly
mathematically wrong. The way to initiate a change in the standard is described
in the links above.
Comment 2 Risto Jääskeläinen 2010-09-10 13:27:43 UTC
I can confirm this error at OOO330m7.

OpenFormula draft says:

6.4.6 Infix Operator "^"
Summary: Exponentiation (Power).
Syntax: Number Left ^ Number Right
Returns: Number
Constraints: NOT(AND(Left=0; Right=0)); Evaluators may evaluate expressions
where OR(Left != 0; Right != 0) evaluates to a non-Error value.

So this may be a defect compared to OpenFormala definions but more important
this is a big error compared to mathematics.  There is no reason leave this
defect as it is now even if there may be mathematically loose definitions for
the Power function in OpenFormula definitions. Still it allow implement the
Power funtion too to give error message for Power(0;0). 

So only solution which is correct both mathematically and by OpenFormula
definitions is change this ^ operator and the power function so that they
product error message for 0 ^ 0.  There is not even compatibility reason for
this defect because for example gnumeric gives #Numero error in this situation.
Comment 3 lebendre 2010-09-10 17:58:12 UTC
For 0^0, you can read this text in wikipedia:
http://en.wikipedia.org/wiki/Exponentiation#Zero_to_the_zero_power
Comment 4 Rob Weir 2013-02-05 00:52:02 UTC
The ODF 1.2 standard says 0^0 shall be 0, 1 or an Error.  The choice is implementation-defined.

Since the the limit of x^x as x approaches 0 is 1, the choice used in Calc is reasonable for most spreadsheet uses.
Comment 5 Pedro Giffuni 2013-02-05 18:22:16 UTC
This is highly controversial: long ago, I contacted my HP calculator support about it and they gave an answer in Wikipedia lines.

The limit of x^x when x -> 0 is indeterminate.
The operation is no doubt illegal and the speadsheet should at least warn about it.

We can give an error and still comply with OpenFormula.

I will give such patch a try ... keep tuned.
Comment 6 orcmid 2013-02-05 19:07:30 UTC
Technically, the proper response for calculation of 0^0 is NaN.  I suspect there is a setting somewhere in the FPU to make it 1 though.  I remember arguing with Don Knuth about this once (since he found 1 nicer for certain things).  

I once published (in Dr. Dobbs) a Pascal implementation of x^n for integer n.  I solved the problem for 0^0 by returning 0/0, which is expected to be mathematically equivalent if neither is NaN.  If the floating point implementation determined that to be NaN, good.  If it was set up to return something else, I had managed to side-step imposing my own notion of the correct answer.

This doesn't solve the problem for Calc of course.  OpenFormula specifies (in 6.4.6) that 0^0 is not permitted.  But it also says a^b is implemented by POWER(a,b).  In 6.16.46 POWER(0,0) is implementation-defined but must be one of 0, 1, or an Error. 

The smart thing is to do whatever Excel does in this case.  I just got #NUM! for =POWER(0,0) using Excel 2013 Preview.  The thing about #NUM! is that it can't be overlooked, whereas a result of 0 or 1 can end up being used in further calculations without awareness of what is happening.  Someone who doesn't like #NUM! (or the Calc equivalent) can add logic to produce the result they prefer instead if 0^0 is possible in their particular spreadsheet.

Since Pedro is going to look into this, I am changing the status of this issue to REOPENED.
Comment 7 Pedro Giffuni 2013-02-05 20:18:55 UTC
This is actually a good chance to optimize yet another operation in AOO.

We are currently using the system pow() version which relies on the same 0^0 behaviour but there are interesting implementations elsewhere:

http://martin.ankerl.com/2012/01/25/optimized-approximative-pow-in-c-and-cpp/

This is going to be fun.
Comment 8 Pedro Giffuni 2013-02-07 18:02:59 UTC
Created attachment 80229 [details]
Proof of concept patch

I did a small wrapper around the pow() function.
This is currently untested.
Comment 9 Rob Weir 2013-02-07 19:06:33 UTC
Pedro, if no one actually tests this the patch is not going anywhere.  It just joins the other 500 unintegrated patches, lost in the database.

So I'll at least mark this as an "easy" fix in case a new developer feels like doing this.
Comment 10 Pedro Giffuni 2013-02-07 19:16:51 UTC
(In reply to comment #9)
> Pedro, if no one actually tests this the patch is not going anywhere.  It
> just joins the other 500 unintegrated patches, lost in the database.
> 
> So I'll at least mark this as an "easy" fix in case a new developer feels
> like doing this.

I own the issue and I will test it properly: I just thought that given
that it is a controversial change I would let it up for review before
committing.

Of course other testers are welcome.
Comment 11 Andrea Pescetti 2013-02-07 20:51:48 UTC
Setting as enhancement since, as multiple comments show including mine from 2010, this turns an ODF-compliant behavior into another ODF-compliant behavior, with a possible MS interoperability gain (or should it be marked as PATCH now? the online documentation is not clear).
Comment 12 Rob Weir 2013-02-07 21:03:23 UTC
OK.  Watch out.  There are a lot of edge cases here, including raising negative numbers to non-integral powers.   It is very easy to break existing spreadsheets in return for imperceptible performance increases.

Of course, the good side of this is we'll have a nice test spreadsheet we can give to the QA team when this is done!
Comment 13 Pedro Giffuni 2013-02-07 21:36:11 UTC
(In reply to comment #12)
> OK.  Watch out.  There are a lot of edge cases here, including raising
> negative numbers to non-integral powers.   It is very easy to break existing
> spreadsheets in return for imperceptible performance increases.
> 

Please note that I implemented this in a way that only the POWER() function is affected. Uses of the libc/C99 pow() function in other formulas is unaffected.

What I did was actually add a powr() function to SAL. powr() is described in IEEE 754-2008 but has not been implemented in any libc, that I know of.

> Of course, the good side of this is we'll have a nice test spreadsheet we
> can give to the QA team when this is done!
Comment 14 Pedro Giffuni 2013-02-08 02:14:38 UTC
Created attachment 80231 [details]
Try to make it compile + cleanup.
Comment 15 Pedro Giffuni 2013-02-08 03:21:05 UTC
Created attachment 80232 [details]
Proof of concept - another iteration

Yet another try.
Not guaranteed to compile yet: I keep making dumb mistakes.
Sorry :(.
Comment 16 Pedro Giffuni 2013-02-08 15:25:54 UTC
Created attachment 80236 [details]
Test file

The test seems to work well, However I am noticing unrelated issues.

0 ^ -2 = indeterminate
1 ^ 10 = 1
Comment 17 Pedro Giffuni 2013-02-08 15:38:39 UTC
Bah ...

(In reply to comment #16)
> Created attachment 80236 [details]
> Test file
> 
> The test seems to work well, However I am noticing unrelated issues.
> 
> 0 ^ -2 = indeterminate

This is due to the formula used by pow(). We are bug compatible with Excel here.

> 1 ^ 10 = 1

Which is, of course, correct.

Patch will be committed with some minor cosmetic fixes.
Comment 18 SVN Robot 2013-02-08 16:31:55 UTC
"pfg" committed SVN revision 1444110 into trunk:
i114430 - AOO Calc: 0^0=1 and it should be indeterminate.
Comment 19 Pedro Giffuni 2013-02-08 16:46:19 UTC
I check again the IEEE 754 - 2008 documentation and the result of

> 0 ^ -2 = indeterminate

follows the standard, so I won't change that.
Comment 20 orcmid 2013-02-08 21:08:33 UTC
(In reply to comment #19)
> I check again the IEEE 754 - 2008 documentation and the result of
> 
> > 0 ^ -2 = indeterminate
> 
> follows the standard, so I won't change that.

That's because mathematically, 0^-2 = 1/(0^2) = 1/0.  "indeterminate" is fine.

The tricky case is (-3)^1.5 and any situation where the POWER(a,b) algorithm determines that exp(log(a)*b) is the calculation to use and it happens that a <= 0.  OpenFormula leaves the case where a <=0 and b is not an integer to be implementation-defined.  

I notice Excel 2013 (Preview) produces #NUM! for POWER(-3,1.5).  There should certainly be an error for Calc, whatever its particular code is.
Comment 21 Pedro Giffuni 2013-02-08 21:43:36 UTC
(In reply to comment #20)
> (In reply to comment #19)
> > I check again the IEEE 754 - 2008 documentation and the result of
> > 
> > > 0 ^ -2 = indeterminate
> > 
> > follows the standard, so I won't change that.
> 
> That's because mathematically, 0^-2 = 1/(0^2) = 1/0.  "indeterminate" is
> fine.
> 

Yeah! .. I am forgetting my basic math :(.

> The tricky case is (-3)^1.5 and any situation where the POWER(a,b) algorithm
> determines that exp(log(a)*b) is the calculation to use and it happens that
> a <= 0.  OpenFormula leaves the case where a <=0 and b is not an integer to
> be implementation-defined.  
> 
> I notice Excel 2013 (Preview) produces #NUM! for POWER(-3,1.5).  There
> should certainly be an error for Calc, whatever its particular code is.

Nice catch!

According to the standard we *should* return NaN (invalid operation).

In my system it returns -3, but it is a system dependent bug:
the wrapper uses the systems libc pow() and we are assuming C99
- IEEE 754 compliance.

I will add a check to fix it.
Comment 22 Rob Weir 2013-02-09 03:34:25 UTC
Let's make sure these changes get into the release notes since this patch changes the calculations and results of existing OpenOffice spreadsheets.  Users deserve to be warned that this may occur.
Comment 23 orcmid 2013-02-09 05:06:41 UTC
(In reply to comment #22)
> Let's make sure these changes get into the release notes since this patch
> changes the calculations and results of existing OpenOffice spreadsheets. 
> Users deserve to be warned that this may occur.

+1.  Fortunately, any changes in results are to ones that announce themselves rather than silently change the results of dependent calculations.
Comment 24 Rob Weir 2013-02-09 05:20:55 UTC
Actually, there are very natural idioms in formula usage that will lead to silent errors due to this change. For example the =if(iserror()) pattern.
Comment 25 orcmid 2013-02-09 06:32:15 UTC
(In reply to comment #24)
> Actually, there are very natural idioms in formula usage that will lead to
> silent errors due to this change. For example the =if(iserror()) pattern.

Well, I wouldn't call that an idiom exactly if it means "I don't want to know about errors," like a try {whatever} catch(all) {nothing}.

I agree about the release notes.  

For most folks, if the deviation is encountered, it is probably something that needs to be known about.  Someone who has intentional if(iserror() ...) functions is likely going to know how to review and check those.
Comment 26 Rob Weir 2013-02-09 13:11:39 UTC
(In reply to comment #25)
> (In reply to comment #24)

> I agree about the release notes.  
> 
> For most folks, if the deviation is encountered, it is probably something
> that needs to be known about.  Someone who has intentional if(iserror() ...)
> functions is likely going to know how to review and check those.

You are making wide-sweeping and unreasonable assumptions here.  Spreadsheets are not only used by their authors.  Spreadsheets are distributed for others to use, embedded into applications, etc.  So you cannot assume that changes like this are trivial to react to.

I' seriously considering vetoing this change because it breaks backwards compatibility and does not come with a test case to prove that it even confirms to ODF 1.2.
Comment 27 Andrea Pescetti 2013-02-09 16:36:49 UTC
Pedro, I suggest that you bring this to the dev list. For an informed judgement we will need to examine:
- ODF Formula conformance
- Interoperability (especially with Microsoft Office)
- Backwards compatibility
- A proposed formulation for a sentence to put in the Release Notes for 4.0
Comment 28 Pedro Giffuni 2013-02-09 17:28:54 UTC
Hello;

(In reply to comment #27)
> Pedro, I suggest that you bring this to the dev list. For an informed
> judgement we will need to examine:

> - ODF Formula conformance
> - Interoperability (especially with Microsoft Office)

The change complies with both. I was even careful to mention IEEE 754 - 2008.

> - Backwards compatibility

4.0 is the best time for doing this change.

> - A proposed formulation for a sentence to put in the Release Notes for 4.0

When there is *somewhere* to add such note I will gladly do so.

Now ...

I am not spending my time on this, in fact I already unsubscribed from the -dev list to rest from confrontations with Rob.

I think before reverting this (which *I* won't do):

- A qualified mathematitian will have to explain why -0^-0 should be interpreted as 1 instead of NaN.

- *specific* examples of functionality broken by this change must be provided.
Comment 29 orcmid 2013-02-09 19:35:47 UTC
(In reply to comment #27)
> Pedro, I suggest that you bring this to the dev list. For an informed
> judgement we will need to examine:
> - ODF Formula conformance
> - Interoperability (especially with Microsoft Office)
> - Backwards compatibility
> - A proposed formulation for a sentence to put in the Release Notes for 4.0

I can attest, as others already have, that the OpenFormula specification permits constrained implementation-defined values for POWER(0,0).  The current implementation is admissible, although the definition of operation 0^0 is worded in a more-restrictive manner.  Producing an Error value (e.g., #VALUE!) for POWER(0,0) is already permitted.

Producing an Error value is what Microsoft Office Excel already does.  I confirmed that with Excel 2013 (Preview) and I reported that here also.  I am assuming that creating interoperability problems in this case is not in our best interests.

It appears to me that the OpenFormula provision of "1" as an allowed implementation-defined result was a concession to some existing implementation.  

It is necessary for there to be an explicit definition of whatever the choice becomes in Calc documentation though.  It is a conformance requirement.  (I am somewhat astonished that the Chair of the ODF TC deflects this issue pending reconciliation with ODF 1.2 when this could be checked and confirmed by that expert directly.)

There is no way to have both backward compatibility and interoperability.  Someone concerned about the variability permitted by OpenFormula would be urged to construct formulas in a manner that these cases are successfully avoided or that an implementation-independent result is forced.  That is something that would be helpful to provide in Calc documentation also.  That is required beyond a release note on any change that is made.

Moving forward, I think it would be a good idea to demonstrate exactly how the patched POWER(a,b) works for all of the cases covered in OpenFormula.  I confirmed that the OpenOffice-lineage implementations and Excel already produce error values when a <=0 and b is not an integer, so there is no interoperability issue there. 

I am reopening this issue.  I think it is fine to point out that this issue on both users@ OO.a.o and dev@ OO.a.o although I am not clear what question one wants to put before those lists.
Comment 30 orcmid 2013-02-09 21:08:53 UTC
(In reply to comment #27)
[ ... ]
> - A proposed formulation for a sentence to put in the Release Notes for 4.0

Rob has created a breaking changes topic on the AOO 4.0 Release Notes at https://cwiki.apache.org/confluence/display/OOOUSERS/AOO+4.0+Release+Notes

I have added the proposed POWER(0,0) = #VALUE! change under that topic (far down the page).

Here is the mathematical explanation.  It is different than the Computer Scientist explanation.

 1. Having a defined value.  There are those who argue that POWER(0,0) should have a defined value because computer algorithms need those [although this is no longer true for common floating-point arithmetic, where there are defined undefined values such as NaN, +Infinity, -Infinity, etc].  The quasi-mathematical argument for 1 involves reasoning about limits of ratios (although there is no way for POWER(a,b) to determine that there are ratios and any convergent series involved.  There is another argument based on the binomial theorem (one that Donald Knuth has told me he finds persuasive) although it is clear that Newton simply didn't consider the (a+b)^n case for a+b = 0 and n=0, it probably not being of any practical concern.  But having x^0 always be one does simplify certain inductive/recursive definitions.  I find the damage done to the axioms of arithmetic too perplexing.

 2. Having an undefined value (i.e., making it an error, such as #VALUE!.  There is no question that 0^0 is not defined in the standard model for real arithmetic.  That is because division by 0 is already undefined in the standard model of arithmetic.  These days computer arithmetics often have a distinct error value for divisions by 0, such as +infinity, but there is no pretense that this is an actual numeric result -- doing so requires a serious non-standard arithmetic.  Some also recognize that 0/0 is different (since it is difficult to argue that the result is best expressed as an infinity), and the result NaN (Not a Number) is appropriate for this case.

Why is that?  Well, if one is going to give 0^0 a definite value, then it is expected that one preserves the law of exponents.  If that is the case, it will follow that 0^0 = 0^(n-n) where n is not 0.  But 0^(n-n) = 0^n/0^n = 0/0 and there we are.

As I mentioned in an earlier comment, I once (Febuary 1986) published a PowerN(a,i) algorithm in Dr. Dobbs where I finessed this case by actually returning PowerN(0.0,0) as 0.0/0.0.  I left it to the implementation of computer arithmetic to sort it out.  (This was earlier than there are digital archives of Dr.Dobbs articles.  I must scan this in someday.  Fortunately, I retained the copyright.)
Comment 31 Rob Weir 2013-02-15 13:12:42 UTC
Created attachment 80289 [details]
Additional tests

Here are some more test cases, with a column indicating the values currently returned in AOO 3.4.1.
Comment 32 Pedro Giffuni 2013-02-15 14:38:48 UTC
Created attachment 80290 [details]
A simpler patch.

FP math in SAL is somewhat tricky and the additional conditions set by the standard IEEE 754 powr spec seem to be causing unwanted side effects.

The attached patch simplifies the check to only do something in the 0⁰0 case.
This still requires testing.
Comment 33 Pedro Giffuni 2013-02-15 14:46:17 UTC
A lost all interest in this issue given that:

1) there is no consensus and
2) people will go to extremes to get this patch reverted.

The issue *is* valid though so I will leave it for some the community
at large to decide if it shall be classified as "Resolved - wont fix".
Comment 34 Pedro Giffuni 2013-02-17 03:06:06 UTC
Created attachment 80292 [details]
Proof of concept patch

Just for curiosity sake ...

I think the original patch was conceptually fine. The code for ScPow though,
is not too clean and routinely raises values to the power of zero!

The base implementation in sc probably requires some redesigning to be more
portable and it would not be suprising to find bugs.
Comment 35 Pedro Giffuni 2013-02-17 18:53:54 UTC
Created attachment 80294 [details]
Final patch

This solves all the issues.

It doesn't go into cleaning the code in Sc though, that will likely require an orthogonal effort.
Comment 36 Rob Weir 2013-02-17 20:20:39 UTC
Added "vetoed" keyword, to make it easier to track that status.
Comment 37 Andrea Pescetti 2013-02-17 21:44:26 UTC
For completeness (and historical record), the veto is about an earlier commit, with an early version of the patch, now removed from this issue.

After the huge discussion on the dev list, it seems clear that the only way to MAYBE get consensus would be to merge Pedro's newer, final, patch and the existing behavior, making the value of 0 ^ 0 depend on a configuration option and keeping the existing behavior by default. See also issue 121773 for another use case that would benefit from the same configuration option.

This assumes that a configuration option qualifies as an "implementation-defined behavior" per the ODF standard; but this should be checked as well before any further work on this issue.
Comment 38 Pedro Giffuni 2013-02-17 22:06:33 UTC
When I said I fixed all the issues, I meant I fixed ALL the issues.

I think Rob is overreacting and should take the time to look at the patch before vetoing.
Comment 39 Rob Weir 2013-02-17 22:37:22 UTC
Actually, I have a veto on changing the user-visible behavior of POWER() or the exponentiation operation via any patch associated with this issue.

Of course, I may be persuaded by discussion on the dev list, including an explanation by Pedro on why he thinks a newer patch addresses the issues that led to the veto.

But since a veto is outstanding on this issue, no patch should be committed.
Comment 40 Rob Weir 2013-02-17 22:47:18 UTC
(In reply to comment #38)
> When I said I fixed all the issues, I meant I fixed ALL the issues.
> 
> I think Rob is overreacting and should take the time to look at the patch
> before vetoing.

#1 Since we agreed to not revert vetoed patches quickly, then it makes sense to track the vetoes so they can be reverted when we get closer to a release.

#2 Your patch has:  	

#define SAL_STRICTER_MATH

So you are enabling the alternative behavior (non-backwards compatible) by default.
Comment 41 Pedro Giffuni 2013-02-17 23:01:12 UTC
(In reply to comment #40)
> (In reply to comment #38)
> > When I said I fixed all the issues, I meant I fixed ALL the issues.
> > 
> > I think Rob is overreacting and should take the time to look at the patch
> > before vetoing.
> 
> #1 Since we agreed to not revert vetoed patches quickly, then it makes sense
> to track the vetoes so they can be reverted when we get closer to a release.
> 

I haven't agreed on anything. I think your act was brutal and against Apache policies and should be repaired. 

> #2 Your patch has:  	
> 
> #define SAL_STRICTER_MATH
> 
> So you are enabling the alternative behavior (non-backwards compatible) by
> default.

I am agnostic. I think some people may want the default behaviour while other want to be use the alternative option. With my patch people can decide during compile time.

Ultimately I think it's the community that must take the decision (by voting) before the release. so feel free to submit the configure instrumentation and start a poll to see what should be the default behaviour.
Comment 42 orcmid 2013-02-17 23:45:11 UTC
(In reply to comment #37)
> For completeness (and historical record), the veto is about an earlier
> commit, with an early version of the patch, now removed from this issue.
> After the huge discussion on the dev list, it seems clear that the only way
> to MAYBE get consensus would be to merge Pedro's newer, final, patch and the
> existing behavior, making the value of 0 ^ 0 depend on a configuration
> option and keeping the existing behavior by default. See also issue 121773
> for another use case that would benefit from the same configuration option.
> This assumes that a configuration option qualifies as an
> "implementation-defined behavior" per the ODF standard; but this should be
> checked as well before any further work on this issue.

The only value of a configuration option is if it travels with the spreadsheet.  Otherwise, we are simply creating an interoperability mess everywhere.

There is an ODF-specific technique for including implementation-dependent settings in the <office:settings> element of a document.  The implementation that an individual set of settings apply to is identified in the naming of each set.  The only constraint on however the suggested configuration control be conveyed is this: "An ODF Consumer shall use application settings to alter application behavior only within the dimensions of variability permitted by this standard."  We're clear that POWER(0,0) falls into that case.

For this to be useful, it is good to make it known what the particular <config:config-item-set> definition is, and how it is identified, so that others could adopt it.  (That is, make the provision implementation-defined.)

There are probably other considerations that depend on how the ODF specifications are evolved, but use of <office:settings> should do for now.  It is probably good to define the setting as applicable only to ODF 1.2 documents just in case.
Comment 43 Rob Weir 2013-02-18 00:05:28 UTC
(In reply to comment #42)

> 
> The only value of a configuration option is if it travels with the
> spreadsheet.  Otherwise, we are simply creating an interoperability mess
> everywhere.
> 

That could be an interesting approach, since you could toggle the configuration option automatically spreadsheets that originate from Excel.  (In fact, maybe just make it be a "excel-compat-mode" flag, since that can be used to trigger a variety of adaptions.

You mention one approach to do this in ODF.  But we would also need an approach that works with XLS files as well, since some AOO users set the MS binaries formats as the default.
Comment 44 Pedro Giffuni 2013-02-18 00:19:19 UTC
Sorry I did my part already and I don't want a piece of it anymore.
(anyway I can stop getting these comments?).
Comment 45 orcmid 2013-02-18 02:06:18 UTC
(In reply to comment #43)
> (In reply to comment #42)

> 
> The only value of a configuration option is
> if it travels with the
> spreadsheet.  Otherwise, we are simply creating an
> interoperability mess
> everywhere.
> 

That could be an interesting
> approach, since you could toggle the configuration option automatically
> spreadsheets that originate from Excel.  (In fact, maybe just make it be a
> "excel-compat-mode" flag, since that can be used to trigger a variety of
> adaptions.

You mention one approach to do this in ODF.  But we would also
> need an approach that works with XLS files as well, since some AOO users set
> the MS binaries formats as the default.

I agree, having an excel-compat-mode in the settings would work fine.  Having it set to that automatically on import of .xls and .xlsx would preserve the interop bidirectionally.  If the document is saved as an .ods, there would be a discrepancy for for a compat-unaware consumer, but that is no different than getting it from .xls and .xslx directly.  Also, Excel could signal compat-mode in the .ods files that software produces (at some point).  

If a new file is saved as .xsl[x], it is a little late.  I suppose setting the default to produce .xls[x] would be a reason for setting excel-compat-mode though.  There will be gray cases and now we're talking about a lot of interaction around this particular peculiarity.

@Pedro - I see you are not in the CC: list.  But if you are subscribed to commits@ oo.a.o, you get the comments the same as all subscribers.