I have a dataset with thousands of rows and therefore need a way to automate a lot of my calculations to speed up literally hours upon hours worth of manual effort.

Unfortunately I am not proficient enough in excel to automate it myself so after some help please.

I have a bunch of numbers on each row, basically signified by 'Dummy' within the 'Type' column.

At the bottom of each of my scores you will see a 'Summary' 'Type'. On this row is where I perform summary calculations.

#1 Instead of having to identify each 'Summary' field in thousands of rows and then autosum J16, J22, etc, I need a way for the formula to sum all rows above a particular 'Summary', relevant for that code and for that date, up until the next 'Summary' (not including the first lot of data which doesn't have a 'Summary' above it. Sounds confusing, let me give an example.

J16 needs to be the sum of J2 to J15. J23 needs to be the sum of J17 to J22. I imagine the best way to calculate this would be to read column 'C' which will always have either the word 'Dummy', or the word 'Summary', and therefore when you get to a summary row (J16), you read all the 'Dummy' rows above (column c), and therefore sum the values for J16. As I mentioned, you will need to find a way to have this work even for the first lot of data, as the first lot of data doesn't have a 'Summary' row above it, it just starts at 'Dummy'.

This way of summing data is relevant for J16, K16, L16, M16, N16, O16, P16

What I want to do is filter 'Type' by 'Summary' within my workbook, use the formula I am hoping someone provides me, and then drag that down all my 'Summary' rows.

Refer to BLUE highlighted fields.

#2. The issue I have with column K is that my Summed total as per #1 is not always a set number of rows. I need a way to automatically divide the averages by the total summed row (eg K2 to K15, divide by K16). As mentioned, I dont know how to do this as the number of rows is never consistent.

What I want to do is filter 'Type' by 'Dummy' within my workbook, use the formula I am hoping someone provides me, and then drag that down all of my 'Dummy rows'

Refer to GREEN highlighted fields.

Help would greatly be appreciated, to manually scroll through thousands of lines and autosum each of these blue highlighted cells would take me forever. Thanks!

Book2 | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||

1 | UID | Date | Type | Ticker | Dummy data | Number #1 | Number # 2 | Number # 3 | Number #1 + Number #3 | Average | Ratio of average to total average | Weighted score #1 | Score #2 | Ratio of score #2 to total score #2 | Score #3 | Score adjusted | ||

2 | 1 | 20/04/2015 | Dummy | ABCD | Dummy | 4 | 14.1 | 307 | 311 | 309 | 14.24% | 44.01 | 56.4 | 16.53% | 12.39 | 2.047354451 | ||

3 | 2 | 20/04/2015 | Dummy | ABCD | Dummy | 12 | 3.7 | 288 | 300 | 294 | 13.55% | 39.84 | 44.4 | 13.01% | 4.56 | 0.593027095 | ||

4 | 3 | 20/04/2015 | Dummy | ABCD | Dummy | 13 | 3 | 141 | 154 | 147.5 | 6.80% | 10.03 | 39 | 11.43% | 28.97 | 3.310574099 | ||

5 | 4 | 20/04/2015 | Dummy | ABCD | Dummy | 27 | 1.3 | 169 | 196 | 182.5 | 8.41% | 15.35 | 35.1 | 10.28% | 19.75 | 2.030921209 | ||

6 | 5 | 20/04/2015 | Dummy | ABCD | Dummy | 22 | 1.5 | 212 | 234 | 223 | 10.28% | 22.92 | 33 | 9.67% | 10.08 | 0.974445484 | ||

7 | 6 | 20/04/2015 | Dummy | ABCD | Dummy | 19 | 1.2 | 265 | 284 | 274.5 | 12.65% | 34.73 | 22.8 | 6.68% | -11.93 | -0.797072781 | ||

8 | 7 | 20/04/2015 | Dummy | ABCD | Dummy | 6 | 4.1 | 57 | 63 | 60 | 2.77% | 1.66 | 24.6 | 7.21% | 22.94 | 1.653499954 | ||

9 | 8 | 20/04/2015 | Dummy | ABCD | Dummy | 7 | 2.1 | 115 | 122 | 118.5 | 5.46% | 6.47 | 14.7 | 4.31% | 8.23 | 0.354360262 | ||

10 | 9 | 20/04/2015 | Dummy | ABCD | Dummy | 6 | 2.3 | 23 | 29 | 26 | 1.20% | 0.31 | 13.8 | 4.04% | 13.49 | 0.545385359 | ||

11 | 10 | 20/04/2015 | Dummy | ABCD | Dummy | 3 | 3.1 | 0 | 3 | 1.5 | 0.07% | 0.00 | 9.3 | 2.72% | 9.30 | 0.253385159 | ||

12 | 11 | 20/04/2015 | Dummy | ABCD | Dummy | 6 | 1.8 | 182 | 188 | 185 | 8.53% | 15.78 | 10.8 | 3.16% | -4.98 | -0.157444074 | ||

13 | 12 | 20/04/2015 | Dummy | ABCD | Dummy | 8 | 1.7 | 287 | 295 | 291 | 13.41% | 39.03 | 13.6 | 3.98% | -25.43 | -1.013424978 | ||

14 | 13 | 20/04/2015 | Dummy | ABCD | Dummy | 8 | 1.6 | 23 | 31 | 27 | 1.24% | 0.34 | 12.8 | 3.75% | 12.46 | 0.467444819 | ||

15 | 14 | 20/04/2015 | Dummy | ABCD | Dummy | 10 | 1.1 | 25 | 35 | 30 | 1.38% | 0.41 | 11 | 3.22% | 10.59 | 0.341156568 | ||

16 | 15 | 20/04/2015 | Summary | ABCD | Dummy | 2169.5 | 100.00% | 230.89 | 341.3 | 100.00% | 110.41 | 10.60361263 | ||||||

17 | 16 | 22/06/2015 | Dummy | EFGH | Dummy | 42 | 2.12 | 75 | 117 | 96 | 0.00 | 89.04 | 89.04 | 0 | ||||

18 | 17 | 22/06/2015 | Dummy | EFGH | Dummy | 24 | 2.31 | 126 | 150 | 138 | 0.00 | 55.44 | 55.44 | 0 | ||||

19 | 18 | 22/06/2015 | Dummy | EFGH | Dummy | 3 | 2.86 | 75 | 78 | 76.5 | 0.00 | 8.58 | 8.58 | 0 | ||||

20 | 19 | 22/06/2015 | Dummy | EFGH | Dummy | 6 | 0.91 | 72 | 78 | 75 | 0.00 | 5.46 | 5.46 | 0 | ||||

21 | 20 | 22/06/2015 | Dummy | EFGH | Dummy | 3 | 2.57 | 84 | 87 | 85.5 | 0.00 | 7.71 | 7.71 | 0 | ||||

22 | 21 | 22/06/2015 | Dummy | EFGH | Dummy | 3 | 1.23 | 120 | 123 | 121.5 | 0.00 | 3.69 | 3.69 | 0 | ||||

23 | 22 | 22/06/2015 | Summary | EFGH | Dummy | 592.5 | 0.00 | 169.92 | 0 | |||||||||

24 | 23 | 22/06/2015 | Dummy | IJKL | Dummy | 5.3 | 12.2 | 265.15 | 270.45 | 267.8 | 0.00 | 64.66 | 64.66 | 0 | ||||

25 | 24 | 22/06/2015 | Dummy | IJKL | Dummy | 4.1 | 9.9 | 281.9 | 286 | 283.95 | 0.00 | 40.59 | 40.59 | 0 | ||||

26 | 25 | 22/06/2015 | Summary | IJKL | Dummy | 551.75 | 0.00 | 105.25 | 0 | |||||||||

MrExcel |

Cell Formulas | ||
---|---|---|

Range | Formula | |

O2:O15,O17:O22,O24:O25 | O2 | =M2-L2 |

P2:P15,P17:P22,P24:P25 | P2 | =O2*N2 |

J16:P16 | O16 | =SUM(O2:O15) |

O23:P23,L23,J23 | O23 | =SUM(O17:O22) |

O26:P26,L26,J26 | O26 | =SUM(O24:O25) |

I2:I15,I24:I25,I17:I22 | I2 | =F2+H2 |

K2:K15,N2:N15 | K2 | =J2/J$16 |

M2:M15,M17:M22,M24:M25 | M2 | =F2*G2 |

J2:J15,J17:J22,J24:J25 | J2 | =AVERAGE(H2:I2) |

L2:L15,L17:L22,L24:L25 | L2 | =J2*K2 |