## [Solved] Formula overflow (Err:512): raise the tokens limit?

### [Solved] Formula overflow (Err:512): raise the tokens limit?

[Actually this problem is not really solved, but I've had an answer to my question...]

Hi all,

Is there a way to raise the (rather arbitrary) limit of the number of allowed tokens in OOo Calc formulas?

I have a "512" error in one of my Calc files, and the help file says that it means that "the total number of internal tokens, (that is, operators, variables, brackets) in the formula exceeds 512". Indeed, there are 516 tokens in my formula...

Is it hardcoded in OOo, or is there an environment variable or obscure configuration option somewhere to set that limit to, say, 1024?

N. B. : Splitting the formula in two cells is NOT an option.

Thanks in advance,

--ghe

Hi all,

Is there a way to raise the (rather arbitrary) limit of the number of allowed tokens in OOo Calc formulas?

I have a "512" error in one of my Calc files, and the help file says that it means that "the total number of internal tokens, (that is, operators, variables, brackets) in the formula exceeds 512". Indeed, there are 516 tokens in my formula...

Is it hardcoded in OOo, or is there an environment variable or obscure configuration option somewhere to set that limit to, say, 1024?

N. B. : Splitting the formula in two cells is NOT an option.

Thanks in advance,

--ghe

Last edited by ghe on Thu Sep 16, 2010 11:33 pm, edited 2 times in total.

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Hard coded as far as I can see. Your option might be to eliminate some pair of parentheses if that can be done without arithmetic ambiguity, although at the cost of clarity.

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Thanks for the quick reply, RoryOF. Where do you see that is it hard-coded?

Unfortunately, eliminating parentheses is also not possible: the formula is a nested =IF(...;...;IF(...;...;IF(...;...;...))), with very few arithmetic calculations.

Unfortunately, eliminating parentheses is also not possible: the formula is a nested =IF(...;...;IF(...;...;IF(...;...;...))), with very few arithmetic calculations.

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

One of the documents I came across in a quick Google suggested that it was a limit compiled into the code. I didn't note the reference. If you posed the problem of what you had to evaluate, perhaps some volunteers might come up with an alternate approach.

Edit: http://wiki.services.openoffice.org/wik ... rror_Codes says it is a compiler error; that being the case, it might be worth exploring some of the variants (go-oo and others(?)) of OpenOffice in case they have altered the internal structure |

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Personally, if my formulas get to be one-quarter that size, I know it's time to re-think how I'm doing it. Usually, there is some other approach, or some other function that allows the same job to be done with a radically smaller formula.

For example, long strings of nested IFs can usually be replaced by one table lookup. The lookup is usually more efficient and--more importantly--much easier to verify and maintain.

For example, long strings of nested IFs can usually be replaced by one table lookup. The lookup is usually more efficient and--more importantly--much easier to verify and maintain.

AOO4/LO5 • Linux • Fedora 23

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

I thought so too, Acknak. Another approach might be better.

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Thank you, guys, but this is not helpful. I know what I'm doing, and no, another approach is definitely not better. Actually, the formulas in the sheet are automatically generated (which guarantees that they are correct). The outer IF checks for the type of the input values (e.g., IF(NOT(AND(OR(ISNUM(X1),X1="NA",TRIM(X1)="");OR(ISNUM(Y1),X1="NA",TRIM(Y1)="");OR(ISNUM(Z1),X1="NA",TRIM(Z1)="");"ERROR";...) checks that X1, Y1 and Z1 are either a number, a "NA" string or an empty string). The inner IFs do the actual calculation: if one of the inputs is "NA", then "NA", else if one of the inputs is <0, then 0, else .... Such a formula quickly grows to 512 tokens.

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

You could try Gnumeric instead.

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

You could perhaps split the formula over a few cells; one might hold the tests for X1, one for Y1, one for Z1, then in the cell with the main formula you can just apply the logic for those cells.

OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu

If your problem has been solved or your question has been answered, please edit the

Nederlandstalig forum

If your problem has been solved or your question has been answered, please edit the

*first*post in this thread and add [Solved] to the title bar.Nederlandstalig forum

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

No, Gnumeric is not an option either. floris v, thank you, but please read my question: "Splitting the formula in two cells is NOT an option."

My question was: is it possible to raise the 512 tokens limit in OpenOffice Calc, if so, how, if not, why?

I think I'll file a bugreport for this. After all, we now have one mega-rows in Calc files (which may seem useless for most people), why should we live with this old arbitrary limit (already present at least since OOo 2.3.0 (i.e. September 2007)).

My question was: is it possible to raise the 512 tokens limit in OpenOffice Calc, if so, how, if not, why?

I think I'll file a bugreport for this. After all, we now have one mega-rows in Calc files (which may seem useless for most people), why should we live with this old arbitrary limit (already present at least since OOo 2.3.0 (i.e. September 2007)).

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Sorry, but I just thought that necessity knows no law.

OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu

If your problem has been solved or your question has been answered, please edit the

Nederlandstalig forum

If your problem has been solved or your question has been answered, please edit the

*first*post in this thread and add [Solved] to the title bar.Nederlandstalig forum

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

First, your example uses comma (,) in AND() and OR(), which is valid for MS Excel but not OOo calc. Let's take it as a semicolon (;) though. As for your demand to raise limit, it can be done if you compile your own version but that's not easy. So you may shorten your formula by using another way that shall meet your no-split requirement.ghe wrote:Thank you, guys, but this is not helpful. I know what I'm doing, and no, another approach is definitely not better. Actually, the formulas in the sheet are automatically generated (which guarantees that they are correct). The outer IF checks for the type of the input values (e.g., IF(NOT(AND(OR(ISNUM(X1),X1="NA",TRIM(X1)="");OR(ISNUM(Y1),X1="NA",TRIM(Y1)="");OR(ISNUM(Z1),X1="NA",TRIM(Z1)="");"ERROR";...) checks that X1, Y1 and Z1 are either a number, a "NA" string or an empty string). The inner IFs do the actual calculation: if one of the inputs is "NA", then "NA", else if one of the inputs is <0, then 0, else .... Such a formula quickly grows to 512 tokens.

My opinion is to use mathematical calculation to replace logical operator. For example, =OR(A;B;C;D;E;F;G) equals to =A+B+C+D+E+F+G, and this reduces 3 tokens. *My test reveals anything you type is taken into account of the quantity of token -- even a semicolon counts.

Your given formula can be reduced a bit

IF(NOT(OR(ISNUM(X1);X1="NA";TRIM(X1)="")*OR(ISNUM(Y1);X1="NA";TRIM(Y1)="")*OR(ISNUM(Z1);X1="NA";TRIM(Z1)=""));"ERROR";...)

--> this reduces 3 tokens: and(A;B;C) to A*B*C

Besides, your example can be reduced more:

IF(NOT(OR(ISNUM(X1);TRIM(X1)="")*OR(ISNUM(Y1);TRIM(Y1)="")*OR(ISNUM(Z1);TRIM(Z1)="")+(X1="NA"));"ERROR";...)

--> this reduces more 8*2-2-1=13 tokens since X1="NA" is duplicated.

And some logical determination can be replaced:

IF(NOT((ISNUM(X1)+LEN(TRIM(X1)))*(ISNUM(Y1)+LEN(TRIM(Y1)))*(ISNUM(Z1)+LEN(TRIM(Z1)))+(X1="NA"));"ERROR";...)

--> this helps the trick to eliminate OR with the aid of the transformation from TRIM(X1)="" to LEN(TRIM(X1)), at least 1*3=3 tokens are saved in this step!

I "shrink" your given example by 19 tokens. As your problem occurs with few difference of 516-512 = 4 tokens, I think this technique does help.

OOo 3.2.1 on Ms Windows XP

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Well, whatever you do, I don't see any reasonable way to have Calc handle longer formulas, so you'll have to either cut some of the formula, as ToresVN has suggested, or use a different approach.

Have you considered using some macro code to handle the bulk of the task? That could shorten the formula as much as you like.

Have you considered using some macro code to handle the bulk of the task? That could shorten the formula as much as you like.

AOO4/LO5 • Linux • Fedora 23

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

It should be very easy to split the formula across more than one cell.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.

Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

An approach would be to copy the conditional information, suitably trimmed in the process, out to new columns or even to a new sheet, then make the formula calculate from those, knowing that they contained correct data, properly prepared.

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

floris v: Yes, I agree with that principle, but since I explicitly wrote in my question that it is not possible to split formulas, it probably meant that I considered that option...

ToresVN: Thank you, I did think about it, but it only gives a marginal improvement, and I am looking for a general solution, i.e. applicable to much bigger formulas (e.g. 1000 tokens).

acknak: No, macros are also not a solution. Splitting the formulas is NOT possible, and I don't see what you mean by "using a different approach".

Villeroy: Splitting the formulas is NOT possible. It's one of the constraints of the problem (see my first post).

RoryOF: Splitting the formulas is NOT possible...

To sum the problem up in the shortest possible statement: Is there a way to raise the upper limit of allowed tokens in OOo Calc formulas (currently 512)? Splitting the formulas is not possible. Using macros is not possible. I just want to know if it is possible to raise that limit (and if so, how).

BTW, Excel allows 8192 characters since Excel 12 (i.e. 2007).

I filed a bugreport (http://qa.openoffice.org/issues/show_bug.cgi?id=114451) for this, let's see what happens.

P. S. : Did I mention that splitting the formulas is NOT possible ?

ToresVN: Thank you, I did think about it, but it only gives a marginal improvement, and I am looking for a general solution, i.e. applicable to much bigger formulas (e.g. 1000 tokens).

acknak: No, macros are also not a solution. Splitting the formulas is NOT possible, and I don't see what you mean by "using a different approach".

Villeroy: Splitting the formulas is NOT possible. It's one of the constraints of the problem (see my first post).

RoryOF: Splitting the formulas is NOT possible...

To sum the problem up in the shortest possible statement: Is there a way to raise the upper limit of allowed tokens in OOo Calc formulas (currently 512)? Splitting the formulas is not possible. Using macros is not possible. I just want to know if it is possible to raise that limit (and if so, how).

BTW, Excel allows 8192 characters since Excel 12 (i.e. 2007).

I filed a bugreport (http://qa.openoffice.org/issues/show_bug.cgi?id=114451) for this, let's see what happens.

P. S. : Did I mention that splitting the formulas is NOT possible ?

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Here's where you can change the formula size limits: http://svn.services.openoffice.org/open ... ler.hxx#63

It seems you'll either have to compile a custom version of Calc, or use Excel.

It seems you'll either have to compile a custom version of Calc, or use Excel.

AOO4/LO5 • Linux • Fedora 23

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Cool, thank you acknak! I'll add that to the bugreport. Now I know for sure that there its no obscure environment variable or parameter I can modify...

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Why not? Are you running out of cells?ghe wrote: N. B. : Splitting the formula in two cells is NOT an option.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.

Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Following the ToresVN explanation, I think you can reduce and improve the formula. This solve your problem in a elegant way.

Better the brute force by the end. Give me a bigger truck , since I do not want to place the boxes tidily. Forgives, only a reflexion.

Better the brute force by the end. Give me a bigger truck , since I do not want to place the boxes tidily. Forgives, only a reflexion.

LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Villeroy: No, I'm not running out of cells. But it's one of the elements/constraints of the problem. I know very well that it is possible to split formulas, but in this particular case it is not possible (or extremely undesirable, if you prefer).

mriosv: Yes, but as I said, reducing the formulas with a few logical-arithmetic tricks does not solve the problem in general (that is, for formulas much bigger than 512 tokens).

mriosv: Yes, but as I said, reducing the formulas with a few logical-arithmetic tricks does not solve the problem in general (that is, for formulas much bigger than 512 tokens).

OpenOffice 3.2 on Debian Testing

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

It depends on how you evaluate your work. And also a truth to OOo calc is its poor efficiency when compared to MS Excel 2007 (and above); I mean particularly the ability to utilize multi-core CPU. Another fact you might not believe, MS Excel 2003 supports merely 900 tokens, not good enough to meet your requirement, saying 1024, if you have a formula containing *much* more than 512 tokens. The other days, someone might ask a similar question like you but he asks more, 2048 tokens.ghe wrote:mriosv: Yes, but as I said, reducing the formulas with a few logical-arithmetic tricks does not solve the problem in general (that is, for formulas much bigger than 512 tokens).

In general usage, we general users may not be familiar with your much-more-than-512-tokens-formula. As for me and the other replies in this thread, spliting is surely a better plan. Besides, your formula as a given example was obviously not well-organized. It did not convince me (at least) to use a non-split formula. There must be common parts in your whole sheet and those parts ought to be calculated first. My trick looks okay to solve your real problem, 516 tokens. If you want an unlimited application, I strongly suggest you to use a real database like SQL, etc.

My experience tells nested IF() structure is not good in OOo calc. Ever I wrote a formula which was really much more complex than yours and it contained not more than 200 tokens with little numerical computing but heavily nested IF() structure. At first I thought it performed perfectly but somehow OOo calc became very unstable. I was not able to reproduce that error. Then everything went well once I split the formula. I lost nothing but gained more stable operations, easier debugging environment, and much more faster calculating speed.

Anyway, I voted for your issue. But I did not agree with your comment since it did not surprise me to be a defect. However it could be a defect if one tries to import from a Excel file. As Excel supports around 900 tokens. A formula works in Excel may fail in OOo.

PS: Your given example contained only 77 token, and my trick saved you 19 tokens, an amazing 25%. WinRAR / 7z could't do that. I also didn't agree my trick did little effort.

OOo 3.2.1 on Ms Windows XP

### Re: Formula overflow (Err: 512): raise the 512 tokens limit?

Yes, I know Excel 2003 and earlier support fewer tokens (actually the limit is in characters, and is 1024): see the bugreport. But Excel now has supported 8192 characters for 3 years; that's why the bugreport asks to raise the limit to 8192 tokens, which is enough to process any Excel file correctly (since 8192 characters can be analysed into at most 8192 tokens).

I agree that general users are not familiar with very large formulas, but (as I wrote in the bugreport) general users are also not familiar with using 1 mega-rows, and yet Calc (and Excel) supports them.

My example formula had indeed a small error, it should of course read "IF(NOT(AND(OR(ISNUM(X1),X1="NA",TRIM(X1)="");OR(ISNUM(Y1),Y1="NA",TRIM(Y1)="");OR(ISNUM(Z1),Z1="NA",TRIM(Z1)="");"ERROR";...)", that is, X1="NA" should only appear once, as my sentence "checks that X1, Y1 and Z1 are either a number, a "NA" string or an empty string" suggests.

I know that there are other tools and other ways to do the job: using a database, using Excel, using Gnumeric, splitting the formula, using macros, etc. But with the particular problem I currently work on, I have to use Calc, and can't split formulas, and can't use macros, etc. Believe me, I checked each other possible way. That's the difference between theory and a concrete problem, where you have to deal with concrete users and concrete machines.

Finally, I have to disagree with your experience on Calc becoming unstable when dealing with nested IF's. I daily work (and have been since about 4 years) with a Calc file which is 2 Mb big when it contains no data, with very complicated formulas (with complicated numerical computing *and* complicated non-numerical (string) computing *and* nested IF's). It does take about 30 seconds to open, but it works perfectly well, and I can use it during whole days without problems. In fact, I think Calc never crashed while I was using it.

Anyway... thank you for the vote!

I agree that general users are not familiar with very large formulas, but (as I wrote in the bugreport) general users are also not familiar with using 1 mega-rows, and yet Calc (and Excel) supports them.

My example formula had indeed a small error, it should of course read "IF(NOT(AND(OR(ISNUM(X1),X1="NA",TRIM(X1)="");OR(ISNUM(Y1),Y1="NA",TRIM(Y1)="");OR(ISNUM(Z1),Z1="NA",TRIM(Z1)="");"ERROR";...)", that is, X1="NA" should only appear once, as my sentence "checks that X1, Y1 and Z1 are either a number, a "NA" string or an empty string" suggests.

I know that there are other tools and other ways to do the job: using a database, using Excel, using Gnumeric, splitting the formula, using macros, etc. But with the particular problem I currently work on, I have to use Calc, and can't split formulas, and can't use macros, etc. Believe me, I checked each other possible way. That's the difference between theory and a concrete problem, where you have to deal with concrete users and concrete machines.

Finally, I have to disagree with your experience on Calc becoming unstable when dealing with nested IF's. I daily work (and have been since about 4 years) with a Calc file which is 2 Mb big when it contains no data, with very complicated formulas (with complicated numerical computing *and* complicated non-numerical (string) computing *and* nested IF's). It does take about 30 seconds to open, but it works perfectly well, and I can use it during whole days without problems. In fact, I think Calc never crashed while I was using it.

Anyway... thank you for the vote!

OpenOffice 3.2 on Debian Testing