Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

Array formulas vs. Pivots

I am attracted to the apparent benefits of array fromulas, and I am interested in substituting them for some of my simple pivots.  I have some very large spreadsheets with over 20 pivots in them.  They take forever to recalc and are 30 mb when done.  I run out of memory, so we wrote VB to close and re-start Excel after half of the pivots are calculated.
I know this shouldn't be in Excel, but it is and I cannot change that.
Will array formulas instead of pivots reduce file size (10,000 rows of source data)?
Will array formulas instead of pivots run faster?
Will array formulas instead of pivots use less RAM and therefore not require re-starting Excel to reclaim memory?
-KIM W.
0
krwennerberg
Asked:
krwennerberg
  • 2
1 Solution
 
WJReidCommented:
hI krwennerberg,

Array formulas use more space than any other type of calculation. They are great in small numbers, but will slow down your spreadsheet even more than pivot tables

Regards,

WJReid
0
 
WJReidCommented:
Hi again krwennerberg,

Is there any way you could use the inbuilt Dbase functions. They can do most of what array formulas can do and much quicker.

Regards,

WJReid
0
 
tommybakCommented:
Hi
If all you pivot tables are based on the same 10000 rows of data, i suspect that you have created 30 independent pivottables. This will give an enormous workbook and slow recalculation down as each individual pivot puts all of the data in a cache in the memory.
You should make 1 (one) pivottable where you mark ALL of your data.
Then the next 29 pivottables should be based on pivottable no #1.
Then excel only have to have all of the data in memory 1 time.
It will also decrease the filesize very much.

regards Tommy
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now