Notes on the FRL Statistics Excel spreadsheet

Version 2.0

Andy Martin Nov. 5, 2004

Modified by Bob Bolton Feb. 27, 2007

USAGE

The FRL Statistics calculator is an Excel spreadsheet, located at:

http://www.cohp.org/records/spreadsheet/Excel_spreadsheet_describe.html

then click the appropriate link to download the preferred file.

The spreadsheet lists all 3141 counties in the USA, including Washington DC.

Data Entry

Type 1 in the "Visit" column for the counties you have visited.

You can type 1 in the Glob column if you are tracking a collection of adjacent counties.

To erase a mistaken entry use the Delete key - do not use the space bar.

If you wish to rank the counties according to your preferred weighting scheme, devise a scheme that seems good to you, then enter the weights in the cells to the right of the “Weights:” label. For example, you may want to weight the categories from 1 to 5 or 1 to 10. After entering the weights, notice that the WT column on the right contains a rating for each county based on its characteristics factored with your weightings. The RANK column then shows the relative “importance” of each county, based on your own preferred weighting scheme.

To mark many entries in a state (if you have completed CA, for example), type 1 in the first row, type 1 in the glob column of that row if this state is in your glob, highlight the 1(s), then copy (Ctrl+C is the shortcut). Highlight the remainder of the rows for the state (rows 2 through last for state), then paste (Ctrl+V is the shortcut).

If you used an earlier version of the spreadsheet that had only 3,140 counties (no DC), you can still copy from the old and paste into this one. Before doing so, however, you’ll need to insert a blank row after the final CT county and before the first DE county. Then select all rows in the Visit and Glob columns, copy the selection (Ctrl+C), then click in the Visit column in the first data row of the new spreadsheet and paste (Ctrl+V).

Features

The spreadsheet automatically totals various attributes and displays them in the blue numbers. The categories of statistics collected are as shown below:

Column

Statistic

Visit

Count

Glob

Glob count

Area row 10

Total area

Area row 11

Total glob area

LA

50 Largest

APX

Apex

H50 row 10

50 Highest

H50 row 11

50 Highest excluding Alaska

HE

50 Highest eastern

WE

Western (AK, AZ, CA, CO, HI, ID, MT, NV, NM, OR, UT, WA, WY)

NE

Northeastern (CT, DE, ME, MD, MA, NH, NJ, NY, PA, RI, VT, WV, DC)

SO

Southern (AL, AR, FL, GA, KY, LA, MS, NC, OK, SC, TN, TX, VA)

MW

Midwestern (IL, IN, IA, KS, MI, MN, MO, NE, ND, OH, SD, WI)

5K

5000-foot prominence

2K

2000-foot prominence

PC

Pacific Coast

AC

Atlantic Coast

GC

Gulf Coast

GL

Great Lakes

CB

Canadian Border

MB

Mexican Border

CD

Continental Divide

AT

Appalachian Trail

PT

Pacific Crest Trail

GX

Geographical eXtreme

ST

State Highpoint

IS

Island

5E

5000-foot Effort

1E

1000-foot Effort (database to be populated by users)

Hi 5

Hi-5 County

TO

Total number of categories

WT

Weighted Total of categories

RANK

Ranking of WT

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. If the rows are sorted in any other sequence, the completed states and Hi-5 states won’t compute properly. Sorting by multiple columns may best be done using the Data/Sort… menu. Note that the standard sort does not match the ordering produced by FIPS code sorting on the county and state codes.

The panes are frozen down to the column headings and to the right of the county names. You can see the effect by scrolling vertically and horizontally using the scroll bars. Horizontal scrolling may be confusing, especially if you forget that there are frozen panes.

At the bottom of the spreadsheet is a state summary. It is useful for showing the number of counties in each state that a person has done. On the right it has an AVG WT (average weighted total) column which gives an average rating for the counties in the state. Then it ranks the states by this average. This provides some idea whether a person is working on their top priority states, determined by the weighting factors entered at the top of the spreadsheet.

The state summary is a little tricky. While the state summary heading is scrolled out of sight, the frozen heading can be confusing over to the right because the AVG WT in the state summary is in the same column as the RANK above.

Those tracking second glob areas can choose to unhide column C.

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.

MODIFICATIONS, OBSERVATIONS, QUESTIONS

At the current time this spreadsheet is considered quite stable. Contact Bob Bolton at rfbolton at bigfoot dot com if you have questions, discover any problems, or want any new categories added.