List of memory usage with Excel functions

[Johney]

Is there a list somewhere that people know of that shows the relative
memory use of different Excel functions? It would be helpful, for
example, to know whether to use OFFSET or INDEX when the worksheet
calls for thousands of these.

Thanks.

 

[Harlan Grove]

Is there a list somewhere that people know of that shows the relative
memory use of different Excel functions? It would be helpful, for
example, to know whether to use OFFSET or INDEX when the worksheet
calls for thousands of these.

INDEX is almost always a better choice. Dunno about memory usage, but INDEX
is nonvolatile while OFFSET is volatile. That means formulas using OFFSET
recalc each time any action triggers a recalc, even when nothing would have
changed in the range to which OFFSET resolves.

But if you want to find out relative memory usage, enter a few hundred
thousand cells' worth of the same OFFSET or INDIRECT formula, save the file,
close Excel, then bring up Task Manager and reopen each such file
separately. Check the memory usage for the Excel.exe process.

 

[Johney]

Thanks for the advice on how to measure relative efficiencies.