- CFC-2005-2a.xls (4.08 MB)
USGS spreadsheet program for preliminary evaluation of CFC data.
This program is used by the US Geological Survey Chlorofluorocarbon Laboratory to assist in the preliminary interpretation of results of CFC analysis of groundwater. The Microsoft Excel workbook consists of several sheets.
Busenberg, E., and Plummer, L. N., 2005, CFC-2005-2a. USGS spreadsheet program for preliminary evaluation of CFC data. In: IAEA Guidebook on the Use of Chlorofluorocarbons in Hydrology. International Atomic Energy Agency, Vienna, in press.
Definitions common to all sub-sheets in CFC-2005-2a
- All input fields or their headings have a yellow background throughout the program.
- Required input fields have bold headings.
- Green background fields contain explanations, comments and caution statements.
- Columns with blue backgrounds should not be altered and these columns should be hidden.
- In all the figures, blue, red, and green lines represent CFC-12, CFC-11, and CFC-113, respectively.
- Ages of other data are found at the intersection of horizontal dashed-lines with curves of the same color. The age or other data can be obtained by placing the pointer on the intersection and a pop-up box appears with the pertinent data.
Setting up the program in "RESULTS" sheet
- CFC enrichment factors--Cells "E12", "E13", and "E14" are used to correct the air curves for any local enrichment of CFCs. Normally the contents of the cells should remain as "1.00" and the program will use the Northern Hemisphere air concentrations. If local enrichment is present, then the content of the cells can be changed to reflect this. For example a factor of "1.05" for CFC-12 should be entered to represent a 5 percent enrichment in the concentration of CFC-12 in air. The program will multiply the entire CFC-12 air curve by this factor. The local air curve in this example will be used to calculate the apparent CFC-12 ages of the samples. The Northern Hemisphere air curve as well as the local air curves are shown in Figure 1 of the "INPUT" sheet.
- Recharge elevation units-Cell "E15" determines the recharge elevation units that will be used through out the worksheet. An input of "0" is used if the elevations of recharge are entered in meters, while "1" should be used if elevations are entered in feet.
- Units of CFC concentration-Cell "E16" determines the units of input of the CFC concentrations in columns "R", "S", and "T". An input of "0" indicates that the unit of concentration of CFCs is entered as picomoles/kg, while an input of "1" is required if the concentrations are entered as picograms/kg.
- Date of sampling-The date format of column "G" is set for the entry of dates in order of month/day/year. For example an entry of 1/3/01 represents the date of January 3, 2001. The date of sampling is a required entry because it is used to calculate the apparent age in years of the ground water. The date format of column "G" can be changed in the Excel program through the following steps: Format-Cells-Date-(select preferred date format).
- Sensitivity of calculated age to the recharge temperature-Cell "U15" should be set at "0.0" in order for this program to calculate the correct apparent ages of the ground water. The program can be used to rapidly evaluate the sensitivity of the calculated apparent ages to the recharge temperature. For example an entry of "2.0" can be used to determine the sensitivity of the calculated ages if the recharge temperature of all the samples (column "V") were higher by 2°C. An entry of "-3.0" can be used to evaluate the sensitivity of calculated apparent ages for a recharge temperature that is 3.0°C lower that the temperature entered in entered in column "V". The program does not accept temperatures below 0°C.
- Sensitivity of calculated age to the recharge elevation-Cell "U16" should be set at "0.0" in order for this program to calculate the correct apparent ages of the ground water. The program can used to rapidly evaluate the sensitivity of the calculated apparent ages to the recharge elevation. For example an entry of "200" can be used to determine the sensitivity of the calculated ages if the recharge elevation of all the samples (column "V") were higher by 200 meters or feet. An entry of "-300" can be used to evaluate the sensitivity of calculated apparent ages for a recharge elevation that is 300 meters or feet lower than the elevations entered in column "W". The program does not accept elevations below sea level.
Input in the CFC-2005-2a Results sheet
Required entries are in bolded/italicized below
- Column "E"--Name of sample
- Column "F"--Number, if more than one sample wasollected from the same source
- Column "G"--Date of sampling
- Column "H"--Time of sampling
- Column "R"--Measured CFC-12 concentration in sample (if determined)
- Column "S"--Measured CFC-11 concentration in sample (if determined)
- Column "T"--Measured CFC-113 concentration in sample (if determined)
- Column "U"--Concentration of "excess air" present in the sample. The content of "excess air" is often less than 2 cubic centimeters STP per kilogram of water, but can be substantially higher in samples recharged along floodplains of rivers, or arroyos, or where recharge occurs through fractured rocks (see Section 4.4). Place "0.0" or an estimate of the "excess air" concentration that is appropriate for the recharge conditions of your sample if the "excess air" concentration has not been determined.
- Column "V"-Recharge temperature. The temperature of recharge of the groundwater sample, which may or may not be the same as the temperature of the water (see Section 4.3). If the recharge temperature has not been calculated from permanent gas concentrations, then enter here the mean annual air temperature of the recharge location.
- Column "W"-Recharge elevation. The elevation at the location of recharge (See Chapter 3 and Table 3.2-2). The recharge elevation can differ significantly from the elevation of the water sample at time of collection.
- Column "Y"-Salinity of the groundwater in parts per thousands. For most dilute ground and surface waters enter "0.00". (See Chapter 3 and Table 3.2-2).
Output of "RESULTS" sheet
- Calculated partial pressures are in columns "AM" through "AO".
- Apparent piston-flow dates of recharge are given in columns "AD" through "AU".
- Apparent piston-flow ages are given in years in columns "BM" through "BR" (see Chapter 3).
- Ratio ages calculated from two CFCs in years are given columns "BS" through "BX". Fraction of young water in the binary mixture of young and CFC-free water is also given where applicable (see Chapter 5).
Input up to 5 numbers from column "A" of the "RESULTS" sheet. The program evaluates several lumped-parameter models in the next 5 sheets. If there are multiple analyses of the same sample, it is recommended that all replicates be evaluated at one time.
Figure 1 on this sheet is a plot of the air mixing ratios (Table 13.2-1) that were used to calculate the ground water ages. If an enrichment factor was used, then both the local (solid lines) and the Northern Hemisphere (dashed line) will be shown.
On Figure 1 of the "SENSITIVITY" sheet are plotted the samples selected in the "INPUT" sheet.
- To observe the sensitivity of the calculated piston age to uncertainties in the recharge temperature, the input °C in cell "B4"will be added to the recharge temperature entered in column "V" of the "RESULTS" sheet.
- The input °C in cell "B5" will be subtracted from the recharge temperature entered in column "V" of the "RESULTS" sheet.
- To observe the sensitivity of the calculated piston age to uncertainties in the recharge elevation, the input uncertainty in cell "B8" will be added to the recharge elevation entered in column "W" of the "RESULTS" sheet.
- The input uncertainty in cell "B9" will be subtracted from the recharge elevation entered in column "W" of the "RESULTS" sheet.
The output will show the date of recharge and the error bars represent the uncertainty in the recharge date. Samples are color coded for identification-see table in "SENSITIVITY" sheet.
On Figure 1, the piston flow recharge dates can be obtained at the intersection of the horizontal dashed-lines with the air curves of the same color.
The local (solid line) as well as the North Hemisphere (dashed line) air mixing curves are given. The age of the samples can be evaluated with and without the local enrichment factor.
The samples were selected to be evaluated in the "INPUT" sheet.
Ratio ages are valid only if (Section 3.3, Chapter 5):
- The two CFCs are not contaminated or their air water equilibrium concentrations were not altered in any way.
- The ratio age is valid only if it is younger or the same as the piston age of the two CFCs used to calculate the ratio.
On Figure 1 of this sheet, the ratio age is obtained at the intersection of the horizontal dashed-lines with the ratio curves of the same color. The recharge date of the young fraction with the local and Northern Hemisphere air can be determined from Figure 1.
The percent of young water in a binary mixture of young and CFC-free water can be determined on Figure 2. Required input is the age of the young water to the nearest 0.5 years. The percent young water in the mixture can be obtained from this figure. Both the local air as well as the Northern Hemisphere air are shown.
The atmospheric partial pressures ratios of CFC-11/CFC-12, CFC-113/CFC-12, and CFC-113/CFC-11 are plotted in Figures 1 through 3, respectively.
- The solid black curves represent piston-flow models plotted using the local air curves.
- Unmixed waters should plot on the piston flow line.
- The dashed lines represent binary mixtures of young and CFC-free water. Mixtures fall on straight lines representing mixtures of various age-waters with CFC-free waters.
- Mixtures of 2000, 1995, 1990, 1985 and 1980 waters with CFC-free water are represented by red, blue, green, orange, and brown dashed-lines, respectively.
- The fraction of young water can be estimated from the location of the sample on the binary mixing lines.
- Set "A" of the three figures, plots all the samples in the worksheet while set "B" plots the samples selected (up to 5 samples) in the "INPUT" sheet.
Required input is the date of sampling that is entered to the nearest 0.5 years.
- The exponential model is assumed (Chapter 6).
- Figure 1 gives exponential model CFC partial-pressures of groundwater samples with average residence times of 1 to 1000 years. Figure 2 is a similar plot of the average residence times of 1 to 100 years.
- If the exponential model applies, the average residence time of a sample is represented by the intersection of the horizontal dashed-line with the exponential model curve of the same color.
- The samples shown were selected in the "INPUT" sheet.
Table 1 below Figure 1 gives the CFC concentrations as a function of the residence time of the ground water. Also given is the apparent piston-flow age for any given exponential residence time.
This sheet permits construction of mixtures of 2 water samples, both of which may contain CFCs. The two samples mixed are defined by their (assumed) apparent age. A graph shows the concentrations (in pptv) of up to 5 samples selected in "INPUT" for each CFC.
Is the revised air mixing ratios of CFCs that are used in this program to date Northern Hemisphere groundwater. The mixing ratios are given on the SIO-98 scale (Table 13.2-1).