SafeSpeed wrote:
I'd like to add fatality risk to the pedestrian incursion spreadsheet.
There's a data tale in this spreadsheet based on Ashton and Mackey 1979:
http://www.safespeed.org.uk/ashton.xlsBut I don't know any easy way of automating the process of reading acros the table data. Suppose a calculated result is 32mph. How can I get to the answer 52%? Obviously I can calculate a straight line approximation between every data point pair, but it's huge - I'd be working on it for a couple of days!
I tried a polynomial approximation with excel's trendlines but the approximation is pretty crap.
I thought of a "three straight lines" approximation, but that's pretty crude as well.
Anyone got any ideas?
You could always try, a) a massive IF statement, b) write a VB function macro, or c) reference a cell number index according to speed.
The last one is the easiest, although I've forgotten how to do it.
But the A&M graph doesn't give you the risk vs speed anyway, as I think I've pointed out to you.
It's a graph of the the percentile of fatalities etc.
So the 52% at 32mph doesn't mean that a pedestrian has a 52% chance of being killed at a 32mph impact speed, rather it means that 52% of the fatalities in their dataset occured at impact speeds of 32mph and below.
So I don't think that the A&M graph would give you anywhere near the true figures.
But other research (I'll scratch out the references, if you like) indicates that the risk of fatality increases as the fourth power of impact speed, serious injury as the third power, and slight injury as the square.
So I'd calculate the fatality probability as follows:
p = (s ^ 4) / (a ^ 4), where s is the impact speed, and a is the impact speed at which a fatal injury is virtually 100% certain. (50mph?)
Regards
Peter