[Solved] Err:512 after pasting Excel formula

[Jaguar57]

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

[robleyd]

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.

[Jaguar57]

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)

[robleyd]

Simply put, replace all commas with semicolons.

[Jaguar57]

I have a number of spreadsheets made with Excel.
Is there a program or a website that I can use to convert them to workable versions in OpenOffice?

[cwolan]

At first glance I'll guess you get Err:509.

I get Err:508.

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

[cwolan]

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.

[robleyd]

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.

[Jaguar57]

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)

[robleyd]

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.

[cwolan]

OP has yet to tell what error they were getting.

Indeed.

---------------------
Roses are Red, Violets are Blue.
I was acting without a clue.

[MrProgrammer]

I have a number of spreadsheets made with Excel.

My condolences to you.

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.

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.

[Jaguar57]

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.
This is about 1/4 of the spreadsheet:

Screen Shot 2024-09-05 at 12.23.22 PM.png (72.66 KiB) Viewed 458 times

[Jaguar57]

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)

[Zizi64]

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.

[Jaguar57]

great! thanks for the assist