Excel formula for summing using figures from adjoining rows
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 |
Excel formula for summing using figures from adjoining rows
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 Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... 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 |
Excel formula for summing using figures from adjoining rows
.. What I need to do is find the 10-mile stretch of
road with the most accidents 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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
Excel formula for summing using figures from adjoining rows
A sample construct for the earlier suggestions is available at:
http://www.savefile.com/files/1404806 Analysing accidents over 10 mile stretches.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
All times are GMT +1. The time now is 09:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com