Templates for Calculating Ageing
Precision
Description of Templates
Template Versions Available
How to Use the Standard Precision Template
Output
Bowker's Test of Symmetry
Pivot Tables
Contact Info
References
Description
A series of
Microsoft Office Excel* templates were
designed to automate the calculation of various measures of ageing
precision, including percent agreement and the total coefficient
of variance (CV, Chang 1982), plus generating both an agebias
plot and an age matrix table. These measures were recommended
by Campana et al. (1995). In the standard template, symmetry
is also tested with a Bowker's test (Hoenig et al. 1995)
if the percent agreement drops below 90%.
These templates are formatted for Microsoft Office Excel 2007*. Please
feel free to download and apply them as needed, and to share them
with other interested parties. As of the release date, all these
templates were correct to the best of my knowledge.
Versions of all the templates are also available for Microsoft Office Excel 2003*. Please use the Contact Info below to request these versions.

Template Versions Available
Click on each filename to download
the corresponding template. (All files are less than 100 kb.)
If clicking on the link doesn't work, try "Save Link as..."
Standard precision template 
Precision.xltx

This template is intended
to compare paired ages from one age reader, but can easily
be relabelled for comparisons of two different readers or structures.
It is designed for fish with a maximum age of 16 years or
less. Output is shown below, and includes an agebias plot, an agefrequency table, Total CV (per Chang 1982),Percent Agreement, and a Bowker's test of Symmetry.

Expanded precision templates 
Precision25yr.xltx
Precision30yr.xltx
Precision50yr.xltx

These are designed to compare
duplicate ages for species which can live for up to 25, 30,
or 50 years. The 30 & 50year templates do not accept
age0 fish; the 25year template does. The 50year template
includes a 'forgiveness factor' for age differences ± 2
years when calculating percent agreement, and has a somewhat
different arrangement from the other files. 
Accuracy template 
Accuracy.xltx

This template is intended
for testing age reader accuracy vs. reference collections.
It does not include a test of symmetry, as these tests are inappropriate for cases where one age is considered to
be more reliable than the other.

Symmetry templates 
Symmetry2kinds.xltx
Symmetry50yr.xltx

The first symmetry template calculates both Bowker's (unpooled) and EvansHoenig (diagonallypooled, Evans & Hoenig 1998) tests of symmetry. It accepts ages up to 16 years and age differences up to 10 years.
The second template calculates Bowker's test of symmetry for samples with ages up to 50 years. It accepts age differences of up to 10 years.
Each of these two templates includes its own distinct instructions.


How To Use The Standard
Precision Template
The description that follows applies
specifically to the standard template (Precision.xltx). The other
precision (and accuracy) templates here are comparable, but cell positions can change from one
template to another. Information on the Bowker's test applies
only to the standard template (and the symmetry templates).
Production (prod.) age is considered
to be the first age attached to a given fish, obtained while the
age reader is working with the entire set of samples (survey,
commercial quarter, etc.). Ages obtained while reageing fish from
the precision subsample are labeled 'test ages.' (Note: In the
Accuracy template, reference (ref.) age is used to indicate the
known or consensus age for each fish.)
Note: All data shown in red
on the template should be replaced.

Step 1: First, the two
sets of ages must be entered in columns A (prod. age) &
B (test age), starting at row 36. Overwrite the sample ages already
listed. Fish which are omitted in either ageing run may
be listed in the table at J36, but should not be included in this
section. Blank lines in the age data must also be avoided, as
these can artificially reduce the total CV.
Do not change the
first 34 rows of 'spacekeeper' ages. They are included to ensure
that the Pivot Tables list all ages, which in turn allows other
calculations to refer to the correct locations.

Step 2: Once all ages are entered,
the calculations in columns CE (starting at row 36) must be
copied downward to the last age pair. The computer may sometimes
do this automatically, depending on how the ages are entered.

These columns calculate the CV for each fish (indicated
by the subscript 'j'). Multiple columns were used in order to
simplify the equations. The column values are:
C, the average age, X_{j};
D, the sum of the squared differences (SSD) between each age
and X_{j}; and
E, CV_{j}, the square root of the SSD_{j} divided by X_{j}.
For fish for which both ages are zero, the 'IF'
statement in column E resets the CV_{j} to zero to avoid an error
message. Note that if your data contain blank lines, the 'IF' statement would convert them to artificial
CV_{j} values of zero; therefore, any blanks should be removed before copying the calculations.
(On the 30 and 50year templates, any age0 fish in columns
A or B will generate a '#ZERO!' message in column E, and the Pivot
Tables will not work properly.)

Step 3: Move to the upper right portion
of the template. Pivot Table 1 starts at cell AN1; Pivot Table
2 starts at AN23. Both Pivot Tables must be refreshed.
This can be done simply by rightclicking within each Pivot Table,
and selecting the 'Refresh Data' option. See below
for more details on the Pivot Table layout.
(These tables begin
in Column AZ for the 25year template, Column BA for the 30year
template, and Column CQ for the 50year template.)

Step 4: At the top of Columns HN, the
labels should be updated, including:
H1, the sample type (survey, commercial,
etc.) and year the fish were captured;
H2, what is compared (repeated readings of the same fish/comparison
between two readers);
H3, optional additional information;
J6, the total number of samples aged from this sample set;
N1, the species name;
N2, the date the exercise was completed; and
N3, the name of the age reader.

At this point, all remaining calculations
& displays should be complete. Doublecheck that the total N,
CV, and other measures seem reasonable. If not, review Steps 13
to see if anything was overlooked.

Step 5: Save the file to a new,
distinctive filename before printing. A file name which incorporates
species, sample source, and age reader is recommended. For example,
"SJS_HD_201302_GB.xlsx" could refer to an exercise which
I completed on haddock collected on the 201302 survey on Georges Bank.

Output
The printed data (H1AL43) show the sample size,
total CV (expressed as a percentage), percent agreement, and Bowker's
test results (if appropriate; see below).
A breakdown of the average test age for each production age (with
measures of variation) is shown in both table form and in the
agebias plot. An agefrequency table is included, as well. The header
lists the document name and the date printed. More information on these statistical measures and displays can be found at http://www.nefsc.noaa.gov/fbp/QAQC/measures.html.
Click above to view fullsize image.
For much of the printout, zeroes are not shown.
Disregard any "#DIV/0!" and "#NUM!", as all
ages may not be adequately represented in your sample. Do not
attempt to remove these from the template, as the calculations
in those cells may be needed in another precision exercise.
(The output for the
50year template is arranged differently, but contains the same
elements.)

Bowker's Test of Symmetry
In the standard template, the cells below the
printout (J45AJ85) are used in calculating the Bowker's test
(Hoenig et al. 1995). This considers only the samples for
which the age was not agreed upon. Comparisons are made on the
diagonal, i.e. fish with ages of (1, 2) are compared with
fish having ages of (2, 1).
Cells S46AJ64 calculate the chisquared value
for each diagonal pair of ages, based on the data in the age matrix
table. Cells S67AJ85 indicate which of these pairs include actual
fish; this total (cell K59) is the number of degrees of freedom
for the test. In cells K46K55, the chisquared values are summed
for each difference between the two ages (i.e. 1 year,
2 years, etc.), with an overall total in cell K58. Differences
of 10 or more years are combined in cell K55, and broken out in
cells K65K71. Hopefully, there will be few disagreements of this
magnitude.
The results of the Bowker's test are reported
in the printout (P6P9) only if the percent agreement is below 90%. A
significance level of P<0.05 is used to distinguish between
significance (**) and nonsignificance (n/s). If the test is not
reported, these cells contain 'N/A' for 'not applicable.'
Note: The standard precision
template is the only one in this set which incorporates a Bowker's
test. This test is not applicable to an accuracy exercise against knownage
samples, nor are the necessary calculations conducive to inclusion
on the precision templates for longlived species. If a symmetry test is
needed for older fish, use Symmetry50yr.xltx.

Pivot Table 1
The upper Pivot Table (AN1AR20) calculates the
Total CV and statistics for the test ages at each production age.
The Pivot Table refers to Columns A, B, and E. Line 21 is included
to make necessary adjustments to the Pivot Table results, by accounting
for blank spaces in A2B35. Mean CV is later calculated as (Sum
of Total CVs)/N from cells AQ21 and AR21. Part of this table is
mirrored in cells M13N30 on the printout, as well.
Use of production age as the basis for these calculations
is arbitrary and is not meant to indicate that either set of ages
is more accurate. Either set could be used here and as the xaxis
in the agebias plot.

Pivot Table 2
The lower Pivot Table (AN23BG43) is the basis
for the agefrequency table in the printout, and the source of counts
of fish and percent agreement values by age. It refers to columns
A and B only. Again, line 44 is present to adjust the totals because
of blank spaces at the top of Columns A and B.

Contact Info
Please contact Sandy
Sutherland (Sandy.Sutherland@noaa.gov) if you need any
assistance with these templates, or would like advice in developing
your own.

References
Campana, S. E., Annand, M. C., and McMillan, J.
I. (1995). Graphical and statistical methods for determining the
consistency of age determinations. Trans. Am. Fish. Soc.
124: 131138.
Chang, W. Y. B. (1982). A statistical method for
evaluating the reproducibility of age determination. Can. J.
Fish. Aquat. Sci. 39: 12081210.
Evans, G. T., and Hoenig, J. M. (1998). Testing and viewing symmetry in contingency tables, with application to readers of fish ages. Biometrics 54: 620629.
Hoenig, J. M., Morgan, M. J., and Brown, C. A.
(1995). Analysing differences between two age determination methods
by tests of symmetry. Can. J. Fish. Aquat. Sci. 52: 364368.

*Disclaimer:
Use of trade or brand names in any NEFSC publication or report
does not imply endorsement.

