The late Benjamin Disraeli, British Prime Minister, said, “There are three kinds of lies: lies, damned lies and statistics.” This point is arguable, but it is inevitable that, at some point, you will be called on to demonstrate how effective or productive your department is with numbers. Statistics can get complex very quickly, and in the examples we’ll use here, the math isn’t going to go beyond simple arithmetic.
Excel is capable of performing some very complex mathematical functions (329 of them in Excel 2003), but we’re going to keep ours simple, and instead of delving into the more involved math operations that Excel can do, we’ll stick to its facility for keeping data well-ordered and accessible.
The simple crime statistics tracking workbook that we developed last month contained worksheets for three years’ worth of data, expressed as the number of Part I offenses that occurred in each of four reporting zones. The data was broken down into reporting zones because, later on, one might be asked to show a community group how its part of town compared with others, and with the city as a whole. However, for gross reporting purposes, we will need to sum the number of crimes and convert those into a crime rate for the entire city.
Crime rates are expressed as the number of crimes per 100,000 population. These are used by the Uniform Crime Reports folks to create a standard by which communities of dissimilar populations can be compared. In any community, there will be a fragment of the population that is predisposed to commit crimes, and it follows that in communities with larger populations, there will be more of these people, and more crimes. What is harder to determine is whether the proportion of criminals in one community exceeds that of another, and whether someone is more likely to be a victim of crime in one place or another. This is the basis for crime rates.
The formula for calculating a crime rate is fairly simple. It is the number of crimes in the measuring period (typically a year), divided by the population of the area in which the crimes occurred, and the result multiplied by 100,000. Expressed in a formula, it looks like this:
(Number of crimes /Population) x 100,000 = Crime Rate.
If a community of 8,000 people experiences 386 Part I offenses in a year, then 386/8000 = .0.04825 x 100,000 = 4825. Nationally, the crime rate for 2001 was 4160.5, so that community would compare unfavorably in national terms. A more meaningful comparison might be with a nearby community with a similar demographic and economic base, and it is here that crime rate statistics can be more useful.
In the example from our last column, we broke out Part I Index Crimes into four reporting zones, but did not total them. We also didn’t include any data for population of each reporting zone or the community as a whole. To do this, we need to add a few more headings or labels to make our worksheets consistent. If you select all of the sheets, then the labels need only be created once, and they will appear on every sheet. We’re also going to insert a new sheet and call it “Summary” to get year-by-year comparisons (Figure 1).
I can keep the three data worksheets selected (note that the “Summary” worksheet is not selected), and enter all my formulas only once. To get the total number of homicides for all reporting zones, click in cell F4, and create a sum formula by typing =SUM(b4:e4).
It doesn’t matter whether you use upper or lower case in these formulas. This formula tells Excel, “Sum all of the values contained in Cells B4 through E4.” In this case, a zero appears in Cell F4 when the formula has been typed in, because there weren’t any reported homicides in our example community that year. To save having to enter that formula for each of the other rows and crime categories, click on cell F4 again, and then on the Copy icon, or on the Edit|Copy item in the Menu Bar. This copies that formula to the clipboard, and Cell F4 will have an animated highlight around it.
Now, click on Cell F5, and drag the cursor down to Cell F15. All of the cells in between will be selected. When you hit the Enter key, the formula in Cell F4 will be copied to the others, and the references changed so that the sum in Row 5 reflects only the values in Row 5, and so on (Figure 2). To get rid of those untidy zeros in the rows that have only labels, click on the cells containing them, and hit the Delete key. Don’t do this with Cell F15, as we’re going to put some numbers in that row for the program to chew on.
There is another, simpler, way of inserting a SUM formula (and a few other commonly used formulas, as well). This is such a commonly used function that Excel provides an entry in the toolbar just for it. Click in Cell B15, and then on the icon in the toolbar that looks like a Greek letter Sigma (Σ – the icon on your display may not be in the same position as it is in the illustration). A dotted box will appear around Cells B10 through B14, as Excel guesses that you want to sum the values in those cells.
If you were to hit the Enter key, it would do just that. Instead, place your cursor on a corner of the dotted box, and the cursor will turn into a double-headed arrow. Drag that arrow to extend the outline of the box to encompass Cells B4 through B13, and hit Enter (Figure 3). The formula should appear in the formula bar at the top of the display, and in Cell B15. Use the copy and paste command as before to copy the formula to Cells C15 through E15. Now that there are values in Row 15, the total number of index crimes appears in Cell F15.
To get the crime rate, we enter the population in Cell B16 (in this case, 58725), and in Cell B19, construct the formula =(F15/B17)*100000 meaning “first calculate the value of the number in Cell F15 divided by the number in Cell B17, then take that value and multiply it by 100,000.” The parentheses around the first calculation tell the program to perform that operation first. The result, 5050.66, appears in Cell B19.
Because the population for the city may change from year to year, each worksheet may have a different value in Cell B17. Keeping that value current will yield the most accurate statistics. It would also be possible to calculate a crime rate for each of the reporting zones, providing the population of each was known. Next time, we’ll use these results to get values on our summary page.
Tim Dees is a former officer who writes and consults about applications of technology in law enforcement. He can be reached at (509) 585-6704 or by e-mail at firstname.lastname@example.org.