Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I hide rows in Excel 2002 using a formula in a cell? | Excel Discussion (Misc queries) | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Updating rows - Excel Formula or Something Else? | Excel Worksheet Functions | |||
How do I view the actual numeric value of a formula in Excel 2002. | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |