Is there a code avavailable that shows how long each formula takes to calculate?
That way I could pinpoint where the problem is that my sheet calculates so slowly and I could adjust the formula.
Thanks
More file details.
Which Operating System
and LibO Version.
Was the file created in LibO?
No such code.
But you may put a NOW() at the beginning and the end of a long column (it’s important, since columns are the primary elements of Calc, not rows) of dragged formula copies. Then after the bottom NOW(), you may put a subtraction of the first from the last, and see the time elapsed.
Putting parts of your calculations into such columns, you may find out eventually which take long.
3 Likes
Windows 10
I was using 7.1.??
I installed 7.4.6 which improved it a lot, but still very slow at times.
File gets exchanged between Excel and LibreOffice (different computers), so it is saved in .xls I don’t recall where it was originally created.
I suspect there are a handful of formulas that are causing the problem, but I hardly know where to start searching.
It is 18 MB size
If this hard labor was ahead of me, then I would first use Search&Replace in formulas with Regular Expressions turned on - I would find all cells with [A-Z]:[A-Z] This is a “lazy form” of the entire column - usually they make the calculation very difficult. Especially if used in VLOOKUP() or MATCH() with exact search. Simply replacing F:F with F1:F10000 greatly improves performance. I can’t give other recommendations, I don’t know what exactly and how your spreadsheet counts
1 Like
There aren’t any of those. I wondered if somehow there is an external link that shouldn’t be there but not sure.
Or something else that is bloating the file. I think the file size should be 1/3 of what it is.
I can’t upload the file… Probably too large?
Yes, 18 MB size too big… What about upload to dropbox (or any other) and put link here?
Well, when I saw a lot of multi-colored cells, I immediately went to Format-Conditional Formatting-Manager:
I don’t know why there are so many of them, but I know for sure that each of them will be calculated every time the image on the screen changes … Is this really necessary?
1 Like
A lot of the colored cells are for easier readability of the printout. Is straight formatting as bad as conditional formatting?
The conditional formatting is mostly in column HO used for the current week as a visual reminder if the inventory is too low. But isn’t getting deleted as the weeks go on. Is there a better method?
Not ready to answer - I’m still researching your spreadsheet. Yes, your complaints about speed are justified - it’s really unbearably slow! Abundant Conditional Formatting is not the only drawback I found: a lot of decorations (eight-pointed stars) - more than a thousand, a rather wasteful way to store the costs of the ingredients by week (usually it is enough to know the date from which the new price is effective and its value), difficult to read and understand (and therefore calculate) formulas in 'Feed Orders'.HK:HL… I’m still trying to find the main source of the problem, but it seems that a whole complex of different reasons are to blame for the slow recalculation and display.
1 Like
“death by a thousand cuts”
1 Like
[quote=“JohnSUN, post:11, topic:91835”]
a rather wasteful way to store the costs of the ingredients by week (usually it is enough to know the date from which the new price is effective and its value), [/quote]
That was another place i didn’t know how the proper method is. I need the new prices to show on current week, without changing the price on former weeks when the price was different. Do you know an example of a better method?
difficult to read and understand (and therefore calculate) formulas in 'Feed Orders'.HK:HL…
HL is summing estimated usage
Once the exact weights are known, HK sums actual usage which overrides HL in HO and HP
Maybe from copy and paste.
It took me a while to understand it. Thanks.
I’d put it in the same column, so to A1 and A1048576. Depending on configurations, different columns can be evaluated in parallel.
1 Like
I’m not sure why Dad put them in. Guess he thought it would spruce it up? Is there a way to search for the shapes so I could delete them?
With F5, the Navigator.
EDIT:
- Set Zoom level to 20%
- Choose menu
View-Toolbars-Drawing - Select the
Selecttool in the Drawing toolbar - Drag to select, Delete
- Pane the spreadsheet, and repeat from step 4
Note: This will delete all objects, including lines and ellipses.
The Navigator will show how many objects remain in the spreadsheet.
Maybe (not sure) it can be done by macro: to choose and delete each 8-Point Star.
.
EDIT 2:
If do you Select all cells with content (A1:IW1716), Cut, and Paste Special without Objects, all shapes will be removed (lines and ellipses included).
This way the file size reduces in 1576 KB.