Frequency within a frequency

V

Vick

Hi,
I think I have a simple question here, but unable to figure it out
myself, so seeking help on it. I have some accident records with
column heads as "Acc.MP(mile point)", "Severity Index(some number
betw.1-5)". I know the start and end mile point of the route and I can
find the frequency in 1 mile bins using the frequency function. But I
also want to know for each mile, how many 5's are there.
Heres an illustration of it.
Acc.MP Severity Index
0.32 1
0.5 5
0.8 4
0.95 5
1.23 5

So, for this example the result should look like:
Bin(1 mile) #.of Acc. Severity(5)
1 4 ??
2 1 ??
I unable to find the frequency of 5's for each mile. Any inputs are
appreciated.

Thanks,
Vick
 
C

CLR

Hi Vick..........

You might be interested in splitting your mileage points at the decimals to
give whole numbers for the miles in one column, and then doing a Data >
Subtotals, and get an "average" on the Severity column........this will give
you the "average severity for each mile", rather than just how many 5's
there are there.............ie: a mile that has 6 each "4" severities, and
no 5's, may be harder to navigate than one that has one 5 and the rest
1's...........

just food for thought..........

Vaya con Dios,
Chuck, CABGx3
 
E

Earl Kiosterud

Vick,

First, you can get your mile points rounded or truncated (depending on how
you want to assign them) with something like:

=INT(A2)

Now use a pivot table, using the results above for grouping (row or column),
and the severity also in the same grouping area. Assign the data to do a
count (it will default to a sum, since they're numbers, not text). This
will give you a breakdown of mp's, and count of severities within each mp.
 
Top