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.
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
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 | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
3 | Win | Place | Lay Win | Lay Place | Lay Win - Risk | |||||||||||||||||||
4 | Profit On Turnover | -1.27% | Profit On Turnover | -4.16% | POT | -2.29% | POT | 2.99% | POT | -0.22% | ||||||||||||||
5 | Bank Growth Ratio | -1032.54% | Bank Growth Ratio | -3384.58% | Bank Growth Ratio | -1865.43% | Bank Growth Ratio | 434.74% | Bank Growth Ratio | -179.73% | ||||||||||||||
6 | Return On Investment | -126.82% | Return On Investment | -415.72% | Return On Investment | -229.13% | Return On Investment | 138.45% | Return On Investment | -22.08% | ||||||||||||||
7 | # of Selections | 81415 | # of Selections | 81415 | # of Selections | 81415 | # of Selections | 81415 | # of Selections | 81415 | ||||||||||||||
8 | # of Selections Per Day | 335.04 | # of Selections Per Day | 335.04 | # of Selections Per Day | 335.04 | # of Selections Per Day | 335.04 | # of Selections Per Day | 335.04 | ||||||||||||||
9 | Average Price | $59.84 | Average Price | $10.14 | Average Price | $59.84 | Average Price | $10.14 | Average Price | $59.84 | ||||||||||||||
10 | Profit & Loss | $103,254 | Profit & Loss | $338,458 | Profit & Loss | $186,543 | Profit & Loss | $112,723 | Profit & Loss | $17,973 | ||||||||||||||
11 | Strike Rate | 11.27% | Strike Rate | 28.79% | Strike Rate | 88.73% | Strike Rate | 70.72% | Strike Rate | 88.81% | ||||||||||||||
12 | Required Strike Rate | 1.67% | Required Strike Rate | 9.86% | Required Strike Rate | 98.33% | Required Strike Rate | 90.14% | Required Strike Rate | 98.33% | ||||||||||||||
13 | Longest Winning Streak | 3 | Longest Winning Streak | 5 | Longest Winning Streak | 39 | Longest Winning Streak | 36 | Longest Winning Streak | 39 | ||||||||||||||
14 | Longest Losing Streak | 39 | Longest Losing Streak | 36 | Longest Losing Streak | 3 | Longest Losing Streak | 5 | Longest Losing Streak | 3 | ||||||||||||||
15 | Win : Loss Ratio | 0.1:1 | Win : Loss Ratio | 0.1:1 | Win : Loss Ratio | 13:1 | Win : Loss Ratio | 7.2:1 | Win : Loss Ratio | 13:1 | ||||||||||||||
16 | Max Drawdown | 1724.57% | Max Drawdown | 2287.89% | Max Drawdown | 254.45% | Max Drawdown | 230.71% | Max Drawdown | 188.20% | ||||||||||||||
17 | Pool Impact Value (A/E) | 0.87 | Pool Impact Value (A/E) | 0.98 | Pool Impact Value (A/E) | 1.02 | Pool Impact Value (A/E) | 1.02 | Pool Impact Value (A/E) | 1.02 | ||||||||||||||
18 | Chi2 | 0.00% | Chi2 | 0.00% | Chi2 | 0.00% | Chi2 | 0.00% | Chi2 | 0.00% | ||||||||||||||
rc-vdw-place-adapted-2021-01-01 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E4,W4,N4,I4 | E4 | =E10/(E7*100) |
E5,W5,N5,I5 | E5 | =E10/10000 |
E6,W6,R6,N6,I6 | E6 | =E10/E7 |
E7 | E7 | =SUBTOTAL(103,AC22:AC1048576) |
E8,W8,R8,N8,I8 | E8 | =E7/243 |
E9 | E9 | =AGGREGATE(1,5,$Y$21:Y200004) |
E10 | E10 | =SUBTOTAL(109,AD22:AD1048576) |
E11 | E11 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AD22:AD1048576,ROW(AD22:AD1048576)-MIN(ROW(AD22:AD1048576)),,1))*(AD22:AD1048576>0))/E7 |
E12,I12 | E12 | =1/E9 |
E13 | E13 | =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))))) |
E14 | E14 | =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,I15 | E15 | =ROUND(E13/E14,1)&":"&ROUND(E13/E13,1) |
E16 | E16 | =AGGREGATE(16,7,$CU$22:CU300000,1) |
E17 | E17 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AD22:AD1048576,ROW(AD22:AD1048576)-MIN(ROW(AD22:AD1048576)),,1))*(AD22:AD1048576>=0))/SUBTOTAL(109,V22:V1048576) |
E18 | E18 | =CHITEST(DN10:DN11,DN8:DN9) |
I7 | I7 | =SUBTOTAL(103,AC22:AC1048576) |
I9 | I9 | =AGGREGATE(1,5,$Z$21:Z200004) |
I10 | I10 | =SUBTOTAL(109,AE22:AE1048576) |
I11 | I11 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-MIN(ROW(AE22:AE1048576)),,1))*(AE22:AE1048576>0))/I7 |
I13 | I13 | =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))))) |
I14 | I14 | =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))))) |
I16 | I16 | =AGGREGATE(4,5,$CY$22:CY130000) |
I17 | I17 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AE22:AE1048576,ROW(AE22:AE1048576)-MIN(ROW(AE22:AE1048576)),,1))*(AE22:AE1048576>=0))/SUBTOTAL(109,AA22:AA1048576) |
I18 | I18 | =CHITEST(DQ10:DQ11,DQ8:DQ9) |
N7 | N7 | =SUBTOTAL(103,AC22:AC1048576) |
N9 | N9 | =AGGREGATE(1,5,$Y$21:Y200004) |
N10 | N10 | =SUBTOTAL(109,AF22:AF1048576) |
N11 | N11 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AF22:AF1048576,ROW(AF22:AF1048576)-MIN(ROW(AF22:AF1048576)),,1))*(AF22:AF1048576>0))/N7 |
N12,W12,R12 | N12 | =1-(1/N9) |
N13 | N13 | =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))))) |
N14 | N14 | =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))))) |
N16 | N16 | =AGGREGATE(4,5,$DC$22:DC128522) |
N17 | N17 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AF22:AF1048576,ROW(AF22:AF1048576)-MIN(ROW(AF22:AF1048576)),,1))*(AF22:AF1048576>0))/SUBTOTAL(109,W22:W1048576) |
N18 | N18 | =CHITEST(DN16:DN17,DN14:DN15) |
R7 | R7 | =SUBTOTAL(103,AC22:AC1048576) |
R9 | R9 | =AGGREGATE(1,5,$Z$21:Z200004) |
R10 | R10 | =SUBTOTAL(109,AG22:AG1048576) |
R11 | R11 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-MIN(ROW(AG22:AG1048576)),,1))*(AG22:AG1048576>0))/R7 |
R13 | R13 | =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))))) |
R14 | R14 | =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))))) |
R16 | R16 | =AGGREGATE(4,5,$DG$22:DG128522) |
R17 | R17 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AG22:AG1048576,ROW(AG22:AG1048576)-MIN(ROW(AG22:AG1048576)),,1))*(AG22:AG1048576>=0))/SUBTOTAL(109,AB22:AB1048576) |
R18 | R18 | =CHITEST(DQ16:DQ17,DQ14:DQ15) |
W7 | W7 | =SUBTOTAL(103,AC22:AC1048576) |
W9 | W9 | =AGGREGATE(1,5,$Y$21:Y200004) |
W10 | W10 | =SUBTOTAL(109,AH22:AH1048576) |
W11 | W11 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AH22:AH1048576,ROW(AH22:AH1048576)-MIN(ROW(AH22:AH1048576)),,1))*(AH22:AH1048576>0))/W7 |
W13 | W13 | =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))))) |
W14 | W14 | =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))))) |
W16 | W16 | =AGGREGATE(4,5,$DK$22:DK130000) |
W17 | W17 | =SUMPRODUCT(SUBTOTAL(103,OFFSET(AH22:AH1048576,ROW(AH22:AH1048576)-MIN(ROW(AH22:AH1048576)),,1))*(AH22:AH1048576>0))/SUBTOTAL(109,W22:W1048576) |
W18 | W18 | =CHITEST(DN12:DN13,DN10:DN11) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
W11 | Expression | =W11<=W12 | text | NO |
W11 | Expression | =W11>W12 | text | NO |
R11 | Expression | =R11>R12 | text | NO |
R11 | Expression | =R11<=R12 | text | NO |
N11 | Expression | =N11>N12 | text | NO |
N11 | Expression | =N11<=N12 | text | NO |
I11 | Expression | =I11>I12 | text | NO |
I11 | Expression | =I11<=I12 | text | NO |
E11 | Expression | =E11>E12 | text | NO |
E11 | Expression | =E11<=E12 | text | NO |
W17 | Cell Value | >1 | text | NO |
W17 | Cell Value | <1 | text | NO |
R17 | Cell Value | >1 | text | NO |
R17 | Cell Value | <1 | text | NO |
N17 | Cell Value | >1 | text | NO |
N17 | Cell Value | <1 | text | NO |
I17 | Cell Value | >1 | text | NO |
I17 | Cell Value | <1 | text | NO |
E17 | Cell Value | >1 | text | NO |
E17 | Cell Value | <1 | text | NO |
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