Is it worth replacing historical formulae with "Paste Special" data

English
#1

I have a spreadsheet with currently 35000 active formulae spread over about 4000 rows x 18 columns.

This sheet grows by 270 rows a month so the weekly utilisation is 56 rows with a daily figure of 8 rows. The Statistics tell me there are 53676 cells in the sheets so I assume the remainder to be unpopulated cells and text cells – each row has a text element.

All the columns are auto filtered and five have conditional 3 colour formatting. 4 of the columns are used as data for charting.

Every month I just extend the range and copy the formula groups into the next month’s “array”.

I’ve observed that the file loads and is active within three seconds on a fresh start and this reduces to 2 seconds when another sheet is open and LO is presumably mostly memory resident.

As the historical data will never change, I was wondering if there is anything to be gained by replacing the formulae in “older” cells with the data resulting from a Paste Special > Values Only.

This could be safely undertaken on a weekly basis or simply at the same time as the monthly range-extending procedure.

Of the 18 columns, 5 always retain the formulae in all cells so the process saving is not exactly proportionally represented by the number of “old” rows being decommissioned.

All thoughts would be greatly appreciated.

#3

You surely have in mind efficiency and storage size.
Basically I would expect an advantageous effect on both. However, if you load (including full recalculation) your sheet within 2.0 s, the formulas may be supposed to be rather simple, and the gain won’t be of much meaning probably.
The actual effect will depend on many specifics of your case, and should be estimated best based on an experiment with a cpopy.

The weekly or monthly “castration” of formulas may be error-prone to some degree. It’s your turn again to judge. Of course, you can’t do things this way in special cases (formulas creating a statistic about contained formulas e.g.) without additional adaptions.

If at all, you should better do it the way @karolus suggested. Otherwise you will convert cells having returned the empty string due to the contained formula (countable as TEXT, answerijng TRUE if asked ISTEXT()) to blank cells (no TEXT)
Formula to Value would preserve the TEXT propertty. (Current behaviour of LibO, probably no long-term guarantee.)

1 Like
#4

That’s another good day - two things learned.
It ignores anything in grouped data that’s hidden and anything filtered out by an auto filter.
Maybe three, because I never even knew of the function beforehand. :thinking: .Thanks @karolus
It’s certainly a nice quick and easy alternative to paste special but are there any thoughts on assessing how much more efficient the processing has become as a result of the change? Or is it something that would only be noticeable if I were to use far more of LO’s capacity?

#5

@Lupp That’s a good call and I hadn’t considered that before I dived into the pool - on a copy.
Fortunately, the structure of my sheet is such that only the “future” events are nulled just so I don’t get lots of zeros in dates I haven’t yet processed. Zero is a valid result so I couldn’t nullify their display.
Still a veritable newbie so no complicated, nested, statistical or array functions - yet. Thanks for your guidance.

#6

As it is, there’s no noticeable delay in the processing so I guess I need to learn how to do more complex things to put LO’s power to better use.

#2

Select all the stuff ‘above’ actual Data and do →→Data→→Calculate→→Formula to Value

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