Data analysis question
Data analysis question
(OP)
I'm trying to analyze some crash data for our county highways.
The goal is to find specific things causing crashes that can be addressed by changing the road design. For example, if "traffic control device disregarded" is over-represented at a specific location, maybe the stop sign is in the wrong place.
I have some data like this:
Case # Vehicle Contributing factor
11 1 Alcohol
11 1 Glare
25 1 Unsafe speed
25 1 not applicable
25 2 Traffic control device disregarded
25 2 Tire failure/inadequate
Each case number represents an individual crash. Each case has two contributing factors per vehicle involved, which means in practical terms, each case number has between 2 and 10 (usually 2-4) contributing factors.
For each of the 45 different factors, I need the percentage of case numbers that have that contributing factor. For example, 15% of cases involve speeding, 18% involve slippery pavement, etc. Also, I don't want to double-count cases where the same factor was attributed to both vehicles.
Any suggestions?
If your suggestion is, "You need to learn Access," my reply is, "Yes, that's true, but I don't have time."
Thanks!
The goal is to find specific things causing crashes that can be addressed by changing the road design. For example, if "traffic control device disregarded" is over-represented at a specific location, maybe the stop sign is in the wrong place.
I have some data like this:
Case # Vehicle Contributing factor
11 1 Alcohol
11 1 Glare
25 1 Unsafe speed
25 1 not applicable
25 2 Traffic control device disregarded
25 2 Tire failure/inadequate
Each case number represents an individual crash. Each case has two contributing factors per vehicle involved, which means in practical terms, each case number has between 2 and 10 (usually 2-4) contributing factors.
For each of the 45 different factors, I need the percentage of case numbers that have that contributing factor. For example, 15% of cases involve speeding, 18% involve slippery pavement, etc. Also, I don't want to double-count cases where the same factor was attributed to both vehicles.
Any suggestions?
If your suggestion is, "You need to learn Access," my reply is, "Yes, that's true, but I don't have time."
Thanks!
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
"I'm searching for the questions, so my answers will make sense." - Stephen Brust





RE: Data analysis question
Sum everything up at the bottom and divide by the total.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Data analysis question
Unfortunately, I have multiple rows for each crash, and one column for contributing factors.
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
"I'm searching for the questions, so my answers will make sense." - Stephen Brust
RE: Data analysis question
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Data analysis question
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
"I'm searching for the questions, so my answers will make sense." - Stephen Brust
RE: Data analysis question
RE: Data analysis question
Assuming that you want to do this all within the worksheet, and not use VBA or anything like that, perhaps, on a second sheet in the workbook, you can create it so that it lists the case number followed by whether or not the contributing factor applies to that case or not, and from there do something like what IRStuff suggests.
A way to do that is by using Countifs statements. For example:
IF(COUNTIFS(Cont. Factor List,Cont. Factor1,Case list,Case #)>0,"yes","no")
this basically says that count the number of times that: the list of the contributing factor has factor 1 AND the corresponding case list equals the case number. Then, if this number is >0 (basically it will equal 1 if it applies to 1 vehicle or 2 if it applies to 2 vehicles) then say "yes" in the cell.
I've wrote up a quick excel file and attached it to demonstrate this.
I hope this helps!
RE: Data analysis question
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
RE: Data analysis question
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Data analysis question
Basically intead of using COUNTIFS it makes use of multiplying two booleans together. So, if both columns aren't what you want, the multiplication will be zero (and it won't count it).
The only trick is that in sheet 2, if you change something inside a cell, you must hit Ctrl+Alt+Enter in it before dragging it across or down, since it is technically an array.
Good Luck!
RE: Data analysis question
RE: Data analysis question
In Access it would be trivial. In Excel, it is going to require a bunch of really esoteric stuff. If I had to do this in Excel I would use VBA so I could loop within each case number.
David
RE: Data analysis question
Dik
RE: Data analysis question
-- MechEng2005