How to speed up a file when autofiltering

H

honkin

Active Member
Joined
Mar 20, 2012
Messages
385
Office Version
  1. 2016
Platform
  1. MacOS
Some of the sheets I have compile annual data and and up being maybe 120,000 rows. Many of them work fine when autofiltering, but am having some difficulty with this one and wonder what might be wrong.

The sheets is only 80,000 rows as it is only September, but when I try to autofilter, it takes an eternity.

2022 Latest Results.xlsb
BCDEFGHIJKLMNOPQRSTUVW
3WinPlaceLay WinLay PlaceLay Win - Risk
4Profit On Turnover-1.27%Profit On Turnover-4.16%POT-2.29%POT2.99%POT-0.22%
5Bank Growth Ratio-1032.54%Bank Growth Ratio-3384.58%Bank Growth Ratio-1865.43%Bank Growth Ratio434.74%Bank Growth Ratio-179.73%
6Return On Investment-126.82%Return On Investment-415.72%Return On Investment-229.13%Return On Investment138.45%Return On Investment-22.08%
7# of Selections81415# of Selections81415# of Selections81415# of Selections81415# of Selections81415
8# of Selections Per Day335.04# of Selections Per Day335.04# of Selections Per Day335.04# of Selections Per Day335.04# of Selections Per Day335.04
9Average Price$59.84Average Price$10.14Average Price$59.84Average Price$10.14Average Price$59.84
10Profit & Loss$103,254Profit & Loss$338,458Profit & Loss$186,543Profit & Loss$112,723Profit & Loss$17,973
11Strike Rate11.27%Strike Rate28.79%Strike Rate88.73%Strike Rate70.72%Strike Rate88.81%
12Required Strike Rate1.67%Required Strike Rate9.86%Required Strike Rate98.33%Required Strike Rate90.14%Required Strike Rate98.33%
13Longest Winning Streak3Longest Winning Streak5Longest Winning Streak39Longest Winning Streak36Longest Winning Streak39
14Longest Losing Streak39Longest Losing Streak36Longest Losing Streak3Longest Losing Streak5Longest Losing Streak3
15Win : Loss Ratio0.1:1Win : Loss Ratio0.1:1Win : Loss Ratio13:1Win : Loss Ratio7.2:1Win : Loss Ratio13:1
16Max Drawdown1724.57%Max Drawdown2287.89%Max Drawdown254.45%Max Drawdown230.71%Max Drawdown188.20%
17Pool Impact Value (A/E)0.87Pool Impact Value (A/E)0.98Pool Impact Value (A/E)1.02Pool Impact Value (A/E)1.02Pool Impact Value (A/E)1.02
18Chi20.00%Chi20.00%Chi20.00%Chi20.00%Chi20.00%
rc-vdw-place-adapted-2021-01-01
Cell Formulas
RangeFormula
E4,W4,N4,I4E4=E10/(E7*100)
E5,W5,N5,I5E5=E10/10000
E6,W6,R6,N6,I6E6=E10/E7
E7E7=SUBTOTAL(103,AC22:AC1048576)
E8,W8,R8,N8,I8E8=E7/243
E9E9=AGGREGATE(1,5,$Y$21:Y200004)
E10E10=SUBTOTAL(109,AD22:AD1048576)
E11E11=SUMPRODUCT(SUBTOTAL(103,OFFSET(AD22:AD1048576,ROW(AD22:AD1048576)-MIN(ROW(AD22:AD1048576)),,1))*(AD22:AD1048576>0))/E7
E12,I12E12=1/E9
E13E13=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576=1,ROW(AC22:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576<>1,ROW(AC22:AC1048576)))))
E14E14=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576<>1,ROW(AC22:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576=1,ROW(AC22:AC1048576)))))
E15,W15,R15,N15,I15E15=ROUND(E13/E14,1)&":"&ROUND(E13/E13,1)
E16E16=AGGREGATE(16,7,$CU$22:CU300000,1)
E17E17=SUMPRODUCT(SUBTOTAL(103,OFFSET(AD22:AD1048576,ROW(AD22:AD1048576)-MIN(ROW(AD22:AD1048576)),,1))*(AD22:AD1048576>=0))/SUBTOTAL(109,V22:V1048576)
E18E18=CHITEST(DN10:DN11,DN8:DN9)
I7I7=SUBTOTAL(103,AC22:AC1048576)
I9I9=AGGREGATE(1,5,$Z$21:Z200004)
I10I10=SUBTOTAL(109,AE22:AE1048576)
I11I11=SUMPRODUCT(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-MIN(ROW(AE22:AE1048576)),,1))*(AE22:AE1048576>0))/I7
I13I13=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-ROW(AE22),0,1))>0,IF(AE22:AE1048576>0,ROW(AE22:AE1048576))),IF(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-ROW(AE22),0,1))>0,IF(AE22:AE1048576<0,ROW(AE22:AE1048576)))))
I14I14=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-ROW(AE22),0,1))>0,IF(AE22:AE1048576<0,ROW(AE22:AE1048576))),IF(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-ROW(AE22),0,1))>0,IF(AE22:AE1048576>0,ROW(AE22:AE1048576)))))
I16I16=AGGREGATE(4,5,$CY$22:CY130000)
I17I17=SUMPRODUCT(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-MIN(ROW(AE22:AE1048576)),,1))*(AE22:AE1048576>=0))/SUBTOTAL(109,AA22:AA1048576)
I18I18=CHITEST(DQ10:DQ11,DQ8:DQ9)
N7N7=SUBTOTAL(103,AC22:AC1048576)
N9N9=AGGREGATE(1,5,$Y$21:Y200004)
N10N10=SUBTOTAL(109,AF22:AF1048576)
N11N11=SUMPRODUCT(SUBTOTAL(103,OFFSET(AF22:AF1048576,ROW(AF22:AF1048576)-MIN(ROW(AF22:AF1048576)),,1))*(AF22:AF1048576>0))/N7
N12,W12,R12N12=1-(1/N9)
N13N13=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576<>1,ROW(AC22:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576=1,ROW(AC22:AC1048576)))))
N14N14=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576=1,ROW(AC22:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576<>1,ROW(AC22:AC1048576)))))
N16N16=AGGREGATE(4,5,$DC$22:DC128522)
N17N17=SUMPRODUCT(SUBTOTAL(103,OFFSET(AF22:AF1048576,ROW(AF22:AF1048576)-MIN(ROW(AF22:AF1048576)),,1))*(AF22:AF1048576>0))/SUBTOTAL(109,W22:W1048576)
N18N18=CHITEST(DN16:DN17,DN14:DN15)
R7R7=SUBTOTAL(103,AC22:AC1048576)
R9R9=AGGREGATE(1,5,$Z$21:Z200004)
R10R10=SUBTOTAL(109,AG22:AG1048576)
R11R11=SUMPRODUCT(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-MIN(ROW(AG22:AG1048576)),,1))*(AG22:AG1048576>0))/R7
R13R13=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-ROW(AG22),0,1))>0,IF(AG22:AG1048576>0,ROW(AG22:AG1048576))),IF(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-ROW(AG22),0,1))>0,IF(AG22:AG1048576<0,ROW(AG22:AG1048576)))))
R14R14=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-ROW(AG22),0,1))>0,IF(AG22:AG1048576<0,ROW(AG22:AG1048576))),IF(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-ROW(AG22),0,1))>0,IF(AG22:AG1048576>0,ROW(AG22:AG1048576)))))
R16R16=AGGREGATE(4,5,$DG$22:DG128522)
R17R17=SUMPRODUCT(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-MIN(ROW(AG22:AG1048576)),,1))*(AG22:AG1048576>=0))/SUBTOTAL(109,AB22:AB1048576)
R18R18=CHITEST(DQ16:DQ17,DQ14:DQ15)
W7W7=SUBTOTAL(103,AC22:AC1048576)
W9W9=AGGREGATE(1,5,$Y$21:Y200004)
W10W10=SUBTOTAL(109,AH22:AH1048576)
W11W11=SUMPRODUCT(SUBTOTAL(103,OFFSET(AH22:AH1048576,ROW(AH22:AH1048576)-MIN(ROW(AH22:AH1048576)),,1))*(AH22:AH1048576>0))/W7
W13W13=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576<>1,ROW(AC22:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576=1,ROW(AC22:AC1048576)))))
W14W14=MAX(FREQUENCY(IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576=1,ROW(AC22:AC1048576))),IF(SUBTOTAL(103,OFFSET(AC22:AC1048576,ROW(AC22:AC1048576)-ROW(AC22),0,1))>0,IF(AC22:AC1048576<>1,ROW(AC22:AC1048576)))))
W16W16=AGGREGATE(4,5,$DK$22:DK130000)
W17W17=SUMPRODUCT(SUBTOTAL(103,OFFSET(AH22:AH1048576,ROW(AH22:AH1048576)-MIN(ROW(AH22:AH1048576)),,1))*(AH22:AH1048576>0))/SUBTOTAL(109,W22:W1048576)
W18W18=CHITEST(DN12:DN13,DN10:DN11)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
W11Expression=W11<=W12textNO
W11Expression=W11>W12textNO
R11Expression=R11>R12textNO
R11Expression=R11<=R12textNO
N11Expression=N11>N12textNO
N11Expression=N11<=N12textNO
I11Expression=I11>I12textNO
I11Expression=I11<=I12textNO
E11Expression=E11>E12textNO
E11Expression=E11<=E12textNO
W17Cell Value>1textNO
W17Cell Value<1textNO
R17Cell Value>1textNO
R17Cell Value<1textNO
N17Cell Value>1textNO
N17Cell Value<1textNO
I17Cell Value>1textNO
I17Cell Value<1textNO
E17Cell Value>1textNO
E17Cell Value<1textNO


Many of the cells showing are fed by calculations of other columns. I know that using OFFSET is a notorious volatile function and possibly some others, but not sure what else to do.

There are also columns further across which calculate things like drawdown etc using the AGGREGATE function.

This particular file is saved as a .xlsb file, which I hoped would be the right format for it. it is currently 51Mb, which is not horrendous.

So what am I doing wrong with this file? Is it the format, the calculations further across totalling equity and drawdown, or a combo of everything?

Having asked that, is there a better way? The sheet is designed to store data and have it be autofiltered, with the data boxes you see changing based on that filtering.

Any help gladly accepted

cheers
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
There are lots of formulas like this:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(AD22:AD1048576,ROW(AD22:AD1048576)-MIN(ROW(AD22:AD1048576)),,1))*(AD22:AD1048576>0))/E7
try to get the filter condition from an auto filtering with OFFSET loop 1 milion rows is never a good idea.
If you can share the file via drop box or gg drive, there might be another solution with VBA .
 
Upvote 0
bebo021999 said:
There are lots of formulas like this:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(AD22:AD1048576,ROW(AD22:AD1048576)-MIN(ROW(AD22:AD1048576)),,1))*(AD22:AD1048576>0))/E7
try to get the filter condition from an auto filtering with OFFSET loop 1 milion rows is never a good idea.
If you can share the file via drop box or gg drive, there might be another solution with VBA .
Click to expand...
Understood bebo021999
Here is the dropbox link 2022 Latest Results.xlsb

cheers
 
Upvote 0
bebo021999 said:
Wow, the 50MB file did not let me open it.
Could you reduce the size by:
- remove irrelative sheets
- delete all SUBTOTAL(3,OFFSET... formula in sheet.
then upload again.
?
Click to expand...
cheers bebo021999

Odd you couldn't open the file, as it is not password protected at all, but you do need to download it, rather than trying to open it from its location.

For my own purposes, I removed all the columns to the right of the main data and created another file. It was easy to use and fast, so it is definitely the columns with the main calculations in them. Each of the 5 models has 4 calculation columns; a Current Equity calculation which is simply AGGREGATE(9,5. The next column is Peak Equity...this is again Aggregate, but 4,5 as I need MAX. The next one is a simply sub traction to give drawdown from the Peak.

There is not a lot of point me supplying the file sans those columns as they are the ones causing the grief and the only ones which really need attending to. The rest of the data appears to be fine.

Here is a link to the download directly https://file.io/oHjCgFTEPoJL

cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria
Replies
3
Views
501
Fluff
Fluff
N
  • Question Question
Please help in vba code
Replies
5
Views
2K
Gindo70
G
K
  • Solved
How to exclude duplicate rows when summing values in columns
Replies
3
Views
735
Alex Blakenburg
A
2
  • Question Question
Combining two subroutines
2
Replies
11
Views
2K
25INF
2

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top