The Traffic Accident Reconstruction Origin -Article-


[Home] [ARnews] [Contents] [Classified] [Advertisers] [Approach Angles] [E-mail Directory] [Feedback] [Organizations] [Reference Library]

Review of Statistical Notation and Excel's Keystroke Statistics

This article will serve as a refresher and introduction for the motivated, of statistical terms and procedures necessary to calculate uncertainty.

Mean

Recall that statistics carries its own notation. In order to calculate an average (also called a mean) we add all the values then divide by the count of the number of values.

The statistical notation for this process is expressed as,

Here the Sigma () term is read as the Sum of all tests from i (the first test) to n (the last test). 1/n is simply the division by the number of tests. is read as X bar and is statistical notation for the mean (or average).

Return to Uncertainty Article

 

Calculating Standard Deviation

Most calculators have the ability to calculate Standard Deviation. Two alternative methods are offered to the reader:

1) A step by step numerical approach

2) A point and click spreadsheet method using Microsoft Excel


A Step by Step Numerical Approach to Standard Deviation

 

Standard Deviation is expressed as

 

Examining individual terms:

= Standard Deviation

= The mean or average (see mean above)

= The result of the ith test (where i = 1, 2, 3, … to (the last test))

= the sum of from i to

= the number of tests

= a factor to multiply by, it is actually a division by

 

With these terms defined we will calculate the standard drviation of officer A's drag sled tests as stated in the article. Those values are reproduced here for convenience.

 

Force

Officer A

22.0

23.0

24.0

24.0

25.0

25.0

26.0

26.0

27.0

28.0

 

 

The table below contains the difference , and the difference squared for each of the pull results of his sample.

 

 

Test

Number

Test Results

Difference between

Average and Test

Difference

Squared

1

22.0

22.0 - 25.0 = -3.0

9.0

2

23.0

23.0 - 25.0 = -2.0

4.0

3

24.0

24.0 - 25.0 = -1.0

1.0

4

24.0

24.0 - 25.0 = -1.0

1.0

5

25.0

25.0 - 25.0 = 0.0

0

6

25.0

25.0 - 25.0 = 0.0

0

7

26.0

26.0 - 25.0 = 1.0

1.0

8

26.0

26.0 - 25.0 = 1.0

1.0

9

27.0

27.0 - 25.0 = 2.0

4.0

10

28.0

28.0 - 25.0 = 3.0

9.0

 

Sum = 250.0

Average = 25.0

Sum of Difference Squared = 30.0

 

Next divide the sum of the difference squared by n-1, or 9.

And finally find the square root of the sum of the differences squared divided by n-1.

So by this example we have introduced the notation and calculated the standard deviation step by step.

Return to Uncertainty Article



Finding Standard Deviation with Microsoft Excel

Microsoft Excel is a powerful mathematical tool. To find standard deviation with Excel. follow these steps.

Step 1: Open a new workbook and type the test values into column A as illustrated in Figure 1 below.

 

 

FIGURE 1

Step 2: Next under the Tools Menu select the Data Analysis option. A window will open offering analysis tools. From this list select Descriptive Statistics . An additional Descriptive Statistics window will open as seen in Figure 2

 

 

Figure 2

Step 3: In the Descriptive Statistics window select the gadget in the Input Range box. This will allow you to select cells A2:A11. Before leaving this window check the radio boxes naming a new Worksheet (in this case called Stats or Statistics) and check the radio box Summary Statistics.

Click OK. Figure 3 results

Figure 3

Step 4: In Figure 3 we see numbers that should appear familiar. The mean is reported as 25. We calculated this average above. The standard deviation is reported as 1.825741. This of course is the same value calculated in the example above. Other values have meaning but they are not necessary for this discussion of uncertainty.

This is just one example how Microsoft Excel can assist the reconstructionist.

Return to Uncertainty Article

Copyright ©



[Home] [ARnews] [Contents] [Classified] [Advertisers] [Approach Angles] [E-mail Directory] [Feedback] [Organizations] [Reference Library]