Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format of data tables in charts | Charts and Charting in Excel | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Help with large amounts of data and formulas | Excel Worksheet Functions |