College & Research Libraries News

Using a spreadsheet program to shift a journal collection

By Dennis C. Tucker

Head of Serials

Mishaivaka-Penn Public Library, Indiana

Letting the computer perform the more complex calculations involved in stack-shifting.

No one enjoys moving library materials. No matter how it’s done, it’s a time-consuming, mentally and physically exhausting task. The task can be made easier and less time-consuming however by applying the modern technology that is available.

The secret to a successful move of a periodicals collection is proper planning, requiring a grasp of several elements: the size of the current collection in linear inches, the amount the collection grows in one year (also in linear inches), the length of a shelf in inches, and the number of shelves available to hold the collection in the new location.

Basically, then, planning a move is an arithmetical operation. The computer technology which is best designed to handle arithmetic is the spreadsheet. During a recent shift of our periodicals collection the use of a spreadsheet saved us hours of staff time and reduced our margin of arithmetical error.

The first step was to gather the data. We used a 3x5 card like the one shown in Figure 1 for each journal that was to be moved. On the card we supplied the following data: the title of the journal, and the height and thickness of a volume. Because the height and thickness of a volume can vary as issues change in size from year to year, some libraries might wish to note runs of the same title on separate cards, each according to its size.

© 1989 Dennis C. Tucker. Dennis C. Tucker is the author of From Here to There: Moving a Library (Bristol, Ind.: Wyndham Hall, 1987).

An indication of the dates of the run will answer any questions that may arise later as to whether certain volumes were included in the count or perhaps overlooked. Journals which will no longer grow because they have been dropped, suspended publication, or changed title, etc. should be marked as a closed entry and they will have no entry for “one year’s growth.” Open entries will, of course, show how many inches per year they grow. If a periodical is retained only for a specified number of years and then discarded (e.g. “current year plus four years”), an appropriate figure will be supplied for “retained for . . . years.” The length of the present run in feet and inches (or in inches alone) is vital to our calculations later on. The last two lines on the card are for calculations when figured manually. We will be using a spreadsheet to perform these functions for us and will therefore leave them blank on the cards. Libraries which classify their periodicals will also want to include the call number somewhere on each card for quick reference.

Because our collection is shelved in two different areas, we used two separate sets of cards for data-gathering. the cads were identical for both locations, but they were color-coded to avoid the time-consuming task of having to label which location the data referred to. We call our two locations “public access” or “reference serials” which is on our main level in open stacks, and the “Mobilex” which is on movable shelving on the lower level in an area restricted to staff use only. We used white stock for printing the cards for the main level and blue for the lower one.

(Later, when the data was entered into the spreadsheet, we interfiled the titles for both locations in a single alphabet, but the data for each location was placed in a separate column and calculated separately.)

To gather the information, we sent a student worker to the stacks with ayardstick. The portion of our collection to be moved was small by most academic standards—some 250 titles—and we only needed between four and six hours to gather all the data from our reference serials area. Approximately the same amount of time was spent collecting the information from the Mobilex. One of the reasons that the number of titles was low compared to our number of actual titles on hand was that many titles are kept only for the current year or are discarded when the microfilm or microfiche arrives and thus do not take up storage shelf space.

The next task was to key the information into the spreadsheet. We were fortunate to have a list of our journal titles already on a spreadsheet that had previously been used for budgeting purposes. For us, it was simply a matter of deleting the unwanted columns, leaving only column A with the titles in it, and saving the worksheet under a new name. (We used “movelist.”) Many spreadsheets have an “import” function which will translate files from either a database or a word processor; libraries which have a list of their holdings in some other electronic format may be able to use this function to avoid re-keying the titles into a spreadsheet.

Because the collection is held in two locations (the older volumes being held in storage), our layout was slightly—but only slightly—more complicated than that needed by libraries with a single location. Libraries with a single location need use only columns A, B, C, and D.

We used a setting of thirty-two characters for the width of column A. (See Figure 2.) If the column is narrower, longer titles will be truncated (e. g. Journal of the American Society of…); if it is wider, it will be difficult to print the spreadsheet on 81/2-inchwide paper.

Some spreadsheets have a “sort” capability. If possible, we recommend using it to sort the list on column A at this time, using an alphabetical sort in ascending order. That way, the computer will put titles in the proper order, and we won’t have to worry about having to shift because we have gotten them out of order. (Libraries which classify their materials could add a column for call numbers and sort on that column.)

The information contained in the other columns and their column-width settings were: Column B— the length of the current collection on public access shelves in linear inches (10 characters wide). Column C—the number of number of inches that the title grows in one year in the public-access section (10 characters wide). Column D was reserved for a formula which we will discuss later (9 characters wide). Column E contained the same information as column B—the number of linear inches of the title currently held—but for those volumes in storage (.10 characters wide).

Though our column widths could have been somewhat narrower and still contained the necessary data, we allowed the aforementioned widths to accommodate the heading at the top of each column. Our combined column width gave us a total of 89 characters. By using an Elite typestyle (12 pitch), we were able to print out our data lengthwise on 8*/2-inch-wide paper.

It must be noted that some of our titles go immediately upon receipt to the Mobilex (usually dependent upon expected usage). These titles will show a specified length for “growth per year” in column F. Most titles, however, go to “reference serials” for several years until they have outlived their popularity and are then shifted in bulk to the Mobilex, several years’ worth at a time. These titles will show a “zero” in the growth per year” column for the Mobilex (column F). Thus, the two collections do not grow' in the same manner.

After we had entered our list of titles and alphabetized it, we took the stack of cards from the “reference serials” section and entered the data for columns B and C—current length of the holdings and growth per year. Note that if a title was held only in the Mobilex, columns B and C remained blank. If a title is kept for the current year only, it thus occupies no shelf or the Mobilex, and was not entered into the spreadsheet.

Likewise, we took the stack of cards for the Mobilex and entered the data for columns E and F—current length of the holdings and growth per year. As mentioned, because most titles in the Mobilex tend to grow as the result of bulk shifting rather than on an annual basis, column F often remained empty. If no issues of a title were held in the Mobilex, both columns E and F remained empty.

Once the data was entered, it was time to build our formula for column D. What we wanted to show in this column was the number of inches to leave for each title so that we would be able to shelve the current holdings plus “X” years’ growth. By using a formula with a variable “X” instead of a specific value, we could change the value of “X” to study various options for achieving optimum placement. The value of “X”—the number of years we wanted to allow for—would be placed in cell D1, so that is how we built the formula. Thus, the formula for cell D5 (amount of space required for this title) is “B5 + (C5 * DI).” (See figure 2.) In other words “current holdings + (1 year’s growth " X (or DI) years).”

Once we had entered the formula into cell D5, we used the spreadsheet’s powerful copy function to copy it into every cell in column D, making the row number relative so that it would correspond to the proper row; that is, the formula for cell D6 would be “B6 + F5 * Gl).” Then we copied it into every cell in column G.

As we wanted to allowthe same number ofyears’ growth in each location (upstairs and down), we made cell Gl relative to cell DI by the use of the formula “+D1Libraries wishing to allow a different rate of growth at each location could simply enter an absolute into Gl, as well as into DI.

We then used sum” function to total each column with the following result: At the bottom of columns B and E, we had the length in inches of the current collection in each respective location. In columns C and F, we had the length in inches that each collection would grow in one year. In columns D and G, we had the total of linear feet needed to house the current holdings in each location plus X number of years’ growth. (See figure 3.)

Our shelves measure 35.5 linear inches in “reference serials” and 34.5 inches in the Mobilex. At the bottom of column B (cell B247 for us) we placed the formula “+B245135.5” (the total length of the current collection I the number of linear inches per shelf). We placed basically the same formula in columns D, E, and G, giving us the number of shelves needed to house the current collection in the “reference serials”plus Xyears (column D), the number of shelves needed to house the current collection in the Mobilex (column E), and the number of shelves needed to house the current collection plus X year’s growth in the Mobilex (column G).

In the four cells below those figures, we entered the numberof shelves currently available: in “reference serials” in columns B and D, and in the Mobilex for columns E and G.

In the next row 250, we entered a formula of “number of shelves available minus number of shelves needed” (cell B249 - cell B247). The result is the number of shelves remaining after the collection has been housed. If the number returned in columns D and G was a positive number, we knew that we had ample room for X years’ growth and could continue to increase the value of X (cell D1 or Gl) until a negative number was returned. If the value returned was a negative one, we knew that we did not have enough room for Xyears’ growth and needed to decrease the value of X until a positive number was returned. When we discovered the lowest possible positive number in cells D250 and G250, the number showing in cells DI and Gl was the optimum number of years of growth to allow room for.

Because of the arithmetical power of a spreadsheet, we needed only change the value of cell DI (or of D1 and Gl if we wanted to calculate different growth allowances), and a whole new set of measurements appeared without the tedious task of a manual recalculation, much subject to error, each time.

A library moving into a new location and wanting to determine how much shelving is necessary forX years’ growth may simplify the formula. They may stop with the figures in row 247—number of shelves needed; that is, the column total in D or G divided by the length of a shelf.

Please note that the figures so far are valid only if titles are going to continue to grow in their current location. We will see in a moment how we shift volumes in bulk from one location to another.

In our case, however, we next needed to decide which titles, and how much of each, would be shifted to storage.Unfortunately, there are no magic formulas for this portion of the job. It is simply a professional judgment call—deciding which journals the reference staff is least likely to have to traipse to the basement for. We decided that two members of the reference staff working together could probably make a better decision than a single individual, and there was often some discussion. Our tools were a yellow legal pad and a red pen—though any color will do. We simply took a walk through the “reference serials” stacks and gave it our best shot, taking careful notes as to which title was to be moved (or discarded in certain cases) and which years.

Once we had determined the titles which were to be moved to the Mobilex and the years of each to be moved, we had to make a rough determination of the number of linear inches to be moved. We chose to go with the figure already shown in column C—the length of growth per year, realizing that the figure may not be exactly correct as volume sizes sometimes change from year to year: A weekly may change to a bi-weekly or to a monthly; our collection may contain a gap which would cause the number of volumes to be moved to be smaller than expected; frequently publishers reduce the number of pages per issue in order to cut costs,etc. We decided to ignore such differences because they would be such a small percentage of the total mass that errors in calculation caused thereby would not provoke a major location shift.

As mentioned above, our figures so far show only how much space to leave for each title to grow in its present location. If volumes are to be shifted from one location to another, their length must be subtracted from the column that indicates their current location and added to the column for their new location. On our spreadsheet we subtracted from column B (length of the current collection in the “reference serials”) the number of inches to be moved to the Mobilex and added it to column E (length of current collection in the Mobilex). Most spreadsheets have an edit function which allows the user simply to insert “minus X” after the number in column B and “plus X” after the figure in column E without the need to recalculate manually and enter completely new figures into the respective columns.

Once all the numbers were entered, column D immediately provided us with the number of years’growth we could have room for uptasirs and column G gave us the same information for the Mobilex. We could now experience with the value of DI (and Gl) to achieve optimum placement.

At this point we were ready to begin shifting. Fortunately, we had gained three rows of stacks and were able to backshift. As soon as the first title was placed on the shelf, our student mover was instructed to leave however many inches of room where indicated in column G of the spreadsheet, whether or not that space was actually filled. Even if there were no issues of a title currently in the Mobilex, we had already moved them on the spreadsheet and column G told us how much room to leave for that title when it actually appeared.

Because we had decided to allow for five years’ growth in the Mobilex as well as in the “reference serials” area, we had about 114 shelves left over in the Mobilex. (See cell D250 in figure 3.) We decided to take up some of that slack by leaving the bottom shelf of each stack empty. This would eliminate the necessity of dropping to our knees in a poorly lighted area to find materials on the bottom shelf, and it would allow a little extra growth room scattered throughout the collection. In case we decided to shift another title from upstairs, or add a new one to the collection, there would be room to do so without a major shift.

After the Mobilex had been completely shifted, we began bringing journals down from upstairs a booktruck at a time, using the notes taken on our yellow legal pad as our guide for what should be moved. The shelf was ready and waiting for them. Because the space had already been left in the Mobilex for each title, it was not necessary to move our spreadsheet, it was easy to see a new journal should be placed in relation to the prior and following titles.

Once we had moved our volumes from the “reference serials” to the Mobilex, we began at the beginning of the alphabet upstairs and shifted our collection to allow room for five years’ growth (our optimum figure).

The entire process, from data collection to the physical move, was as smooth as a baby’s skin. We encountered no major snags and found that perhaps the hardest part was adjusting to the feeling of let-down once the excitement of the move was over.

ALA Library/Book Fellows for 1989-1990

Eight individuals, seven of them from academic and research libraries, have been selected to represent the American Library Association and U.S. librarianship as Library/Book Fellows overseas. The joint program of ALA and the U.S. Information Agency places U.S. library and publishing professionals in institutions overseas for up to one year.

Karen J. Starr, assistant professor at Oregon State University, Corvallis, will train Norwegian librarians in online searching of U.S. databases at the Norwegian School of Library and Information Sciences in Oslo. The appointment began in September and will end in June 1990.

Alicia Sabatine, library information specialist at Jefferson County Public School, Golden, Colorado, will teach online searching of U.S. databases to students from 16 English-speaking Caribbean countries and develop course outlines in this area. She is working at the at the University of the West Indies in Kingston, Jamaica, through June 1990.

Barbara G. Kile, director of the division of government publications and special resources at Rice University, Houston, will assist the National Central Library in Taipei, Taiwan, in developing and organizing its U. S. government publications collection. She will be in Taipei from January to June 1990.

Linda Eileen Williamson, documents librarian at the University of Illinois at Chicago, will assist in organizing and promoting a U.S. government documents collection at the University College, Dublin, Ireland. Her appointment is from April to September 1990.

Henry F. Raine, senior cataloger at the Folger Shakespeare Library, Washington, D.C., will plan and implement retrospective conversion of the Alexander Turnbull Collection of pre-1801 imprints (approximately 9,500 titles) for the National Library of New Zealand, Wellington. His appointment ends in September 1990.

Left to right: (back row) Henry F. Raine, Katherine Van de Vate, Faye Powell, Alicia Sabatine, Helen Amabile, Donald Hausrath; (front row) Karen J. Starr, Dorothy C. Woodson, Linda Eileen Williamson, Barbara G. Kile. Janet M. Gilligan.

Dorothy C. Woodson, social sciences subject specialist at the State University of New York at Buffalo, will work with the special collections department at the University of Swaziland Library to identify and collect materials on Swaziland held in U.S. libraries. She will also help develop the Swaziana collection by advising on acquisition procedures. The appointment runs from October 1989 to August 1990.

Faye Powell, social science librarian at Portland State University, will advise on collection analysis and development at the American Studies Research Center in Hyderabad, India. Her appointment runs until January 1990.

Katherine Van de Vate, Arabic cataloger at Princeton University Library, will train 12 library employees in cataloging at the Asad National Library in Damascus, Syria, from April to September 1990.

Library/Book Fellow positions available for 1990-1991 will be announced in late December 1989.

Copyright © American Library Association

Article Views (Last 12 Months)

No data available

Contact ACRL for article usage statistics from 2010-April 2017.

Article Views (By Year/Month)

January: 5
February: 5
March: 5
April: 11
May: 3
June: 0
July: 1
August: 4
January: 8
February: 17
March: 12
April: 9
May: 8
June: 3
July: 10
August: 7
September: 0
October: 10
November: 8
December: 1
January: 7
February: 7
March: 12
April: 4
May: 4
June: 5
July: 11
August: 9
September: 16
October: 16
November: 9
December: 15
January: 0
February: 0
March: 0
April: 0
May: 0
June: 0
July: 0
August: 5
September: 10
October: 3
November: 2
December: 8