LO Gets Into An Unrecoverable "Doom Loop" - LET Function Self Ref?

[Clearwell_T]

Hi All (especially @mikekaganski),

Wondering if someone can help with a bug situatiuon I’m trying to troubleshoot.

I’ve been using a hell of a lot of LET functions in my spreadsheet in the pursuit of maximum speed and efficiency (ie: scan a large list only once, instead of multiple times inside a formula ie: several MATCH iterations).

The problem I keep running into is, I get what I call these “Doom Loops” where something (haven’t been able to nail it down yet) will upset something else (haven’t been able to nail it down yet: assuming internal LET behaviour) and all my calculated output cells turn into an unrecoverable VALUE! error.

That’s OK, coz at least I’ve got myself an experimental error condition I can use to figure out where the problem lies. The strange thing I can’t get my head around though, and it happened to my tester chap yesterday is, I can close the sheet and re-open it, and the condition persists. It’s not until I quit LibreOffice completely will the condition clear. AND my tester man says even a quit out didn’t clear his version of the sheet yesterday! I have to trust he knows what he did and is reporting it faithfully.

The other odd thing was, when I got his misbehaving file back and opened it on my machine, it loaded up and ran just fine, and then when I sent it back to him, it also ran fine again.

So my question to someone like Mike who knows the inner workings of LO is, are there caches or similar that hold info over between different files being opened and/or are there preferences or some such that can get modified by opening a file from another user’s machine or similar?

I hope I’ve explained something that even vaguely makes any sense.

Thanks to everyone here for the always helpful help.

Regards.

CT

[Clearwell_T]

I’m starting to wonder if the problem I’m having is related to some kind of internal memory overload, possibly related to the number of Undo’s?? LET caching?

Is that possible?

My attached screenshot shows the state of my sheet right after I made a simple delete of a number in a dashboard cell, that at the time, was not even controlling anything.

It was just a layout placeholder I was playing about with, so I don’t see how it could force any formula to “panic”, but that seems to be what happens randomly all the time.

I’m confused

Help!

CT

[Lupp]

What do you think the pasted screenshot could help?
a) This or that
b) Nothing at all
My choice: b)
Attach an example document (.ods) showing the issue.
If it is triggered by a specific user interaction, attach it in working state and describe the action precisely.

[Villeroy]

Looks like https://ask.libreoffice.org/uploads/short-url/kTwvqt8WF9rYKu8Md3kLDeDIy8d.ods
The usual spreadsheet madness.

[Clearwell_T]

Hi @Lupp,

@Lupp

b) Nothing at all
My choice: b)

Sorry about that. I’m slightly time constrained due to looking after my wife who’s unwell, so I apologise for asking a question with only half the bloody information attached!

One thing it would help you to know is that, I’m a totally inexperienced newbie at this, so sorry if my questions are very basic.

As I was in a hurry, I’d just wondered if someone with experience might take one look at my screenshot and cut straight to the heart of the matter and say “oh, that’s the well known old blah, blah, blah problem” and I could save everyone including myself a load of hassle.

Obviously it’s more involved than that, so I have uploaded a copy of my file here:

Untitled transfer

The sheet will work fine for a while, then some seemingly inoccuous change will trigger what you see in the screenshot. This morning it was simply changing a basic number from “1” to “4” in a cell that was only a dummy placeholder for a layout change, which was not linked to or part of any formula, or the output of any formula. It’s very random.

If you have the time and the know-how to look under the hood and identify where I’ve gone wrong, I very much appreciate your help.

Thanks for the reply.

Regards.

CT

[Clearwell_T]

Hi again @Villeroy,

@Villeroy

The usual spreadsheet madness.

See, I don’t know anything about what you’re talking about here. And I tried to respectfully engage you previously concerning this, but you chose to ignore me.

I get the impression that self-admittedly inexperienced users such as myself, who have missed out on the history of the great “Spreadsheet Madness Wars®” are really annoying to you. My fault entirely no doubt.

But what I don’t get is the “drive-by sarcasm” that your replies seem to embody?

I’ve made it patently clear to everybody here, that I know nothing about spreadsheeting, but obviously that’s a crime. I came here to learn, and instead I got condescension. If this is the state of community engagement surrounding FOSS, I think I’ll just go back to commercial products. At least I’ll get treated with respect.

For the avoidance of any misunderstanding, below is my reply to your last drive-by sneering, in case you care to bother to answer this time.

Have a good day.

Regards.

CT

Hey @Villeroy,

Thanks very much for the reply.

@Villeroy

LIke the vast majority of today’s spreadsheet users, you try to implement a database on spreadsheets.

I’m really intrigued by this comment. I’m totally sure I am one of those people, but only through sheer self-admitted ignorance.

LIke the vast majority of today’s spreadsheet users …

This seems like the most telling part of your statement. Does this mean that there isn’t a suitable product that’s “fit for purpose” out there in the market, or just that, like me, inexperienced users such as myself, assume unattainable results by focusing on the wrong product solution??

try to implement a database on spreadsheets.

What I find most misleading in this scenario as I find it is, why would the default configuration of a spreadsheet include over a million rows and north of 50,000 columns, if that wasn’t intended to lead neophytes such as myself into believing that there was some serious raw capabilty under the hood just itching to be unleashed.

My failing no doubt.

Can you recommend a suitable equivalent database version of what LO does, that I should look into?

Thanks again for your help. Much appreciated.

Regards.

CT

[Lupp]

@Clearwell_T

One thing it would help you to know is that, I’m a totally inexperienced newbie at this, so sorry if my questions are very basic.

What do you mean by “at this”. If you mean “using spreadsheets” there is no way for me to understand how you might have created the apparatus of formulas contained in this monster sheet.

@Clearwell_T

This morning it was simply changing a basic number from “1” to “4” in a cell …

Which cell? In what way was the sheet changed in the respective session before the accident as compared to the shared version?
Though I feel to be “experienced” to some degree with spreadsheets, I surely can’t find a clue to help you. In fact I regret to have commented on your original post.
I hope you may solve your actual problems and your wife will recover soon.

[Clearwell_T]

Hey @Lupp,

Thanks for the reply.

@Lupp

What do you mean by “at this”. If you mean “using spreadsheets” there is no way for me to understand how you might have created the apparatus of formulas contained in this monster sheet.

Look, I’m reasonably technical (I’ve been a working recording engineer and record producer for the past 40 years), and I made my first basic spreadsheet with =A1+B1 back in August of last year.

I then decided back in Febuary this year to make something really challenging in LO as a learning experience, and to help with learning trading, as well as find out the limits of how far the technology available on my humble home machinery could be pushed.

I’ve been using ChatGPT to teach me how to formularise in the sheets and understand as deeply as I can what’s going on under the hood of a spreadsheet, and so it’s taken me over 2 months of non-stop work and pulling my hair out to get to this point.

BUT, what I don’t have any experienced insight into is, have I tried to make LibreOffice do more than it will EVER be able to do?

All I want is for someone who knows the program intimately, to point to where I’ve overstepped the mark, and then I’ll understand that what I’ve built will never work properly. Hence @mikekaganski’s help has been endlessly educational.

When people like Villeroy engage in their (as I called it) “drive-by sniping”, it really pisses me off. I don’t deserve that kind of treatment. I’ve tried to stay humble and grateful to everyone for giving me their valuable time around here.

If there’s some sort of political back story to the uninitiated like me stepping on toes by pushing the technology to do things it’s not designed to do, then Villeroy could have been nicer about it.

@Lupp

Which cell? In what way was the sheet changed in the respective session before the accident as compared to the shared version?

It doesn’t appear to matter what I do. The sheet is pushing some kind of limits, and almost ANYTHING I do at this point will make it throw up “creeping #VALUE! death” one way or the other…

@Lupp

Though I feel to be “experienced” to some degree with spreadsheets, I surely can’t find a clue to help you.

That’s no problem. I appreciate you spending the time here to try and help me.

@Lupp

In fact I regret to have commented on your original post.

What? Why? You simply tried to help me. Just because my sheet is “a monster” as you put it, isn’t a reason to regret trying to help a fellow traveller. I’m sorry you feel this way.

Anyway, I’ve taken up enough of your time. Thanks for the kind words about the wife. She should be fine.

Thanks again Lupp.

Kind regards.

CT

[Clearwell_T]

PS,

@Clearwell_T

All I want is for someone who knows the program intimately, to point to where I’ve overstepped the mark, and then I’ll understand that what I’ve built will never work properly.

Or more hopefully, someone who knows the program inside out, to say something like “if you adjust this parameter in the advanced settings to allocate more memory to the application, you’ll squeeze more performance blah, blah, blah…”

[Lupp]

@Clearwell_T

@Lupp

In fact I regret to have commented on your original post.

What? Why? You simply tried to help me. Just because my sheet is “a monster” as you put it, isn’t a reason to regret trying to help a fellow traveller. I’m sorry you feel this way.

My comment you quoted wasn’t meant offensive. You were polite, and I also intended to be. Regretting was caused by the fact that i had spent time without getting to anything useful for you - or interesting for me. And there are other topics meeting my preferences better. I dislike “overformatted” sheets and extremely long formulas, and I’m not a bit interested in what nowadays is called “trading”.
I also don’t like being a “second try” after an attempt with ChatGPT or any AI. The reasons are more than one and off topic here.
In any case, it deserves respect when someone reaches this way a level in a relatively short time that gives them the courage to embark on a major project.

[Clearwell_T]

Hi Lupp,

@Lupp

My comment you quoted wasn’t meant offensive. You were polite, and I also intended to be. Regretting was caused by the fact that i had spent time without getting to anything useful for you - or interesting for me.

Aahh, OK, I understand now. Sorry, I know English is not your first language, and my German is non-existent!!?!

@Lupp

I dislike “overformatted” sheets and extremely long formulas

Well, we’ve all got diiferent use cases in mind, and all I’ve done is drive for a concept that’s crystal clear in my mind. I’m simply looking for a tool that aids with clarity when extracting useful information. The sheet works beautifully, it seems to me having worked with computers for a long time, that there is pressuire on the memory handling inside LO, and I’ve been trying to find out from someone with the know-how if that’s true.

May I ask what you use your simple sheets for? Do you work to a limit of how busy you let them get?? I’m intrigued how others work.

@Lupp

and I’m not a bit interested in what nowadays is called “trading”.

I had always imagined if I was doing something like star charting every night, as an amateur I’d be thinking about using these kinds of tools, but it seems like they are not quite up to the demands our imaginations often ascribe to them.

@Lupp

I also don’t like being a “second try” after an attempt with ChatGPT or any AI

Yes, I can understand that impulse, but then you did jump in on the other thread and comment about digital assistants:

Since this thread developed to a kind of “general discussion” I feel free to add my 2 cents.
Being 80 and “old-fashioned” possibly, I never tried to have a chat with any A-Eye. However, very few years ago I started to check my texts written in English sometimes using the DeepL.com translator which is AI-based to get them in German (my native language). I’m not always satisfied, but I remember that I was rather repelled as a school boy by the authoritative “Learn English and French!” - and I ask myself if somebody will take the trouble to study foreign languages 20 years from now …

and I took from this that you were in agreement that digital assistants were unavoidable. And look, there is ZERO chance that ChatGPT could have built this sheet. It took my imagination, and HUGE amounts of patience (it just plain gets things massively wrong ALL THE TIME) and concentrated stamina for learning the language of LO that created this thing. I couldn’t have done it on my own either. So what? I’ve just tried to build a tool to use for things I need in my life. So what?

@Lupp

The reasons are more than one and off topic here.

Not really, this is the perfect opportunity to hear valuable opinions I’d say. I’m all ears!!

@Lupp

In any case, it deserves respect when someone reaches this way a level in a relatively short time that gives them the courage to embark on a major project.

Thank you Lupp. If nothing else, I’ve made more of myself through embarking on this project, and I’ve learned a tremendous amount along the way. No harm, no foul.

Cheers.

CT

[Villeroy]

The rightmost blocks contain numbers imported from a database, obviously. All the records are completely filled with numeric values without any gaps. =COUNT(column)-ROWS(column) is always zero.

So, how often do you need to check if any of these values is missing before you do any trivial arithmetic? The database providing these data takes care of this.
Instead of styles, which you use for conditional formatting only, I see 500+ different cell formattings based on style “Default”.
Tables contain narrow blank columns instead of borders, which makes navigating the tables impossible.

Like each and every database on sheets I’ve seen in the past 25 years, this one is just another example why the whole approach is unproductive, simply because this arithmetic tool of the 1980ies was never intended to be used as a database, even though Microsoft invests all the computing power of our modern machines to make it possible anyway.

[Wanderer]

@Clearwell_T

This morning it was simply changing a basic number from “1” to “4” in a cell that was only a dummy placeholder for a layout change, which was not linked to or part of any formula, or the output of any formula. It’s ver random.

With this kind of “random” errors I usually suggest to backup and reset the user-profile. (Do not try to re-install. This will not touch the profile.
.
Currently I don’t use LET(), but use additional columns, wich may be hidden, if I need intermediate values stored and referenced.

@Clearwell_T

why would the default configuration of a spreadsheet include over a million rows and north of 50,000 columns,

?? The other option would be to have Calc, BigCalc, BiggerCalc, MaxRowCalc, MaxColCalc… to be maintained separate… And always somebody would complain on limits.
.
As several people lost data or published wrong, because for example old xls-files didn’t store more than 65000 rows the current behaviour is an improved one. But as we use Calc on computers from RasperryPi, Intel N100 up to very powerful CPUs with lots of RAM there is no common border, what is usable.
.
Also it depends on your formulas. Simple accounting/math is often possible, while more complex vlookup/match/min/max and array-operations fail. I usually try to have the datasets in a database and give Calc reduced versions (by query) for interactive work.
And for some tasks a separate python or C/pascal-program is simply more efficient.

[koyotak]

Have I ended up in an endless palaver that endlessly bites its own tail? What is your party slogan? Where is any deeper meaning buried?

[Clearwell_T]

Hi Koyotak,

@koyotak

Have I ended up in an endless palaver that endlessly bites its own tail? What is your party slogan? Where is any deeper meaning buried?

Are you asking me that?? I don’t understand what your message is?

CT