Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Revontulet
 
Posts: n/a
Default Conditional summing with large amounts of data

Hi

I have a workbook contains 3 sheets

"2002": The first is data for 2002. There are about 35,000 rows of data.
Each row has 5 identifying features (ie columns) plus two of actual numbers.

"1995": The second sheet is similar data for 1995. However there are only
about 8,000 records and only 4 identifying factors.

(The identifying factors are pre-defined lists, with between 8 and 33
options available.)

The third sheet is a summary sheet. What I want to do is dynamically return
a 2D table that summarises any combination of that data (including
aggregating and filtering). I have successfully implemented this using the
following formula:

=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D 18)*
(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*
IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B $4&"35393")=$F$4))*
IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B $5&"35393")=$F$5))*
(INDIRECT($D$6&"!J2:J35393")))

D6 contains either 2002 or 1995 (ie the sheet names).
B2:B5 contains the letters of the columns which are being filtered and/or
displayed (dynamically retrieved by simple formulae)
F4:F5 contain possible filters

This formula is copied across a 33 by 33 range (large enough to cover all
possible combinations of rows/columns)

Unfortunately this calculates very slowly - it currently takes about
30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to
recalculate a lot as you need to change other options to set up the table how
you want it.

I have read a few sites on optimising speed of calculations etc, and they
tend to suggest using alternatives to the sum() function but I am not sure
how they apply here given the versatility I am hoping for.

Any advice or suggestions would be very much apprecaited

Regards

Revontulet
  #2   Report Post  
Revontulet
 
Posts: n/a
Default

Bump :)

"Revontulet" wrote:

Hi

I have a workbook contains 3 sheets

"2002": The first is data for 2002. There are about 35,000 rows of data.
Each row has 5 identifying features (ie columns) plus two of actual numbers.

"1995": The second sheet is similar data for 1995. However there are only
about 8,000 records and only 4 identifying factors.

(The identifying factors are pre-defined lists, with between 8 and 33
options available.)

The third sheet is a summary sheet. What I want to do is dynamically return
a 2D table that summarises any combination of that data (including
aggregating and filtering). I have successfully implemented this using the
following formula:

=SUM((INDIRECT($D$6&"!"&$B$3&"2:"&$B$3&"35393")=$D 18)*
(INDIRECT($D$6&"!"&$B$2&"2:"&$B$2&"35393")=F$16)*
IF(ISBLANK($F$4),1,(INDIRECT($D$6&"!"&$B$4&"2:"&$B $4&"35393")=$F$4))*
IF(ISBLANK($F$5),1,(INDIRECT($D$6&"!"&$B$5&"2:"&$B $5&"35393")=$F$5))*
(INDIRECT($D$6&"!J2:J35393")))

D6 contains either 2002 or 1995 (ie the sheet names).
B2:B5 contains the letters of the columns which are being filtered and/or
displayed (dynamically retrieved by simple formulae)
F4:F5 contain possible filters

This formula is copied across a 33 by 33 range (large enough to cover all
possible combinations of rows/columns)

Unfortunately this calculates very slowly - it currently takes about
30s-1min to calculate on a Intel P4 3GHz, with 500Mb Ram, and tends to
recalculate a lot as you need to change other options to set up the table how
you want it.

I have read a few sites on optimising speed of calculations etc, and they
tend to suggest using alternatives to the sum() function but I am not sure
how they apply here given the versatility I am hoping for.

Any advice or suggestions would be very much apprecaited

Regards

Revontulet

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 04:56 PM
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 06:17 AM
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 06:15 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
Help with large amounts of data and formulas Aimoore Excel Worksheet Functions 2 November 19th 04 11:47 PM


All times are GMT +1. The time now is 11:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"