Frequency within a frequency

  • Thread starter Thread starter Vick
  • Start date Start date
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
 
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
 
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.
 
Back
Top