Calc - multiple.operations() shows wrong results when some internal limit is reached

English
, , , , ,
#1

With your help I am successfully computing multiple operations in Calc. Works like a charm for a small number of cells (about 10,000).

When my formula gets larger, the results given in the cross-table are all the same. I can reproduce this error. A mistake in those formulas is unlikely, as I use drag fill. I suspect memory issues.

Running on LO 3.5.7.2 with 8GB RAM, with about 50% free.

Any ideas on that issue? Thanks in advance!

Linked files:

working ods

and screenshot

broken ods
[updated]: https://db.tt/rK218h3Q and the old one [https://db.tt/Ez6F64U6]

and screenshot

edit: updated the broken file

edit2: title updated to match bug report 76447

#2

Both files have diferent Data and different reference in CrossTable.A1.

#3

I agree with @mariosv in that it is not clear from the data, what the expected vs observed results are. Perhaps edit your question to more clearly indicate an example. I don’t really understand what the Data sheet is attempting to do.

#4

@mariosv and @oweng ,both of you were right. I have updated the broken example accordingly.
The crosstable is supposed to take the result form the last step of the chain equation (V3) and modify it’s two input factors F1 and G1. To ease things up, I have used references in CrossTable.A1:A3.

#8

I can confirm this bug or feature. Open the broken file, remove all data after row 824 and write 1 to V825. CrossTable sheet will now show nicely changing data. Remove one row less and whole table contains the same number, since MULTIPLE.COMPUTATIONS() doesn’t work for some reason.

Since the calculations take close to no time in your case, this most likely is a hardcoded limit for calculation chain length (with your calculations the chain gets longer with every added row). Like most limits it should be raised due to improved performance of LO (cudos, developers!) and faster CPUs. iMHO it’s worth reporting.

#9

Thank you for trying out. On my version it already breaks at about row 400. I will have to check on other computers.
I think of writing a ticket, though.

#10

Thank you for the example, today I learned something new and it already helped me solve a problem easier than before.
I do run a 64-bit version of LO and memory settings are 100 MB graphics memory, 20 MB per object and 120 objects cached if that matters anything.

#11

I suspect that @mahfiaz is correct, even though I still do not understand the document and cannot replicate the results indicated by @mahfiaz (the results I get vary so much from test to test I can’t confirm anything). It sounds like it may be some form of hard limit in the LO calculations, like that for the goalseek facility (refer fdo#37341 and fdo#64499). These types of “what if?” functions seem to be a nightmare as there is always a use case that will break them.

#12

BTW, what is this calculation useful for? I even plotted the data but still no clue…

#13

I have tried with LOPortable 4.2.2.1 under windows too, still the same. In the options, I have maxed memory usage for both versions, no change. The cross-table only works if I have less then about 350 rows in the “Data”-sheet.
@oweng Do you think that avoiding the if-functions may result in an working document then?
@mahfiaz These sheets are part of a way bigger calculation for a research project. Is there any way to contact you in private, as I would prefer not discussing that in public?

#14

@oweng, if the results have different numbers in the table, it’s working. If whole table consists of the same number, then it is not, it just copies over the result from A1. This is the main problem, in case of hitting some limit one would expect #NA or some other error not just wrong values. This is the real bug. Limits to protect Calc from hanging are fine (although these could probably be higher).
@lactea, you could leave it, but if you would like to share, then mail is my username @gmail.com

#15

@lactea, @mahfiaz, I am only using the “working” ODS i.e., trying to break it. I have done this by copying row 299 on the Data sheet down to row 397 and then entering the following 1.00 Result in V398 (otherwise the entire Result column is 0.00 due to the forward reference). At this row the CrossTable results all display the same value. If I delete the content (not the row as this will cause reference errors) on rows 397-398 and again enter 1.00 in V397 the CrossTable results display OK.

#16

@mahfiaz, thank you for putting that in more understandable words.
@oweng, I have tried you way and can confim that. I was successfully able to break the working one by extending the calculation over row 396.

Also, the broken example can be “fixed” if all rows below 397 are deleted and 1 being put in V397.

This really looks like a hardcoded limitation now.

Filed in as bug 76447
https://bugs.freedesktop.org/show_bug.cgi?id=76447

#17

@lactea, thanks for reporting the bug. I am still unable to reproduce the 824/825 row change indicated by @mahfiaz and another commenter in the bug. My experience in both the working and broken examples correlates with your’s @lactea, in that that row 396/397 appears to be the boundary .

#18

There is no “other commenter” there, if you take a look at the mail address.

#19

@mahfiaz, oh, of course, sorry. :slight_smile:

#5

It is not a large spreadsheet, and consumes only an average amount of RAM (~760MB with both sheets open). I am not sure if it is a memory problem or not, but if it is, you may need to upgrade to v4.2 to gain the benefit of the improved memory management features.

#6

In your case, do you get correct results in the cross-table? Please note that I have updated the example.
edit: I have tried in 4.2.2.1, still no change. Please see above.

#7

I’m having the same problem. I’m using Multiple Operations to display the output of a simulation as a function of combinations of two variables, X and Y, but Calc populates the entire data table with the output from the first combination. In other words, I’m expecting output reflecting [X(1), Y(1)], [X(1), Y(2)], [X(1), Y(3)]…[X(n), Y(n)], but every cell is populated with the output from [X(1), Y(1)]. Is it a problem with a hardcoded limit?

#20

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.