Ask Your Question
0

Sudoku solver, calc internal tokens number exceeded [closed]

asked 2012-12-12 19:46:15 +0200

anonymous user

Anonymous

updated 2015-09-09 20:36:38 +0200

Alex Kemp gravatar image

Whats the maximum number of characters in libre office calc cell? I use LibreOffice 3.5 build 403 on openSUSE 12.2. When I try to enter quite long formula Libre shorten it from 964 char to 958 in one example, and 982 to 976 chars in an other example. I've tried searching on google but founded nothing usefull. So... two question

  1. Whats the maximum number of characters in a calc cell
  2. If max char is more than 1024, Why do Libre office shorten my formula?

/J

[edit] found that it is 512 chars, but somehow ignored that and allowed little more than that, between 950 and 1000

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 15:46:48.759137

Comments

Maybe exposing the formula, someone can help make it shorter. It seems too long to fight with.

m.a.riosv gravatar imagem.a.riosv ( 2012-12-13 00:47:11 +0200 )edit

Libre Office 4.0.2 on Ubuntu 13.04: formula in a cell: up to 1300 characters (not clear if it depends on character set) Plain text in a cell: up to 65535 characters (not clear if it depends on character set)

Paijo gravatar imagePaijo ( 2013-09-10 12:36:16 +0200 )edit

4 Answers

Sort by » oldest newest most voted
1

answered 2013-02-27 05:58:52 +0200

qubit gravatar image

updated 2013-03-07 23:52:49 +0200

@Qui writes:

[I] found that [the maximum number of characters in a libre office calc cell] is 512 chars, but somehow ignored that and allowed little more than that, between 950 and 1000

Qui -- can you tell us where you found this information so that we can note that when we resolve this question?

Thanks!


Update: The 512 error code appear to refer to internal tokens, not the raw number of characters one can put into a formula:

Compiler: the total number of internal tokens, (that is, operators, variables, brackets) in the formula exceeds 512.

If your variables are multi-character, if you have whitespace, if there's a multi-character operator, etc.., then the total number of characters allowed in a formula will be much higher than 512.

So instead of your original question "What's the max # of chars that can be used in a formula?", I think it's more helpful for you to know "What's the max # of variables, functions, operators, etc.. that can be used in a formula?" (Ans: 512, as noted above :-)

edit flag offensive delete link more

Comments

1

I see that I don't mentioning that I get error 512, really bad of me. But here are a link to the page where it tells the 512 char limit. https://help.libreoffice.org/Calc/Error_Codes_in_Calc

Qui gravatar imageQui ( 2013-03-07 18:11:42 +0200 )edit
1

Since LibreOffice 4.0 the maximum number of tokens is 8192.

erAck gravatar imageerAck ( 2014-03-31 11:46:25 +0200 )edit
1

answered 2012-12-13 13:18:04 +0200

Qui gravatar image

updated 2012-12-13 13:34:24 +0200

Thanks :) Well, I tried to make a suduko (9 rows x 9 column) solver in calc. Now when I changed from swedish to english the problem dissaper, formula got little shorter. But here it is: If '1' is in same row, column or same 3x3 area, do't show '1'. Every small test return '1' if no '1' is found and multiplied together, so if product of multiplication is zero, there is a '1' present.


=IF(IF(ROW()=1,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),1,":",CHAR(65+COLUMN()),ROW()-1)),2,0)))*IF(ROW()=9,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),ROW()+1,":",CHAR(65+COLUMN()),9)),2,0)))*IF(COLUMN()=1,1,ISERROR(HLOOKUP(1,INDIRECT(CONCATENATE("A",ROW(),":",CHAR(63+COLUMN()),ROW()+1)),2,0)))*IF(COLUMN()=9,1,ISERROR(HLOOKUP(1,INDIRECT(CONCATENATE(CHAR(65+COLUMN()),ROW(),":I",ROW()+1)),2,0)))*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN(),3)),1+3*INT((ROW()-1)/3)+MOD(ROW(),3)))=1,0,1)*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN()+1,3)),1+3*INT((ROW()-1)/3)+MOD(ROW(),3)))=1,0,1)*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN(),3)),1+3*INT((ROW()-1)/3)+MOD(ROW()+1,3)))=1,0,1)*IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN()+1,3)),1+3*INT((ROW()-1)/3)+MOD(ROW()+1,3)))=1,0,1),"1","")

Divided into smaller parts : If this cell is first row, 'return' 1, else search for '1' in this column from row 1 to this row minus one.


IF(ROW()=1,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),1,":",CHAR(65+COLUMN()),ROW()-1)),2,0)))

If row is '9' (last) return '1', else search for '1' in this row plus 1 to row 9


IF(ROW()=9,1,ISERROR(VLOOKUP(1,INDIRECT(CONCATENATE(CHAR(64+COLUMN()),ROW()+1,":",CHAR(65+COLUMN()),9)),2,0)))

then two similar but for columns. After that there are four checks for the four other cells that has not been checked with above formula in 3x3 area.

Search the upper left cell that has not been cheacked earlier


IF(INDIRECT(CONCATENATE(CHAR(3*INT((COLUMN()-1)/3)+65+MOD(COLUMN(),3)),1+3*INT((ROW()-1 ...
(more)
edit flag offensive delete link more
1

answered 2013-02-04 16:05:08 +0200

m.a.riosv gravatar image

If you are using several times in every cell the function CONCATENATE(), you can replace it:

=CONCATENATE("A";"B";"C") -> "ABC"

="A"&"B"&"C" -> "ABC"

LibreOffice help:
image description

edit flag offensive delete link more
1

answered 2012-12-13 02:34:43 +0200

ROSt52 gravatar image

updated 2012-12-13 02:35:09 +0200

I am surprised about the length of your formulas and admire you for understanding such long ones. Why don't you break them down? If you don't want to see results in between you might be able to hide rows or columns.

edit flag offensive delete link more

Question Tools

2 followers

Stats

Asked: 2012-12-12 19:46:15 +0200

Seen: 12,801 times

Last updated: Mar 28 '14