Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DCOUNT Performance
Hi - Looking for performance enhancement advise.
Hi - I am using DCOUNTA to calculate both a total and a pass in a calculation to workout percentage passed, based on several filters in a sheet of data which is 20,000 records. I have a total of 230 DCOUNTA's and all using the same filter names, and each DCOUNTA has 10 columns of 'and' filters and between 2 to 7 rows of 'or' filters. I have a look up that changes one of the filter values in all DCOUNTA's and when I make the selection it takes 1min 34sec to recalculate. Is this normal and expected or is there a better method or syntax ? Typical DCOUNTA is =DCOUNTA('Incident Data'!$A:$Z,1,A5:K9). one filter field has =IF(FixFilter_Name="","",FixFilter_Value) and there are a total of 330 of these. Any ideas on how to reduce the calculation time? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DCOUNT Performance
1 min 34 seconds is not that bad considering you have 20,000 records, if you
were to use array formulas my guess is that it would be slower compared to using a D function. The only thing I could think of that would improve with the exception of using a database and not Excel for something like this would be to use many small formulas and then sum or count the results of those formulas. You could turn off automatic calculation so it doesn't calculate when you change a lookup value then when it is time to refill the coffee manually calculate the workbook. -- Regards, Peo Sjoblom "Paul Dennis" wrote in message ... Hi - Looking for performance enhancement advise. Hi - I am using DCOUNTA to calculate both a total and a pass in a calculation to workout percentage passed, based on several filters in a sheet of data which is 20,000 records. I have a total of 230 DCOUNTA's and all using the same filter names, and each DCOUNTA has 10 columns of 'and' filters and between 2 to 7 rows of 'or' filters. I have a look up that changes one of the filter values in all DCOUNTA's and when I make the selection it takes 1min 34sec to recalculate. Is this normal and expected or is there a better method or syntax ? Typical DCOUNTA is =DCOUNTA('Incident Data'!$A:$Z,1,A5:K9). one filter field has =IF(FixFilter_Name="","",FixFilter_Value) and there are a total of 330 of these. Any ideas on how to reduce the calculation time? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DCOUNT Performance
"Paul Dennis" wrote...
.... I have a total of 230 DCOUNTA's and all using the same filter names, and each DCOUNTA has 10 columns of 'and' filters and between 2 to 7 rows of 'or' filters. .... If there were a fair amount of redundancy, row to row, in your criteria range, there could be some performance gains. Impossible to be more precise without seeing what the full criteria range looks like. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dcount question | Excel Discussion (Misc queries) | |||
DCount help | Excel Worksheet Functions | |||
DCOUNT with different files | Excel Worksheet Functions | |||
dcount with vba | Excel Worksheet Functions | |||
dcount vba | Excel Worksheet Functions |