Epidemiological Calculations

 Week 1 Epidemiological Tools.xlsx  (32.448 KB)

There are a variety of tools that an epidemiologist might have at his or her disposal. The most common tools that are used in the field are SAS, SPSS, EpiInfo, R, and many others. These require significant time and diligence to become proficient at each of these statistical tools. However, almost every office has Microsoft Excel, Numbers (the Mac equivalent), or Google’s Excel-like product.

Often times epidemiological calculations can seem/appear intimidating. However, if you let the excel document do the work for you then you do not have to worry about making errors in simple math. Please download the attached excel sheet. You will be doing the following:

Calculating:

Crude Mortality Rate

Age-Specific Mortality Rate

Age-Adjustment (using the direct method)

Prevalence

Incidence Density

Graphing:

Incidence and Prevalence

You will need to go to the American FactFinder, then click on Advanced Search in the area marked state, county, or place type your community or a community near you and select enter. You are going to want information from a table that contains Age and Sex in the title.

• Week 1 Epidemiological Tools  – Excel Document (32.5 KB)

  Example: Open Cohort (City, State, Zip Code etc)
  Year	Number Sick	Total Pop	Incidence	Prevalence
  2001	900	890,750	101.04	101.0384507438
  2002	400	908,830	44.01	143.0410527821
  2003	200	920,623	21.72	162.9331441861
  2004	400	930,517	42.99	204.1875645474
  2005	100	939,605	10.64	212.855402004
  2006	300	948,844	31.62	242.4002259592
  2007	600	956,749	62.71	303.1098020484
  2008	200	963,160	20.76	321.8572199842
  2009	100	975,122	10.26	328.1640656246
  Problem !: Open Cohort	This is the space where you should place your chart!
  Year	Number Sick	Total Pop	Incidence	Prevalence
  2012	900
  2013	400
  2014	200
  2015	400

  Formulas in Excel can be useful. Most epidemiological measures of incidence and prevalence are simple formulas that you can program in excel. Please visit this Microsoft Overview: https://support.office.com/en-us/article/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173 Once you have finished the material, you will be ready for our first step. Incidence and Prevalence. The generic formula for both incidence and prevalence is sick/total population x some population multiplier, which can range from 100, 1,000, 10,000, 100,000, or even 1,000,000.

  https://support.office.com/en-us/article/overview-of-formulas-in-excel-ecfdc708-9162-49e8-b993-c311f47ca173Figure 1: Incidence and Prevealence of Herpes 2001 – 2009

  Incidence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 44.01263162527646 21.72441922480755 42.98685569420011 10.64277010020168 31.61742077728267 62.712372837599 20.76498193446572 10.25512705076903 Prevalence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 143.0410527821485 162.9331441860566 204.1875645474505 212.8554020040336 242.4002259591672 303.1098020483951 321.8572199842186 328.164065624609

  Years

   

   

  Rate Pper 100,000 Population

   

   

   

   

  In this example, let’s assume that once you are infected with this disease you have it for life. Also, a fundamental assumption of this table will be that no one dies in this 9 year time window. Click on each incidence and prevalence window to see where I inserted the formula. Incidence = (Number Sick Cell/TotalPopulation Cell)*100,000 Prevalence is a little more different. Because people do not die from this disease and for this example, no one is dieing we have to add the previous years numbers for total diseased. If we were trying to calculate the prevalence for 2002, we would have to enter the number from 2001 + 2002. Please click on the cell to see how I wrote the formula.

  Figure 1: Incidence and Prevealence of Herpes 2001 – 2009

  Incidence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 44.01263162527646 21.72441922480755 42.98685569420011 10.64277010020168 31.61742077728267 62.712372837599 20.76498193446572 10.25512705076903 Prevalence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 143.0410527821485 162.9331441860566 204.1875645474505 212.8554020040336 242.4002259591672 303.1098020483951 321.8572199842186 328.164065624609

  Years

   

   

  Rate Pper 100,000 Population

   

   

   

   

  In the chart above, please find the population data from your town for the years 2012-2013. Using Excel formula complete the table. Once your table is complete, please visist the following link: https://support.office.com/en-us/article/create-a-chart-from-start-to-finish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2#OfficeVersion=Windows You will need to create a chart based on the data you generated. Please see my example to the right of these instructions.

  Figure 1: Incidence and Prevealence of Herpes 2001 – 2009

  Incidence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 44.01263162527646 21.72441922480755 42.98685569420011 10.64277010020168 31.61742077728267 62.712372837599 20.76498193446572 10.25512705076903 Prevalence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 143.0410527821485 162.9331441860566 204.1875645474505 212.8554020040336 242.4002259591672 303.1098020483951 321.8572199842186 328.164065624609

  Years

   

   

  Rate Pper 100,000 Population

   

   

   

   

  Figure 1: Incidence and Prevealence of Herpes 2001 – 2009

  Incidence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 44.01263162527646 21.72441922480755 42.98685569420011 10.64277010020168 31.61742077728267 62.712372837599 20.76498193446572 10.25512705076903 Prevalence 2001.0 2002.0 2003.0 2004.0 2005.0 2006.0 2007.0 2008.0 2009.0 101.0384507437553 143.0410527821485 162.9331441860566 204.1875645474505 212.8554020040336 242.4002259591672 303.1098020483951 321.8572199842186 328.164065624609

  Years

   

   

  Rate Pper 100,000 Population

   

   

   

   

  Mortality Measures

  Example 1: Crude Mortality Rate & Age Specific Rate (ASR)
  Age Group	Deaths	Total Population	ASR
  0 – 14	4	231,158	1.73
  15 – 19	23	73,200	31.42
  20 – 34	500	231,158	216.30
  35 – 54	250	231,158	108.15
  55+	1000	211,895	471.93
  Total	1777	978,569
  Crude Mortality Rate	181.59
  Example 2: Age-Adjusted Rate (Direct Method)	Expected Deaths are calculated by multiplying the raw ASR X Standard Pop
  Age Group	Deaths	Total Population	ASR*	Standard Population ^	Expected Deaths
  0 – 14	4	231,158	0.000017304	56,100,000.00	970.76
  15 – 19	23	73,200	0.000314208	23,100,000.00	7258.20
  20 – 34	500	231,158	0.002163023	69,300,000.00	149897.47
  35 – 54	250	231,158	0.001081511	85,800,000.00	92793.67
  55+	1000	211,895	0.004719319	92,400,000.00	436065.03
  Total	1777	978,569	326,700,000.00	686985.14
  * we leave the ASR in decimal form and do not multiply it by 100,000 for this method
  ^ the standard population is usually the standard population for the United States.
  However, it can really be whatever population you want it to be.
  Adjusted Mortality Rate = Expected Deaths / Standard Population x 100,000
  Adjusted Mortality Rate	210.28

  In this exercise, we will be exploring how to calculate crude mortality, age-specific mortality, and direct age-adjustement. This exercise will require you to have access to demographic data from American FactFinder. Please work on the morbidity assignment prior to tackling this assignment. I am going to assume you already know how to use Excel formulas, as they were used extensively in the morbidity tab. As with the previous tab, I will provide you examples. Remember: Age Specific Mortality (ASR): Total Died in that Age Category/Total in that Age Category x 100,000 Crude Mortality Rate: All deaths/Total Population x 100,000 Age-adjustment is odd, you have to calculate the ASR first and not multiply it by 100,000. Then you multiply it by a standard population, or a population you are attempting to compare it to.

  Now if you look at the ASR, you will see that clearly 55+ have the highest age specific rate. However, remember that population covers a lot of people and can be prone to a high rate or mortality already. Calculating an age specific adjustment be useful to see if the crude rate increases or decreases.

  http://sphweb.bumc.bu.edu/otlt/MPH-Modules/EP/EP713_StandardizedRates/EP713_StandardizedRates_print.html