WELCOME TO THE AGRICULTURAL ANTIBIOTICS CALCULATOR
(Copyright 2005 Environmental Defense)

EXPERIENCED USERS USE THESE LINKS TO GO DIRECTLY TO THE CALCULATOR DATABASE FILES:
AgABX_interactive1.xls
AgABX_static.xls

HOW TO ACCESS THE AGRICULTURAL ANTIBIOTICS CALCULATOR

2. Click on one of these links to access the Calculator spreadsheets: AgABX_interactive1.xls         AgABX_static.xls
This will either launch the spreadsheet within your browser or prompt you to open or save the file.  If you get the prompt, click open to launch the file; alternatively, if you want a copy of the spreadsheet on your own computer for use off-line, click save when prompted or right-click on one of the links above and select "Save Target as ..." to download it to your computer, and then open the downloaded file in MS Excel. [NOTE: If you download the files, you may wish to come back to our website periodically to make sure you have the latest version.]

3. If you are prompted to enable or disable macros, be sure to enable them.

4. NOTE: These Excel files are quite large (approximately 7 and 10 megabytes, respectively) because of the large amount of data and interactive features they contain.   For this reason, they are intended primarily for users with broadband internet access.  If this poses a problem for you, please contact us to see if other arrangements can be made.  Please be patient, especially the first time you access the file, as it may take up to a couple of minutes for the file to open or download; after the first use, it should be much faster for subsequent uses.

__________

Follow the links below for more information on the Calculator and how to use it:

WHAT IS THE AGRICULTURAL ANTIBIOTICS CALCULATOR?

NAVIGATING THE AGRICULTURAL ANTIBIOTICS CALCULATOR

UNDERSTANDING THE INFORMATION PROVIDED BY THE AGRICULTURAL ANTIBIOTICS CALCULATOR

[NOTE: These instructions are to be used in conjunction with the Calculator spreadsheets.  New users may wish to print out the instructions below for easy reference while using the Calculator.]

WHAT IS THE AGRICULTURAL ANTIBIOTICS CALCULATOR? (back to top)

The Agricultural Antibiotics Calculator is comprised of two web-accessible MS Excel spreadsheets that allow you to view national, state and county-level state estimates of the amount of antibiotics used as feed additives for nontherapeutic purposes in raising hogs, broiler chickens and beef cattle (note that therapeutic use, i.e., for treatment of sick animals, is not included).  More context for this information and descriptions of how the estimates were developed and their limitations are provided in the associated Environmental Defense report, Resistant Bugs and Animal Drugs: State and County Estimates of Antibiotics in Agricultural Feed and Animal Waste; see especially Appendix 1.

The first spreadsheet, called AgABX_interactive1.xls, is interactive and provides:

• estimates -- for all 50 states and each of the nearly 3100 U.S. counties -- of the quantities of antibiotics used annually as feed additives for non-therapeutic purposes in:

• hogs,

• broiler chickens (meat chickens produced for human consumption),

• beef cattle, and

• all three animal types;

• aggregate estimates for a) all antibiotics and b) "medically important" antibiotics, defined as those classified as "highly" or "critically" important by the US Food and Drug Administration (see our report Resistant Bugs and Animal Drugs);

• aggregate estimates expressed in a) pounds of antibiotics per county or state per year, and b) pounds of antibiotics per 1000 square miles per year by county or state (the latter accounts for the different sizes of counties and states by "normalizing" the data to a common unit of area that better reflects intensity of antibiotics use);

• the national rankings of each state, and the national and within-state rankings of each county (by both rank order and percentile);

• in addition to the amount used, estimates of the amounts of antibiotics excreted in animal wastes, for a) all antibiotics and b) "medically important" antibiotics;

• the estimated number of each type of animal annually receiving antibiotic feed additives for nontherapeutic purposes in each state and county, based on the 2002 Census of Agriculture; and

• the area in square miles of each state and county.

This spreadsheet's interactive features allow users to select the animal type(s), state(s) and/or county(ies) for which they wish to see data calculated and displayed, and to order the displayed data alphabetically or by rank order.  While it provides all of the aggregate estimates for all antibiotics and "medically important" antibiotics noted above, it does not provide data down to the individual antibiotic compounds.

The second spreadsheet, called AgABX_static.xls, has the same data as the first, but is "static" rather than interactive.  In addition, it provides national, state and county-level estimates for each individual antibiotic compound used in each type of animal, as well as the summary data.  It provides estimates in pounds per year, but not in pounds per 1000 square miles per year.

NAVIGATING THE AGRICULTURAL ANTIBIOTICS CALCULATOR SPREADSHEETS  (back to top)

UNDERSTANDING THE INFORMATION PROVIDED BY THE AGRICULTURAL ANTIBIOTICS CALCULATOR (back to top)

The interactive file, AgABX_interactive1.xls, has four tabs, while the static file, AgABX_static.xls, has eight tabs.  The tabs are named as follows (click on a name to see a description of that tab's contents):

AgABX interactive.xls file tabs                AgABX static.xls file tabs

AgABX interactive.xls file tabs

Start tab: 

This tab is used to make your selections as to which data you wish to see, and is largely self-explanatory.  Use the pull-down lists to select an animal type, and, if desired, an individual state and either an individual county or all counties in that state.  These selections determine what data appear on the other three tabs once you have run the Calculator.  The buttons named "Calculate results" and "Clear all entries" generate the selected data and reset the tabs to allow new selections to be made, respectively.

States tab:

This tab:

• displays the Calculator's estimates for the US as a whole (row 5) and for each of the 50 individual states.

• shows estimates in a) pounds per year and b) pounds per 1000 square miles per year, for:
          i) all antibiotics and
          ii) "medically important" antibiotics. 
  Each of these four estimates is provided separately for:
          iii) antibiotics used (columns B-E) and
          iv) antibiotics excreted in waste (columns F-I).

• indicates which antibiotics used for a given animal type are considered "medically important" in cell K4.

• provides national rankings for each state (Columns K and L).
          - Use the "Sort by Rank" button to display the state records in rank order by the amount of all antibiotics used in pounds per year (i.e., the values in column B).
          - Use the "Sort A --> Z" button to restore the original alphabetical listing.

• displays the area of each state in square miles(Column N); these values are used to calculate the area-adjusted data (pounds per 1000 square miles per year) shown in Columns D-E and H-I.

All US counties tab:

This tab:

• displays the Calculator's estimates for each county in the US, initially organized by state. 

• displays a state total and the portion of that state total that comes from counties within the state for which the USDA Census of Agriculture withheld animal data to avoid disclosing data for individual farmers (listed as "Undisclosed"). 

• as on the States tab, shows estimates in a) pounds per year and b) pounds per 1000 square miles per year for:
          i) all antibiotics and
          ii) "medically important" antibiotics. 
  Each of these four estimates is provided separately for:
          iii) antibiotics used (columns D-G) and
         iv) antibiotics excreted in waste (columns H-K).

• indicates which antibiotics used for a given animal type are considered "medically important" in cell D6.

• provides national rankings for each county (Columns L and M), and rankings within a state for each county (Columns N and O).
          - Use the "Sort by national rank" button to display the county records in rank order by the amount of all antibiotics used in pounds per year (i.e., the values in column D).
          - use the "Sort A --> Z" button to restore the original alphabetical listing.

• lists the number of animals in each state and county of the selected type(s) estimated from the Census of Agriculture (Columns P-R; use the horizontal scroll bar to display them); our estimates are based on the assumption that these numbers of each animal type are fed antibiotics for nontherapeutic purposes.

• displays the area of each county in square miles (Column S); these values are used to calculate the area-adjusted data (pounds per 1000 square miles per year) shown in Columns F-G and J-K.

Selected county(ies) tab:

This tab:

• displays the Calculator's estimates for each county in the state that was selected on the Start tab. The name of this selected state is shown in cell B7 (if no state was selected on the Start tab, this tab is blank). 

• displays national totals (row 5), state totals (row 8) and the portion of those state totals that comes from counties within the state for which the USDA Census of Agriculture withheld animal data to avoid disclosing data for individual farmers (listed as "Undisclosed," row 9). 

• as on the other tabs, shows estimates in a) pounds per year and b) pounds per 1000 square miles per year for:
          i) all antibiotics and
          ii) "medically important" antibiotics. 
  Each of these four estimates is provided separately for:
          iii) antibiotics used (columns C-F) and
          iv) antibiotics excreted in waste (columns G-J).

• indicates which antibiotics used for a given animal type are considered "medically important" in cell L4.

• provides national rankings for each county (Columns L and M), and rankings within a state for each county (Columns N and O).
          - Use the "Sort by rank" button to display the county records in rank order within the selected state by the amount of all antibiotics used in pounds per year (i.e., the values in column C).
          - Use the "Sort A --> Z" button to restore the original alphabetical listing.

(back to list of tabs)

AgABX static.xls file tabs

County all tab:

This tab:

• displays the estimates for each county in the US, organized by state, summed across all three animal types. 

• displays a state total and the portion of that state total that comes from counties within the state for which the USDA Census of Agriculture withheld animal data to avoid disclosing data for individual farmers (listed as "Undisclosed").

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to one or more of the three animal types. 
  Each of these estimates is provided separately for:
          d) antibiotics used (columns H-AG) and
          e) antibiotics excreted in waste (columns AH-BG).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell I5 or cell AI5 to display the cell comment. 

• indicates in which animal type(s) a given antibiotic compound is used, in rows 1-3 above the name of each antibiotic in columns K-AG:  B=broilers, H=hogs, C=beef cattle.

• provides national rankings for each county (Columns B and C) and rankings within a state for each county (Columns D and E).

• lists the number of animals of each of the three types in each state and county, estimated from the Census of Agriculture, for both 2002 and 1997 (the latter is the year of the prior census), in Columns BH-BM (use the horizontal scroll bar to display them). We used these to calculate the amount of antibiotics used as feed additives for nontherapeutic purposes.

(back to list of tabs)

State all tab:

This tab:

• displays the estimates for each state in the US, summed across all three animal types, for both 2002 (rows 10-59) and 1997 (rows 60-109).

• displays US totals for 2002 and 1997 (rows 7-8).

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to one or more of the three animal types. 
  Each of these estimates is provided separately for:
          d) antibiotics used (columns E-AD) and
          e) antibiotics excreted in waste (columns AE-BD).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell F5 or AF5 to display the cell comment. 

• indicates in which animal type(s) a given antibiotic compound is used, in rows 1-3 above the name of each antibiotic in columns H-AD:  B=broilers, H=hogs, C=beef cattle.

• provides national rankings for each state (Columns C and D).

• lists the total number of animals in each state, estimated from the USDA Census of Agriculture and summed across all three animal types (Column BE; use the horizontal scroll bar to display it).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.
          - Column BF shows the number of animals reported at the county level, while
          - Columns BG and BH show the absolute and percentage differences between the state and county totals. 
  This difference represents the number of animals for which data were withheld at the county level by the Census of Agriculture to avoid disclosing data for individual farmers.  We used these to calculate the "undisclosed" amounts of antibiotics used as feed additives for nontherapeutic purposes in each state, shown in the "County all" tab.

• shows the total number of counties in each state and the national total (Column BI).

(back to list of tabs)

County hog tab:

This tab:

• displays the estimates for each county in the US, organized by state, for hogs only. 

• displays a state total and the portion of that state total that comes from counties within the state for which the USDA Census of Agriculture withheld animal data to avoid disclosing data for individual farmers (listed as "Undisclosed").

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to hogs. 
  Each of these estimates is provided separately for:
          d) antibiotics used (columns H-Y) and
          e) antibiotics excreted in waste (columns Z-AQ).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell I4 or AA4 to display the cell comment.

• provides national rankings for each county (Columns B and C) and rankings within a state for each county (Columns D and E).

• lists the number of hogs in each state and county, estimated from the Census of Agriculture, for both 2002 and 1997 (the latter is the year of the prior census), in Columns AR and AS (use the horizontal scroll bar to display them).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.

(back to list of tabs)

State hog tab:

This tab:

• displays the estimates for each state in the US, for hogs only, for both 2002 (rows 9-58) and 1997 (rows 59-108). 

• displays US totals for 2002 and 1997 (rows 6-7).

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to hogs. 
  Each of these estimates is provided separately for:
          d) antibiotics used (columns E-V) and
          e) antibiotics excreted in waste (columns W-AN).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell F4 or X4 to display the cell comment.

• provides national rankings for each state (Columns C and D).

• lists the total number of hogs in each state, estimated from the USDA Census of Agriculture (Column AO; use the horizontal scroll bar to display it).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.
          - Column AP shows the number of hogs reported at the county level, while
          - Columns AQ and AR show the absolute and percentage differences between the state and county totals. 
  This difference represents the number of animals for which data were withheld at the county level by the Census of Agriculture to avoid disclosing data for individual farmers.  We used these to calculate the "undisclosed" amounts of antibiotics used as feed additives for nontherapeutic purposes in each state, shown in the County all tab.

• shows the total number of counties in each state and the national total (Column AS).

• shows the number of counties with data withheld by USDA (Column AT).

(back to list of tabs)

County broiler tab:

This tab:

• displays the estimates for each county in the US, organized by state, for broiler chickens only. 

• displays a state total and the portion of that state total that comes from counties within the state for which the USDA Census of Agriculture withheld animal data to avoid disclosing data for individual farmers (listed as "Undisclosed").

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to broiler chickens. 
  Each of these estimates is provided separately for:
          d) antibiotics used (columns H-Y) and
          e) antibiotics excreted in waste (columns Z-AQ).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell I4 or AA4 to display the cell comment.

• provides national rankings for each county (Columns B and C) and rankings within a state for each county (Columns D and E).

• lists the number of broilers in each state and county, estimated from the Census of Agriculture, for both 2002 and 1997 (the latter is the year of the prior census), in Columns AR and AS (use the horizontal scroll bar to display them).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.

(back to list of tabs)

State broiler tab:

This tab:

• displays the estimates for each state in the US, for broiler chickens only, for both 2002 (rows 9-58) and 1997 (rows 59-108). 

• displays US totals for 2002 and 1997 (rows 6-7).

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to broiler chickens. 
  Each of these estimates is provided separately for
          d) antibiotics used (columns E-V) and
          e) antibiotics excreted in waste (columns W-AN).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell F4 or X4 to display the cell comment.

• provides national rankings for each state (Columns C and D).

• lists the total number of broilers in each state, estimated from the USDA Census of Agriculture (Column AO; use the horizontal scroll bar to display it).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.
          - Column AP shows the number of broilers reported at the county level, while
          - Columns AQ and AR show the absolute and percentage differences between the state and county totals. 
  This difference represents the number of animals for which data were withheld at the county level by the Census of Agriculture to avoid disclosing data for individual farmers.  We used these to calculate the "undisclosed" amounts of antibiotics used as feed additives for nontherapeutic purposes in each state, shown in the County all tab.

• shows the total number of counties in each state and the national total (Column AS).

• shows the number of counties with data withheld by USDA (Column AT).

(back to list of tabs)

County beef cattle tab:

This tab:

• displays the estimates for each county in the US, organized by state, for beef cattle only. 

• displays a state total and the portion of that state total that comes from counties within the state for which the USDA Census of Agriculture withheld animal data to avoid disclosing data for individual farmers (listed as "Undisclosed").

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to beef cattle. 
  Each of these estimates is provided separately for:
          d) antibiotics used (columns H-S) and
          e) antibiotics excreted in waste (columns T-AE).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell I4 or U4 to display the cell comment.

• provides national rankings for each county (Columns B and C) and rankings within a state for each county (Columns D and E).

• lists the number of beef cattle in each state and county, estimated from the Census of Agriculture, for both 2002 and 1997 (the latter is the year of the prior census), in Columns AF and AG (use the horizontal scroll bar to display them).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.

(back to list of tabs)

State beef cattle tab:

This tab:

• displays the estimates for each state in the US, for beef cattle only, for both 2002 (rows 9-58) and 1997 (rows 59-108). 

• displays US totals for 2002 and 1997 (rows 6-7).

• shows estimates in pounds per year for:
          a) all antibiotics,
          b) "medically important" antibiotics, and
          c) each of the individual antibiotic compounds fed to beef cattle. 
  Each of these estimates is provided separately for
          d) antibiotics used (columns E-P) and
          e) antibiotics excreted in waste (columns Q-AB).
  (Use the horizontal scroll bar to display the columns off the screen to the right.) 

• indicates which antibiotics used are considered "medically important" in the text box that appears when you hover your mouse over cell F4 or R4 to display the cell comment.

• provides national rankings for each state (Columns C and D).

• lists the total number of beef cattle in each state, estimated from the USDA Census of Agriculture (Column AC; use the horizontal scroll bar to display it).  We used these to calculate the amount of antibiotics used for nontherapeutic purposes.
          - Column AD shows the number of beef cattle reported at the county level, while
          - Columns AE and AF show the absolute and percentage differences between the state and county totals. 
  This difference represents the number of animals for which data were withheld at the county level by the Census of Agriculture to avoid disclosing data for individual farmers.  We used these to calculate the "undisclosed" amounts of antibiotics used as feed additives for nontherapeutic purposes in each state, shown in the County all tab.

• shows the total number of counties in each state and the national total (Column AG).

• shows the number of counties with data withheld by USDA (Column AH).

(back to list of tabs)

(back to top)

 Environmental Defense  l  February 2005