NEFSC Logo
FBP logo

Precision Templates Fishery Biology Program www.nefsc.noaa.gov

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
Age-Bias Plot
Contact Info
References


Updated Precision Templates (December 2018)

These updated templates display the data slightly differently than before. A small amount of noise was added to all paired ages to improve visibility of overlapping data points, and they are displayed with an Agreement Plot. The original Age-Bias Plot is still available (far right), but is not included in the printout.


Description

A series of Microsoft Office Excel* templates were designed to automate the calculation of measures of ageing precision, including percent agreement and the total coefficient of variance (CV, Chang 1982), plus generating both an agreement plot and an age matrix table. Most of 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 2013*. 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; if errors are found, please let me know via the Contact Info below.


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-new.xltx

This template is intended to compare paired ages from one age reader, but can easily be relabeled 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 agreement plot, an age-frequency table, Total CV (per Chang 1982), Percent Agreement, and a Bowker's test of Symmetry.


Expanded precision templates

Precision25yr-new.xltx
Precision30yr-new.xltx
Precision50yr-new.xltx

These are designed to compare paired ages for species with maximum ages up to 25, 30, or 50 years respectively. The 30- & 50-year templates do not accept age-0 fish; the 25-year template does. The 50-year template includes a ±1 year 'forgiveness factor' for age differences when calculating percent agreement, and has a somewhat different arrangement from the other files.


Accuracy template

Accuracy-new.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 two symmetry statistics: both Bowker's (unpooled) and Evans-Hoenig (diagonally-pooled, Evans & Hoenig 1998) tests. It accepts ages up to 16 years and age differences up to 10 years.

The second template calculates the Bowker's test of symmetry for samples with ages up to 50 years and age differences of up to 10 years.

Each of these two templates includes its own distinct instructions within the file.


Basic instructions can be found within each template.

The following gives more detailed instructions and explanations of the various elements.



How to use the Standard Precision Template

The description that follows applies specifically to the standard template (Precision_new.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 and 25-year precision 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, year, etc.). Ages obtained while re-ageing 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 'space-keeper' 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.

Typing age pairs into template

Step 2: Once all ages are entered, the calculations in columns C-J (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.

Dragging calculations downward to copy

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, Xj;
D, the sum of the squared differences (SSD) between each age and Xj;
E, CVj, the square root of the SSDj divided by Xj
F & G, random numbers to be applied to each age;
H, a checksum to test if the randomized age will be too high;
I & J, each age adjusted by the random number in F & G.

For fish for which both ages are zero, the 'IF' statement in column E resets the CVj to zero to avoid an error message. Note that if your data contain blank lines, the 'IF' statement would convert them to artificial CVj values of zero; therefore, any blanks should be removed before copying the calculations. (On the 30- and 50-year templates, any age-0 fish in columns A or B will generate a '#ZERO!' message in column E, and the Pivot Tables will not work properly.)

If asterisks (**) appear in column H, either use a template that accepts higher ages, or manually edit the randomized numbers so that they will be displayed on the agreement plot.

Step 3: Move to the upper right portion of the template. Pivot Table 1 starts at cell AR1; Pivot Table 2 starts at AR23. Both Pivot Tables must be refreshed. This can be done simply by right-clicking within each Pivot Table, and selecting the 'Refresh Data' option. See below for more details on the Pivot Table layout. (On other templates, these tables begin different positions but they are still to the far right.)

Refreshing pivot tables

Step 4: At the top of Columns M-R, the labels should be updated:

M1, the sample type (survey, commercial, etc.) and year the fish were captured;
M2, what is compared (e.g., repeated readings of the same fish/comparison between two readers);
O5, the total number of samples aged from this sample set;
R1, the species name;
R2, the date the exercise was completed; and
R3, the name of the age reader.

Example of header labels

At this point, all remaining calculations & displays should be complete. Double-check that the total N, CV, and the displayed data seem reasonable. If not, review Steps 1-3 to check 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, "ER_WNFL_2017Q1.xlsx" could refer to the exercise above.


Output

The printed data (M1-AP39) 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, as well as both an agreement plot and an age-frequency table. 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/QA-QC/measures.html.

Example template output with date from Campana 1995
Click image to view full-size version.

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 50-year template is arranged differently, but contains the same elements.)


Bowker's Test of Symmetry

In the standard template, the cells below the printout (O45-AN85) 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 W46-AN64 calculate the chi-squared value for each diagonal pair of ages, based on the data in the age matrix table. Cells W67-AN85 indicate which of these pairs include actual fish; this total (cell P59) is the number of degrees of freedom for the test. In cells P46-P55, the chi-squared values are summed for each difference between the two ages (i.e. 1 year, 2 years, etc.), with an overall total in cell P58. Differences of 10 or more years are combined in cell P55, and broken out in cells P65-P71. Hopefully, there will be few disagreements of this magnitude.

The results of the Bowker's test are reported in the printout (T6-T9) only if the percent agreement is below 90%. A significance level of P<0.05 is used to distinguish between significance (**) and non-significance (n/s). If the test is not reported, these cells contain 'N/A' for 'not applicable.'

Note: The standard and 25-year precision templates are the only ones in this set which incorporate a Bowker's test. This test is not applicable to an accuracy exercise against known-age samples, nor are the necessary calculations conducive to inclusion on the precision templates for long-lived species. If a symmetry test is needed for older fish, use Symmetry50yr.xltx and it's embedded directions.


Pivot Table 1

The upper Pivot Table (AR1-AV20) calculates the Total CV and statistics for the test ages at each production age. The Pivot Table refers to data in columns A, B, and E. Line 21 is included to make necessary adjustments to the Pivot Table results, by accounting for blank spaces in A2-B35. Mean CV is later calculated as (Sum of Total CVs)/N from cells AU21 and AV21. Part of this table is mirrored in cells Q13-R30 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 x-axis in the agreement plot.


Pivot Table 2

The lower Pivot Table (AR23-BK43) is the basis for the age-frequency 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.


Age Bias Plot

The age-bias plot can be found at column AW if it is needed.


Contact Info

Please contact Sandy Sutherland 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: 131-138.

Chang, W. Y. B. (1982). A statistical method for evaluating the reproducibility of age determination. Can. J. Fish. Aquat. Sci. 39: 1208-1210.

Evans, G. T., and Hoenig, J. M. (1998). Testing and viewing symmetry in contingency tables, with application to readers of fish ages. Biometrics 54: 620-629.

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: 364-368.


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



Link disclaimer | Email webmaster | Privacy policy |     File Modified Dec 13, 2018