Faster way to work with large data?

[ajocius1]

I have list of data with 250 K lines. That data is formatted as table and I regularly update it from another data source. I do the calculation using SUMPRODUCT function to calculate values that I need. Then I copy that cell to 170 cells (10 lines, 17 columns) so that I can see result for every entity and month that I need.

Formula uses several fields to check:

=SUMPRODUCT((Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3)*(Table2[Time to complete]))/SUMPRODUCT((Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3))

I would like to expand list of KPI's that I can calculate based on same data sheet, but it is already is taking 15-20 sec to recalculate. I have added macros to enable and disable calculation for that result sheet, but still it is time consuming to use it.

I was thinking perhaps there is smarter way of working? Tried Power Query, that helps to import and do simple calculations on source data, but then it returns data into excel sheet Table , so I assume calculations will take the same time. Any thoughts on how can I make my work faster?

 

[JEC]

Use a macro or use a lot of helpercolumns, which allows you to use simpler (faster)functions

 

[Fluff]

May depend on what version of Excel you are using.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ajocius1]

Use a macro or use a lot of helpercolumns, which allows you to use simpler (faster)functions

Will macro be faster than SUMPRODUCT formula?

 

[Fluff]

Thanks for that.
You could use the FILTER function, which should be faster that SUMPRODUCT which should be faster.

 

[ajocius1]

Thanks for that.
You could use the FILTER function, which should be faster that SUMPRODUCT which should be faster.

I come accross that Counta(Filter...) function returns 1, when it there is zero records meeting criteria, then I turned to Sumproduct. Thinking of using separate sheets for every KPI and disable/enable calculation for every sheet by auto_macro triggered when activating sheet. Does anybody know if PowerQuery, PowerPivot would help somehow or same "engine" is behind calculations?

 

[Fluff]

I come accross that Counta(Filter...) function returns 1

Which is why you shouldn't use that construct.

 

[ajocius1]

Which is why you shouldn't use that construct.

, so what construct can I use with Filter to replace Sumproduct function? I count records that meet criteria.

 

[Fluff]

How about

=SUM(FILTER(Table2[Time to complete],(Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3),0))/COUNT(FILTER(ROW(Table2[Time to complete]),(Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3)))

or if you are trying to get the average

=AVERAGE(FILTER(Table2[Time to complete],(Table2[WorkOrder.LegalEntity]=$B6)*(Table2[YearComp]=C$4)*(Table2[MonthCompl]=C$3),0))

 

[ajocius1]

or if you are trying to get the average

This one was considerably (at least twice) faster, thank you