• Injury and Violence Prevention Program

• Just In

• Injury Prevention through the EMS and Trauma Care System

• Injury Data Tables

• InjuryRateAnalyzer

• Publications

• Program Contacts - Technical Assistance

• Related Sites

• Alternative File Formats

• Download PDF and/or Excel Viewers

About InjuryRateAnalyzer

The InjuryRateAnalyzer is a Windows based Excel application for comparing county or EMS region injury rates to Washington state injury rates. Through color coding, it can help identify injury rates either significantly higher or lower than the state rate. This can be done by age group or for all ages combined. Information on how the InjuryRateAnalyzer workbooks were created can be found here.

Download

Years 2000-2004 Aggregated (Excel 2000 files, compressed into ZIP files)

Fatal Injuries

• Fatal injuries by county  (683 KB)
• Fatal injuries by EMS region (374 KB)

Nonfatal Injury Hospitalizations (see Limitations section below)

• Nonfatal injury hospitalizations by county (1,251 KB)
• Nonfatal injury hospitalizations by EMS region (680 KB)

Quick Start

Here are some quick and dirty steps on how to get started:

1. Download and unzip and open an InjuryRateAnalyzer application.
2. Open the InjuryRateAnalyzer application.  If the InjuryRateAnalyzer control box does not appear in the middle of the screen, adjust the Excel security settings so that workbook macros can be run.  (see Set Excel security settings below). Reopen application..
3. The InjuryRateAnalyzer control box will appear in the middle of the screen.
4. Select the county or EMS region of interest using the tabs at the bottom of the worksheet window.
5. Click "Calculate." The rates highlighted in yellow will be lower than the state rate, and the rates highlighted in green will be higher than the state rate.

If you run into problems, or you want more in depth directions, read the Directions section below.

Directions

Set Excel security settings

The macro security settings in Excel must be set in a way that allows your computer to run the computer code in InjuryRateAnalyzer.  To check the macro security settings, first open Excel.  From the menu bar at the top of the Excel window, go to "Tools->Options...".  This causes the Options dialog box to open.  Choose the "Security" tab.  In the dialog box, towards the bottom on the right-hand side press the button "Macro Security..."  In the Security dialog box that opens, under the "Security Level" tab, you should see four options: Very High, High, Medium, and Low.  Choose the medium option.  Click "OK" in the "Security" and then the "Options" dialog boxes.  Excel will now allow the computer code in InjuryRateAnalyzer to run.

Download and unzip an InjuryRateAnalyzer workbook

The InjuryRateAnalyzer workbooks can contain a lot of data, so they are compressed into a zip file.  After downloading an InjuryRateAnalyzer ZIP file, unzip the file.  Typically you can do this by simply double-clicking on the zipped file.  On most Windows based computers, the operating system should offer native support for ZIP files, or the computer should have a program like WinZip or PKZip installed.  If for some reason you cannot unzip the file, you can email the injury data manager at injury.data@doh.wa.gov.  The injury data manager can email you an unzipped, uncompressed InjuryRateAnalyzer file.

Open InjuryRateAnalyzer

Opening an InjuryRateAnalyzer workbook should cause three things to happen:

1. the worksheets containing either the geographic (county or EMS region) rate data will appear,
2. a new menu item "RateAnalyzer" will be created on the right-hand side of the menu bar at the top of the Excel window,
3.  the InjuryRateAnalyzer control box will appear in the center of the worksheet window.

Running InjuryRateAnalyzer

The type of analysis is controlled through a  "Settings" dialog box, accessed from the menu at "RateAnalyzer->Settings".  Clicking the "Calculate" button in the main InjuryRateAnalyzer control box (dialog box) will perform the given analysis.

Explanation of Controls

InjuryRateAnalyzer menu

From the InjuryRateAnalyzer menu item, you can control various aspects of the application.  The most important of which are the settings, accessed by going to "RateAnalyzer->Settings...".  Selecting this menu item will cause the "Settings" dialog box to open. 

Settings Dialog Box

• Analysis Type frame: Choosing the "counterpart of Washington State worksheet" will allow InjuryRateAnalyzer to compare each cell's rate in each county or EMS region to the corresponding rate in the "Washington State" worksheet.  Using this option, you can analyze how a counties or EMS regions rates differ from the statewide rates. 
  
  Choosing the "counterpart in 'All Ages' column on same worksheet" allows InjuryRateAnalyzer to compare each cells rate with the corresponding "All Ages" rate in the same row of the same worksheet.  Using this option, you can analyze differences in rates in age groups compared to the rates for all ages combined.
• Confidence Level frame: This frame allows you to set the confidence level in your analysis.  Setting one of these options will result in InjuryRateAnalyzer only comparing rates that are significantly different to the chosen confidence level from the reference rates, be they in the Washington State worksheet or in the "All Ages" column (see "Analysis Type frame" above for the difference between the two).  Choosing the "None" option will cause the InjuryRateAnalyzer to compare all rates without using confidence levels.
• Analysis Target frame: This options in this frame allow you to specify which sheets should be analyzed, only the visible worksheet or all worksheets.  Depending on the speed of your computer, specifying the "all worksheets" option could result in the application taking an undue amount of time to complete the analysis.  Choosing the "only the visible worksheet" restricts the analysis to the visible worksheet, greatly reducing analysis time.

Hide Rows and Unhide Rows

Since InjuryRateAnalyzer only contains rates when the corresponding count is 5 or greater, many cells do not have data.  Instead, they contain asterisks.  As a result, in many cases entire rows of data do not contain any data whatsoever.  "RateAnalyzer->Hide Rows" causes these rows to be hidden.  "RateAnalyzer->Unhide Rows" will uncover these hidden rows.

Zoom All Pages

Choosing this menu item will open a dialog box that will allow you to zoom all the pages in the workbook to the same magnification.  This is useful to do a quick color analysis of all the counties or EMS regions.  For example, after performing an analysis on all the worksheets in the workbook, you could zoom the pages down to a magnification level of 25%.  Then you would be able to quickly click though all the location tabs at the bottom of the worksheet window in order to get a broad overview of unusual rates.

InjuryRateAnalyzer Control Box

After selecting the appropriate settings from the InjuryRateAnalyzer menu, this dialog box is where you actually perform an analysis on a workbook.  The control box consists of a "Calculate" frame, and a series of 0 or more criteria frames to the right of the "Calculate" frame.

Calculate frame

Pressing the "Calculate" button will cause InjuryRateAnalyzer to perform an analysis based on one or more criteria that are to the right of the button, using settings chosen through the menu item "RateAnalyzer->Settings."

The "Add Criteria" button will add a criteria frame up to a maximum number.  If InjuryRateAnalyzer is already displaying the maximum number of criteria frames possible, this button becomes disabled.

Criteria frame(s)

The heading of each criteria frame is a the name of a color.  Each frame contains a two numbers that you set.  These two number define a rate multiplier interval.  For example, if you wanted to search for rates that are less than the reference rates (in the Washington State worksheet or the "All Ages column"), you would choose an interval form 0 to 1.  In conjunction with the confidence level you set using the menu item "RateAnalyzer->Settings", this will cause InjuryRateAnalyzer to color code all rate's cells that are between 0 and 1 times as great as their respective reference cells.  To find rates that are larger, you could set the interval from 1 to some arbitrarily large number, such as 1000.  This will cause InjuryRateAnalyzer to color cells in which their rates are from 1 to 1000 times as great as the reference rates.

Other Features

Every cell that has a rate contains additional relevant information in the the form of an Excel comment.  The presence of these comments are represented on the worksheets by small red triangles in the upper left-hand corner of each cell.  Placing the mouse curser over any individual red triangle will cause Excel to open a comment box.  These comment boxes contain the following information:

1. the count,
2. the lower 95% confidence limit
3. the upper 95% confidence limit
4. for all locations other than Washington state as a whole, the Washington state rate,
5. for all locations other than Washington State as a whole, the rate for all of Washington state excluding that location (rest of Washington rate).

For most locations, (4) and (5) will very similar, but if a location has a significant portion of the state's population, such as King county, any given Washington state rate and the matching rest of Washington state rate have the potential of being very different.

Statistical Calculations

The injury rates in InjuryRateAnalyzer are assumed to follow a Poisson distribution.  To calculate confidence intervals, InjuryRateAnalyzer follows the DOH Guidelines for Using Confidence Intervals for Public Health Assessment.  See Section 4.4 Crude and age-specific rates.  Specifically, for counts greater or equal to five and less than 100, the confidence intervals are calculated directly from the Poisson distribution.  For counts 100 and over, the normal approximation is used.

To determine whether rates are significantly different from their complement Washington State rates or All Ages rates, InjuryRateAnalyzer uses the Chi-squared test for a two-way table.

Limitations

Nonfatal Injury Hospitalization InjuryRateAnalyzer Applications

Hospitalization counts in Nonfatal Injury Hospitalization InjuryRateAnalyzer applications are obtained using a combination of Washington state's Comprehensive Hospital Abstract Reporting System (CHARS), Washington state VA hospitalization data, and Oregon hospitalization data.  Data is not collected, however, from military hospitals.  This could result in injury rates being underestimated in certain geographic locales in Washington state.

Military hospitals

The following military hospitals do not provide data to CHARS:

1. Madigan Army Medical Center, Pierce County
2. Naval Hospital Bremerton, Kitsap County
3. Naval Hospital Oak Harbor,  Island County

As a result, hospitalization rates in InjuryRateAnalyzer in Kitsap and Island counties likely underestimate the true rates.  To a lesser extent, the same is also true in Pierce county.

Cross-border hospitalizations

Washington state does not have access to Idaho injury hospitalization data, yet in certain border counties many Washington residents are hospitalized in Idaho, Asotin county being the county most affected.  As a result, this county is not included in Nonfatal Hospitalization InjuryRateAnalyzer applications.

Questions/Comments?

If you have questions or comments about InjuryRateAnalyzer, please contact Jethro DeLisle at 360-236-2867 or injury.data@doh.wa.gov.