Efficacy of storing text or referring to text

[WhiteKnight]

I was pondering upon the relative merits of filling cells with repeating text as opposed to referring to single instances of the text in multiple cells.

My structure requires a sequence of 8 pre-defined (up to 15 characters) text cells to be repeated in a column. The pattern is then replicated for hundreds of sets.

Set 1 event 1, event 2, event 3 ~ event 8

Set 2 event 1, event 2, event 3 ~ event 8

Each event has 16 Calculated cells on its row.

Is there a noticeable difference between the text being physically present in the cell as opposed to having the cell series refer to a single instance of the text series? viz. =A$1, =A$2, =A$3 - =A$8 then repeating =A$1, =A$2, =A$3 - =A$8

In terms of volume, with just 2000 rows, the text requires 20000 bytes whereas the formulae characters require only 8000 bytes.

Is there a trade-off between the volume of data in memory and the processing of calculations/referrals?

Is a VLOOKUP() more efficient than a direct reference or is the lookup array too small to be significant?

Is the relatively small number of rows from the potential 1048576 rows too small to be significant?

Version: 7.0.5.2 (x64)
Build ID: 64390860c6cd0aca4beafafcfd84613dd9dfb63a
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: sv-SE (en_GB); UI: en-GB
Calc: threaded

8GB Ram, i5 3.4GHz

[eeigor]

Are you interested in research?
Read this book.
For LO Calc, it will also work. There’s a lot of optimization and timing.
The book can be found in the public domain.

[WhiteKnight]

Spot on - Many thanks for your efforts

[erAck]

Identical strings in text cells are implemented as shared strings with the actual string being only one copy, and text cells have less overhead than formula cells and formula cells may need to be recalculated, so in general using literal text is better in this constellation.

I don’t understand your question regarding VLOOKUP() here, lacking context.

[WhiteKnight]

VLOOKUP() was simply a reference to looking the text strings up in a 2x8 array where 1 = “key” to text 1, 2 = “key” to text 2, etc., as opposed to the direct references =A$1, = A$2. I believe I read somewhere that a defined table array with VLOOKUP() is more efficient than individual cell references but I wasn’t convinced a 2x8 array would offer anything significant even if I had remembered correctly. Your explanation concerning the string being only one copy clearly defines the VLOOKUP() potential as a novice’s pipe-dream.