Interactive Excel Tool for Computing, Assessing, and Using EmP Ws Equations

developed by
Ken Gerow, Professor of Statistics
Department of Statistics, University of Wyoming
[email protected]

Abstract

Recent evaluations of the regression-line percentile (RLP) method for developing standard weight (Ws) equations urged a change in standard practices. We recommend a change to the empirical-percentile (EmP) method, which is not prone to length-related bias associated with extrapolating beyond the range of the data. To enable consistent and valid interpretation of relative weight, we recommend adoption of the use of three quadratic regression lines (1st, 2nd, and 3rd quartiles; the 2nd quartile is the median) of the distribution of mean log(weight) on log(Length). To facilitate developing new Ws equations and revising old Ws equations (i.e., those developed by the RLP method), we describe the use of a freely available interactive computational tool for developing Wsmodels with the EmP method. This tool estimates precision of Ws equations via bootstrapping and will calculate equations using all three quartiles. This suite of standards will give researchers flexibility in use of Ws, and also enable managers to assess relative weight more consistently than is possible using a single standard.

Tool Philosophy

Rather than “input data here”, and “get Ws equation there”, this tool was designed to allow the researcher to participate (by observing or intervening) in the process of setting up the data for equation estimation. In this way, it is hoped that the resulting equation will have less of a “black box” feel, and feel more like a sequence of understandable steps. Excel was chosen as the platform for the tool since it is ubiquitous, and it has graphical and interactive capabilities that most other software packages don’t (in combination, at least) have.

Tool Elements (each element has its own worksheet)

  • Raw data: Simply, a place to organize the length-weight data in a manner that facilitates data quality assurance and analysis.
  • Data quality: The data from a selected sampled population is graphed so that unusual values and other data quality issues can be detected. This sheet is kept hidden unless the user invokes it from the data sheet.
  • Collation: Mean weights for fish in each length class from each study population are recorded. In addition, the number of populations with fish in each length class is noted.
  • “Number of fish”: This sheet, hidden unless invoked by the user from the collation worksheet, tallies the number of fish in each length class from each study population.
  • Summarized: This sheet sets up the data for estimation of quartiles (1st, 2nd, and 3rd) and means of mean weights for each length class.
  • EmP-Ws: On this sheet, the summarized data is organized for estimation of Ws equations (in particular, length classes with insufficient data) are removed from consideration, and the resulting Ws equations are displayed.
  • Ws graphs: Plots of the resulting equations are done on the log-log and original scales.
  • Bootstrap: A bootstrap study is done to assess precision of the median Ws equations.
  • Units: this sheet allows the user to change measurement units; updated Ws equations and estimates of Ws are then provided.
  • Application: For a chosen study population, scatter plots are drawn of Wr for individual fish, and for means from selected length categories.