Saving as XLS deletes formula

Discuss the spreadsheet application

Saving as XLS deletes formula

Postby AylaAsperger » Sat May 17, 2008 10:29 am

Hello,

I have the Dutch version of OOo 2.4 installed on windows xp pro, and I am running in to the following problem:

I have a spreadsheet that uses the =EN() formula which is probably the =AND() formula in the English version of OOo. This formula works perfectly, except when I save my file as an XLS and I close and reopen that file, the formula is gone and there is an (N/B) instead of it. When I save my file as an ODS it doesnt happen.

I hope anybody can help me out with this!
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)
User avatar
AylaAsperger
 
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Saving as XLS deletes formula

Postby jrkrideau » Sat May 17, 2008 5:02 pm

AylaAsperger wrote:Hello,

I have the Dutch version of OOo 2.4 installed on windows xp pro, and I am running in to the following problem:

I have a spreadsheet that uses the =EN() formula which is probably the =AND() formula in the English version of OOo. This formula works perfectly, except when I save my file as an XLS and I close and reopen that file, the formula is gone and there is an (N/B) instead of it. When I save my file as an ODS it doesnt happen.

I hope anybody can help me out with this!


I am on a machine witb 2.3.1 but I don't seem to have that problem when I save to Excel 97/2000 ( using the AND() function) but I am using a very simple 2 condition statement.

Was the document orginally created in OOo?
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3795
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Saving as XLS deletes formula

Postby AylaAsperger » Sat May 17, 2008 9:37 pm

Rideaujrk wrote:I am on a machine witb 2.3.1 but I don't seem to have that problem when I save to Excel 97/2000 ( using the AND() function) but I am using a very simple 2 condition statement.

Was the document orginally created in OOo?


Yes, it is originally made in OOo. The formula is written in the way of =EN(statement1;statement2;.....) with as much as 64 statements. It works perfectly, untill I have closed the document and it disappears, to be replaced bij (N/B)
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)
User avatar
AylaAsperger
 
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands

Re: Saving as XLS deletes formula

Postby Villeroy » Sat May 17, 2008 11:15 pm

As far as I know the count of arguments is limited to 30 in both Calc and Excel. I wonder why Calc accepts 64 arguments. When saving in Excel format I notice that =AND(arg1;arg2;...;arg30) works as expected whereas AND with 31 arguments becomes =NA() after reload.
Code: Select all   Expand viewCollapse view
=AND(1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30)
=AND(1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29107
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Saving as XLS deletes formula

Postby AylaAsperger » Sat May 17, 2008 11:19 pm

Villeroy wrote:As far as I know the count of arguments is limited to 30 in both Calc and Excel. I wonder why Calc accepts 64 arguments. When saving in Excel format I notice that =AND(arg1;arg2;...;arg30) works as expected whereas AND with 31 arguments becomes =NA() after reload.


Yeah I wonder to why 64 works untill closing the document, and when saved as ODS. But thanks for this tip! I will divide my formula up in 3 parts which would probably solve the problem!

Thank you very much for your help in this matter!
OOo: OpenOffice.org 4.1.8 (English)
OS: Windows 10, 64-bit (English)
User avatar
AylaAsperger
 
Posts: 18
Joined: Sat May 17, 2008 10:00 am
Location: The Netherlands


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 21 guests