[Solved] Err:512 after pasting Excel formula

Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

[Solved] Err:512 after pasting Excel formula

This line of code gives me an error. Should I break it into two sections for two different cells?
=IF(AND(H101>N96,H101<=N97),140-((H101-N96)/(N97-N96))*2.5,IF(AND(H101>N97,H101<=N98),137.5-((H101-N97)/(N98-N97))*2.5,IF(AND(H101>N98,H101<=N99),135-((H101-N98)/(N99-N98))*2.5,IF(AND(H101>N99,H101<=N100),132.5-((H101-N99)/(N100-N99))*2.5,IF(AND(H101>N100,H101<=N101),130-((H101-N100)/(N101-N100))*2.5,IF(AND(H101>N101,H101<=N102),127.5-((H101-N101)/(N102-N101))*2.5,IF(AND(H101>N102,H101<=N103),125-((H101-N102)/(N103-N102))*2.5,IF(AND(H101>N103,H101<=N104),122.5-((H101-N103)/(N104-N103))*2.5,IF(AND(H101>N104,H101<=N105),120-((H101-N104)/(N105-N104))*2.5,IF(AND(H101>N105,H101<=N106),117.5-((H101-N105)/(N106-N105))*2.5,IF(AND(H101>N106,H101<=N107),115-((H101-N106)/(N107-N106))*2.5,IF(AND(H101>N107,H101<=N108),112.5-((H101-N107)/(N108-N107))*2.5,IF(AND(H101>N108,H101<=N109),110-((H101-N108)/(N109-N108))*2.5,IF(AND(H101>N109,H101<=N110),107.5-((H101-N109)/(N110-N109))*2.5,IF(AND(H101>N110,H101<=N111),105-((H101-N110)/(N111-N110))*2.5,IF(AND(H101>N111,H101<=N112),102.5-((H101-N111)/(N112-N111))*2.5,IF(AND(H101>N112,H101<=N113),100-)))))))))))))))))

 Edit: Changed subject, was avoiding an error in calc  Make your post understandable by others  -- MrProgrammer, forum moderator
Last edited by MrProgrammer on Thu Sep 12, 2024 5:42 pm, edited 4 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.1.15 on MacOS 10.13
robleyd
Moderator
Posts: 5204
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: avoiding an error in calc

At first glance I'll guess you get Err:509. The argument separator in OpenOffice Calc is a semi-colon (;) not a comma as you have. If that doesn't solve your problem, please tell us what the actual error is.

I'd also suggest you might consider using a lookup table and VLOOKUP() rather than a complex nested IF() to achieve your desired outcome.
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.8.1.2; SlackBuild for 24.8.1 by Eric Hameleers
---------------------
```Roses are Red, Violets are Blue
Unexpected '{' on line 32```
.
Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

Re: avoiding an error in calc

Do you mean this
=IF(AND(H101>N96,H101<=N97),140-((H101-N96)/(N97-N96))*2.5,0)
should be this
=IF(AND(H101>N96;H101<=N97),140-((H101-N96)/(N97-N96))*2.5,0)
or this?
=IF(AND(H101>N96,H101<=N97);140-((H101-N96)/(N97-N96))*2.5;0)
OpenOffice 4.1.15 on MacOS 10.13
robleyd
Moderator
Posts: 5204
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: avoiding an error in calc

Simply put, replace all commas with semicolons.
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.8.1.2; SlackBuild for 24.8.1 by Eric Hameleers
---------------------
```Roses are Red, Violets are Blue
Unexpected '{' on line 32```
.
Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

Re: avoiding an error in calc

Is there a program or a website that I can use to convert them to workable versions in OpenOffice?
OpenOffice 4.1.15 on MacOS 10.13
cwolan
Posts: 146
Joined: Sun Feb 07, 2021 3:44 pm

Re: avoiding an error in calc

robleyd wrote: Thu Sep 05, 2024 6:20 am At first glance I'll guess you get Err:509.
I get Err:508.
wrote: The argument separator in OpenOffice Calc is a semi-colon (;) not a comma as you have.
That yields Err:512 (Formula overflow).

In addition, the formula appears to contain an unnecessary minus sign (U+002D).
Sign.png (13.79 KiB) Viewed 916 times
OpenOffice 1.1.5 – 4.1.15
LibreOffice 3.3.0.4 – 24.8
Windows 7,10,11 64-bit
cwolan
Posts: 146
Joined: Sun Feb 07, 2021 3:44 pm

Re: avoiding an error in calc

LibreOffice: The formula without the said character may work. At first glance — no error.
LO limits the total number of internal tokens to 8192 while AOO sets the limit to 512.
OpenOffice 1.1.5 – 4.1.15
LibreOffice 3.3.0.4 – 24.8
Windows 7,10,11 64-bit
robleyd
Moderator
Posts: 5204
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: avoiding an error in calc

A simple formula with comma separator in OpenOffice Calc gives me:
comasep.png (14.7 KiB) Viewed 890 times
OP has yet to tell what error they were getting.
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.8.1.2; SlackBuild for 24.8.1 by Eric Hameleers
---------------------
```Roses are Red, Violets are Blue
Unexpected '{' on line 32```
.
Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

Re: avoiding an error in calc

What I copied and pasted had an obvious error that was made by my trying to convert it
but now all is straightened out.
There were two places where I had to cut the long line of code in half.
I am using the converter at cloudconvert.com and it seems to work good.

Are Go Office and Libre Office the same or more limited versions of Open Office?
(thanks for your help. It's much appreciated)
OpenOffice 4.1.15 on MacOS 10.13
robleyd
Moderator
Posts: 5204
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: avoiding an error in calc

From https://www.libreoffice.org/:
LibreOffice is a free and powerful office suite, and a successor to OpenOffice.org (commonly known as OpenOffice).
It is what OpenOffice could be if it had more resources. Our resident poster person for LO will be along shortly to tell you more about it.

I have no idea what Go Office is, other than a UK office furniture supplier.
Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.8.1.2; SlackBuild for 24.8.1 by Eric Hameleers
---------------------
```Roses are Red, Violets are Blue
Unexpected '{' on line 32```
.
cwolan
Posts: 146
Joined: Sun Feb 07, 2021 3:44 pm

Re: avoiding an error in calc

robleyd wrote: Thu Sep 05, 2024 8:53 am OP has yet to tell what error they were getting.
Indeed.

---------------------
Roses are Red, Violets are Blue.
I was acting without a clue.
OpenOffice 1.1.5 – 4.1.15
LibreOffice 3.3.0.4 – 24.8
Windows 7,10,11 64-bit
MrProgrammer
Moderator
Posts: 5041
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: avoiding an error in calc

Jaguar57 wrote: Thu Sep 05, 2024 7:45 am I have a number of spreadsheets made with Excel.
My condolences to you.
Jaguar57 wrote: Thu Sep 05, 2024 7:45 am Is there a program or a website that I can use to convert them to workable versions in OpenOffice?
That program is OpenOffice (or LibreOffice). If the spreadsheet contains a valid Calc formula, just open the Excel file with Calc and it will convert all the formulas to Calc syntax, replacing comma with semicolon and other needed changes. But 100- (near the end) is not a valid expression in Calc because the subtraction operator is missing the second operand.

robleyd wrote: Thu Sep 05, 2024 6:20 am I'd also suggest you might consider using a lookup table and VLOOKUP() rather than a complex nested IF() to achieve your desired outcome.
That should work. Personally, I would use the SUMPRODUCT function because it helps me understand the calculation. I'd set defined name Foo to 142.5-ROW(A1:A17)*2.5 then replace the entire nested IF mess with:
=SUMPRODUCT(\$H\$101>N96:N112;\$H\$101<=N97:N113;Foo-(\$H\$101-N96:N112)/(N97:N113)*2.5).

[Tutorial] The SUMPRODUCT function

I had to guess at the intent of the formula where there is a syntax error. I didn't test because Jaguar57 didn't attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). I will not help further unless they attach a spreadsheet document.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.7, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

Re: Err:508 after pasting Excel formula

That formula was part of the calculation of 2 stroke engine port open duration (in degrees) given the millimeters above port.
So you guys think LibreOffice is better than OpenOffice?
My error # was 512.
Screen Shot 2024-09-05 at 12.23.22 PM.png (72.66 KiB) Viewed 458 times
OpenOffice 4.1.15 on MacOS 10.13
Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

Re: Err:508 after pasting Excel formula

When I open the spreadsheet in LibreOffice the macro buttons are shrunk so that the word "calculate" is cut off.
Can you tell me how to increase the horizontal length of the button? (it starts at D54)
Attachments
Screen Shot 2024-09-05 at 12.56.24 PM.png (58.39 KiB) Viewed 439 times
OpenOffice 4.1.15 on MacOS 10.13
Zizi64
Volunteer
Posts: 11402
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Err:512 after pasting Excel formula

Can you tell me how to increase the horizontal length of the button? (it starts at D54)
Make visible the Form Controls Toolbar: View - Toolbars - Form Controls.
Switch the Form Control elements into Edit mode, then drag the Button wider (or set the width numerically). Finally Switch OFF the Edit mode.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Jaguar57
Posts: 7
Joined: Thu Sep 05, 2024 5:24 am

Re: Err:512 after pasting Excel formula

great! thanks for the assist
OpenOffice 4.1.15 on MacOS 10.13