Notes on the FRL_Stats.xls Excel spreadsheet.
	Andy Martin Nov. 5, 2004
 


1. USAGE
   The file FRL_Stats.xls is an Excel spreadsheet, located at
	http://groups.yahoo.com/group/cohp/files/
		then select the Excell FRL Statistics Computer link.

   You can also try going directly to

 http://groups.yahoo.com/group/cohp/files/Excell%20FRL%20Statistics%20Computer/

   It is 660K in size, a zipped version of 178K is also at this location. 

   It lists all 3140 counties in the USA (but not Washington DC).
   The user marks a 1 in the "Visit" column for
   counties they have hiked. They can also mark a 1 in the Glob column
   if they are tracking a collection of adjacent counties.
   To erase a mistaken entry use delete - do not replace it with a space.

   The spreadsheet will automatically total various attributes in blue
   numbers. These include count, total area, and several other statistics. 

   To mark many entries in a state (if you have completed TX, for example ;)
   mark the first row 1. Add the glob "1" if this state is in your glob.
   Highlight the one(s), and then "copy". Highlight the rest of the
   rows for the state (rows 2 through last for state). Select "paste".

   If the FRL_Stats.xls file is enhanced in the future, but still
   has 3140 counties in the same row order, you will be able to
   cut and paste your old spreadsheet hiked and glob info into the
   new file.

   You can sort the data columns as you please. However, the standard
   order is produced by sorting states in alphabetical order of
   postal names, and then sorting counties alphabetically within
   a state. There are a couple ways to do this:

	A. Data ==>
  	     Sort 
		Sort by State
		     then by County
		OK

	B. Use the AZ sort tool on the standard toolbar.
	     Click topmost county, then click AZ
	     Click topmost state. then click AZ

   Note that this does not match the ordering produced
   by FIPS code sorting on the county and state codes.

   Tip: If you want to have the column headers stay in place while
   scrolling down, click in the cell under "Visit", then
	Window ==>
	    Freeze Panes ==>

   Finally, those tracking second glob areas can choose to display
   hidden column "C".

2. SOURCE INFO FOR AREAS.
   This info came from a spreadsheet provided by Rik Dunham. The areas
   include water, while the World Almanac is land only. This does not
   make much of a difference - the 50 Largest list stays the same (though the
   order is slightly different), and most counties see only a 1% increase.
   Counties that border ocean or lakes can get quite a bit more than this,
   and even double their area.

   Should someone desire to use the World Almanac figures, they could be
   entered into the spreadsheet in a new column.

3. SOURCE INFO FOR COUNTY NAMES AND FIPS CODES.
   This info was also in Rik's spreadsheet. Many errors were found and
   fixed:
         NC Greene county spelled wrong (fixed)
         VA Clifton Forge & South Boston still included (removed)
	    Williamsburg missing (added)
         TN Loudon county on 3 rows. This was fixed, and all states checked
            to be sure they had proper number of counties. However, the names,
            codes, and area info was not checked.
         SC Pickens on two rows.
            Several other multiple row entries removed.
         RI Had all county names wrong (fixed)
         GA had some mix up with MA. (fixed) 
         AK and HI info missing. Names added from World Almanac,
	    FIPS codes and total (water + land) areas added from online info.
         DC was deleted.
         Broomfield CO was added.
       
	 Finally ran a "diff" between Rik's list and another listing
	 of the US cohps. Perhaps 10 more invalid or poorly spelled
	 names were discovered and fixed, many in Ohio.

	 The name info is probably solid now. Hopefully the FIPS
	 codes and areas are pretty good as well.
 
4. ADDING ADDITIONAL CATEGORIES

   Note: Changing county names, stste names, or adding/removing
   a county (currently 3140 of them) may change the standard
   sorting order, and make it impossible for users to transfer
   data in from previous versions of FRL_Stats.xls.

   Please do not do this unless absolutely necessary.

   At the current time FRL_Stats.xls is considered stable.
   Contact Andy at oldadit@iname.com to report bug fixes or
   suggest additions.

   The file FRL_Extra.xls can be used for adding additional
   categories or statistics.

   Look at the file ChangeLog.txt to see if file is available
   for editing. If it is, modify Changelog.txt to record your lock
   of the file, and give your name and email.

   The Excel version I used dates from 1997. Hopefully newer versions can use
   my output, and the output of newer versions can be used by the 1997 Excel.
   It will be best not to add the latest 2004 Excel features to
   the spreadsheet.

   To add a category:

	A. Add new data column in COMPUTATIONS AND DATABASE section. 
        B. Add title and comment for this data column.
        C. Add data to data column.
        D. Add data summation box (row 7) if appropriate. Assuming
	   your new column is "Z" then formula will be
	    =SUM(Z12:Z3151)
        E. Add users summation box (row 8). It will have an
	   equation of the type 
		=SUMPRODUCT($A12:$A3151, Z12:Z3151
           where Z is your data column, and 12 and 3151 are start and end
           of the county rows.
	   Note that steps D and E can be made easier by copying
	   the box immediately left of your new work, and then
	   minor edits of the equation as needed.
        F. Test your work. 
        G. Upload your file, both as the new FRL_Extra.xls, and with an
	   incremented version number.
	H. Modify ChangeLog.txt to note changes made, and to allow
	   others to modify file.

     Notes: adding things like purity statistics may take multiple 
     data columns.

     Adding info like National Park counts for counties only is possible.
     However, to add all parks will probably make the spreadsheet hard
     to maintain and sort, so please don't do this without an OK from
     the spreadsheet maintainer.


5. POSSIBLE ENHANCEMENTSS
   Find someone to serve as maintainer contact person.

   Add more data columns and FRL categories:
	Super, Presidential, Indian, 3 state & Twofer counties.
	He Man counties and 5K gain counties
	300' summit counties (big project)
	Multiple area cohps.
	Compute cohp total for completed states.
	Partial listings for N. Park and N. Forest HP cohps.

   Make the "Visit" column some sort of boolean. Best would be if the user
     could toggle entry with mouse. If this is not possible, perhaps
     add data check to insure only valid entry is "1" or DELETE.

   Adjust areas upwards as needed. A few counties in the listing provided by
     Rik Dunham had multiple areas for certain counties. Some of these have
     been summed, but this was not done for 100% of the counties.

   Get rid of gray line marking hidden columns.
   Lock data (is this needed ??)