Solved

Array formulas vs. Pivots

Posted on 2002-07-16
3
583 Views
Last Modified: 2008-03-17
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
Comment
Question by:krwennerberg
  • 2
3 Comments
 
LVL 13

Expert Comment

by:WJReid
ID: 7157279
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
 
LVL 13

Expert Comment

by:WJReid
ID: 7157285
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
 
LVL 1

Accepted Solution

by:
tommybak earned 200 total points
ID: 7166066
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now