This information provides a description of two publicly available macro-driven spreadsheets that calculate dioxin toxicity equivalence (TEQ) from congener results: an Advanced TEQ Calculator and a Basic TEQ Calculator. The capabilities and applications of each are here described.
Two types of Excel-based calculators are available for calculating TEQ for the relevant dioxin, furan, and dioxin-like PCB (polychlorinated biphenyl) congeners in soil or water samples. Users of these calculators should already be proficient with performing TEQ calculations and with Excel spreadsheets. The programmed spreadsheets are macro-driven, and the user's computer must have macros enabled through Excel's Trust Center Settings.
The Advanced TEQ Calculator provides a "sensitivity analysis" that allows the user to see what effect different options for handling non-detected and rejected congener data have on a sample's TEQ value. The Advanced Calculator provides all of the following:
- TEQ result when non-detected congeners are substituted with zero as the congener concentration value,
- TEQ when non-detected congeners are substituted with 1/2 of the detection limit as the congener value,
- TEQ when non-detected congeners are substituted with the full detection limit value as the congener concentration,
- TEQ when non-detected congeners are handled using a non-parametric statistical technique based on the Kaplan-Meier (KM) approach to handling non-detect results in statistical calculations, and
- a calculation of the relative percent difference (RPD) between the lowest and highest TEQ options, which allows identification of sensitive samples at a glance.
The Kaplan-Meier approach for handling non-detected concentration values has been demonstrated by Dr. Dennis Helsel. The KM approach avoids the bias introduced when an artificial fixed value (such as 1/2 the detection limit) is substituted for a non-detect concentration result. Dr. Helsel has proposed using the KM technique in conjunction with dioxin TEQ and similar summing calculations (Helsel 2009).
In addition to handling non-detects, the Advanced TEQ Calculator facilitates evaluating how different options for handling rejected ("R-qualified") congeners affects the TEQ value. Note that all TEQ calculation options provide the same TEQ value when all congeners are detected.
The Basic TEQ Calculator does not provide a sensitivity analysis; it provides only the KM TEQ when simple non-detects are present. The Basic Calculator was designed with the incremental sampling design (http://www.epa.gov/superfund/health/contaminants/dioxin/dioxinsoil.html) in mind, so it also provides pre-programmed calculators for the Student's t 95% upper confidence limit (UCL) and the nonparametric Chebyshev 95% UCL when congener data for triplicate incremental sample results are entered into the first 3 sample slots of the Data Entry sheet. Another spreadsheet in the calculator facilitates UCL calculations for more than 3 replicates, but the TEQ value for each replicate must be entered (i.e., the raw congener data cannot be used on that sheet).
The Basic TEQ Calculator is useful when:
- A user is new to the Calculators and learning how to enter data. The Basic Calculator is less complex and the concepts can be mastered before moving to the Advanced Calculator,
- There are no rejected congener data,
- The congener with the highest toxic equivalent concentration (TEC) is not a non-detect,
- It has been decided that the KM TEQ will be the TEQ value used in all subsequent data evaluations and project decision-making, and
- The sample collection design for the congener data was based on defined decision units (DUs) and incremental sampling of the DUs.
One or both Calculators offer the following features:
To enable easy "copy & paste" of sample TEQ results to other programs, the sample ID, selected TEQ result, and any qualifier is reproduced as a list below the sample rows.
- An "Instructions" page.
- It is easy to change the values for the toxicity equivalence factors (TEFs).
- Unneeded congener columns can be hidden to reduce the overall size of the spreadsheet. For example, when there is data only for dioxin/furan congeners, the columns for dioxin-like PCB congeners can be collapsed out of the way.
- It is easy to reorder the congener columns so that the spreadsheet data entry order matches the order in the source database or data report.
- Data entry into the Calculators can be done in several ways: 1) manually into the "Data Entry & Output" sheet (if there are only a few samples), 2) by electronic "copy and paste" into the "Data Entry & Output" sheet once the congener order is synchronized, or 3) Advanced Calculator only: by "copy and paste" into the optional "Data Import" sheet (best if there are many samples).
- (Advanced Calculator only) the calculator comes with rows for 50 samples, however, sample rows are easily added or deleted simply by clicking a button.
- The spreadsheet indicates what percentage of TEQ comes from the dioxin/furans group (17 congeners) vs. the dioxin-like PCB group (12 congeners), if both are present.
- The TEQ value is qualified as "estimated" ("J") if more than 50% of the TEQ value comes from qualified, non-detected, and/or rejected congener data.
- The ready-to-use, blank Calculators' filenames contain the word "template." A "filled in" Calculator (with example data entered) is also supplied to aid learning how to use the Calculators. These files have the word "Example" in the filename. The example data demonstrates both correct and incorrect ways to enter data into the Calculators.
- The Calculators can serve as historical documentation of how TEQs were calculated and chosen for project use.
- Cells can be locked and the workbook password changed to secure existing data and maintain spreadsheet integrity in an Excel format. The Calculators can be viewed electronically even if the full Microsoft Office Excel program is not available by using a free Excel Viewer that is downloadable from the Microsoft Office website.
- Each sheet of the workbook can be "printed" as an individual pdf file. The computer must have either an Adobe Acrobat Pro or similar Adobe program (not just Adobe Acrobat Reader) or the CutePDF program installed so that "Adobe PDF" or "CutePDF Writer" appears in the drop-down menu for selecting a printer in the "Print" interface screen. (CutePDF Writer as a standalone can be obtained for free.) Other "print" selections (see Instructions sheet) allow an entire sheet to be shrunk to fit to one page before conversion to pdf. When viewing the pdf, the view must be enlarged to 600 % or 800%. Using those same commercial programs, the separate pdf files (one per sheet) can be assembled into a single pdf file of the entire workbook.