Excel formula for summing using figures from adjoining rows

[stephentimko]

I have road milepoints and the number of accidents at that milepoint in
format listed below. What I need to do is find the 10-mile stretch of
road with the most accidents. How do I have Excel go through and take a
number from the left column and sum all the accidents within a 10-mile
range?




141 1
142 1
143 1
146 1
147 1
149 1
151 1
155 1
158 1
159 1
161 1
163 1
166 2
167 3
167 1
173 1
175 1
175 1
175 1
177 1
178 1
179 1
180 1
180 1
180 1
182 1
183 1
185 1

 

[Max]

One way ..

Assuming source data in cols A and B from row1 down

Put in C1: =$A$1+ROW(A1)-1
Put in D1: =SUMIF(A:A,C1,B:B)
Put in E1: =SUM(OFFSET(D1,,,10))
Select C1:E1, fill down as far as required

Col C simply lists all milepoints, starting from the milepoint in A1
Col D returns the total accidents at each milepoint in col C
Col E yields the required total accidents for the 10 mile stretch
for each milepoint in col C, viz.:
E1 gives the figure for milepoints 141-150
E2 gives the figure for milepoints 142-151
E2 gives the figure for milepoints 143-152
and so on ..

 

[Max]

.. What I need to do is find the 10-mile stretch of
Extending the earlier construct ...

Put in F1: =IF(E1="","",E1-ROW()/10^10)

Put in G1:
=INDEX(C:C,MATCH(LARGE(F:F,ROW()),F:F,0))&"-"&
INDEX(C:C,MATCH(LARGE(F:F,ROW()),F:F,0))+9

Put in H1: =INDEX(E:E,MATCH(LARGE(F:F,ROW()),F:F,0))

Select F1:H1, fill down

Col F is an arbitrary tie-breaker col for col E
(there's bound to be a lot of ties in the figures in col E)

Col G returns the full descending sort of "10-mile stretches" by total
accidents
Col H returns the total accidents for the "10-mile stretches" listed in col
G

Tied "10 mile stretches", if any, would appear
in the same relative (sequential) order that they are listed in col C

 

[Max]

A sample construct for the earlier suggestions is available at:
http://www.savefile.com/files/1404806
Analysing accidents over 10 mile stretches.xls