Spreadsheets in the rubber laboratory.

In the early 1970s, widespread availability of the electronic calculator significantly reduced the time and effort required to calculate the specific gravity, cost per pound and pound/volume cost of a rubber formula. Though a vast improvement over the slide rule, permanent records had to be maintained manually.

Today, rubber companies keep formulas, mixer recipes and raw material information on mainframe and minicomputers. Compound and data records (test results) are maintained on computers as well, and some companies have developed programs to search appropriate database(s) for compounds meeting specific criteria.

Many facets of the production environment are integrated with computers. Specific to the rubber industry, there are computer-controlled weighing and mixing systems, injection presses and data acquisition systems. In the rubber laboratory, rheometers, viscometers, tensometers and analytical instruments are frequently supported by personal computers which set operating conditions and acquire data.

Why use spreadsheets?

Complaints among those who operate in a mainframe environment include cryptic log on routines, which may require passwords to be changed regularly, operating procedures that are not user friendly and difficulty in obtaining adequate time on terminals.

As personal computers continue to replace typewriters in the office and laboratory, a wide selection of software becomes available. Software to set up designed experiments, analyze the data and optimize formulations is available. SPC charts can be prepared by computers which collect the required data and print the charts. Recent quality control publications list more than one hundred distributors of some three hundred programs for designed experiments, statistical process control, gage R & R, problem solving and statistical methods (refs. 1 and 2). There is software designed to manage formulas and compound data, mixer recipes, mix cycles, raw material inventories and search for existing compounds with the desired properties (refs. 3-5).

Spreadsheet software, designed to record, manipulate and perform operations on columns and rows of numbers, can be used by the rubber chemist or compounder to perform specific gravity and cost calculations, and maintain records of formulas and test results. Worksheets can be tailored to fit a particular method of operation (the way a laboratory or office runs) or to fit the desires of an individual (chemist or compounder). And the software is likely to be available on a nearby PC.

The purpose of this article is to illustrate some worksheets that were developed to perform some of these tasks in our laboratory. We hope that the reader can apply some of what we have learned to his or her own operation. The worksheets discussed here were developed on an IBM compatible PC using Lotus 123 Version 2.2 (ref 6), but should be applicable to most other spreadsheets and computers. Similar worksheets have been used with Excel 3.0 (ref 7) on a MacIntosh (ref. 8) computer.

Historical records

The first worksheet is a quarterly record of production compound test results (table 1). The typical worksheet is composed of twenty columns of test data; for clarity, only five columns of data are shown. The cure time, test descriptions and specification limits are shown in the left-most columns. For ease of use, the worksheet is arranged so as not to require extensive operating experience in order to enter data, and is formatted to display the data in the desired manner.

We use several worksheet models of this type with different test profiles. The worksheet used to record data for a vibration control compound is similar to what is shown in table 1, whereas the worksheet for a fuel hose compound would include tests such as fuel immersions and permeation resistance.

[TABULAR DATA OMITTED]

These worksheets consist of data cells (for entry and storage of results) and formula cells (for the calculation of property changes). The formula cells are protected so that they cannot be written over, while data cells are unprotected so that data can be entered in them.

Aged values are compared to original values in order to calculate property changes. The formula cells remain blank if there are no original property values, but actual values are shown regardless of whether or not changes can be calculated. Durometer changes are expressed in whole numbers, while tensile, elongation, volume and modulus changes are expressed in percentages, formatted to display one decimal place. Volume changes are not calculated in the worksheet, but are done according to ASTM D 471 and entered in the appropriate cells.

The original formula used for tensile and elongation change, per ASTM D 573, was:

[(aged - original)/original] X 100 91) formatted to display one decimal place. By changing the cell format to percent mode, we eliminated the X 100 factor. This change, when carried out over 20 columns and 400 compounds, represents the space required for eight or nine worksheets. In addition to requiring less space, smaller worksheets run faster.

The columns to the right of the data area calculate the minimum, maximum and average values of all of the properties and changes within a worksheet. This information is being used to construct databases for the development of compound search programs.

Graphs can be constructed to visualize property trends over time (figure 1). We have found that viewing a graph can also help locate typographical or test errors. At one point, we explored the possibility of constructing control charts from a similar worksheet, and were able to construct average- and median-range charts, but found that by loading the data into a separate SPC program, it could be done in a fraction of the time.

We are currently evaluating a set of commands to detect test and/or typographical errors, so that an error message is displayed when an entered value is outside of set limits, telling the operator to double check the entry. The increased size of the worksheet needs to be weighed against the convenience and effectiveness of this feature.

Experimental formulas

This worksheet (table 2) lists experimental formulations by revision number and shows the corresponding properties. The formulas are arranged in groups of ten, which fit neatly into the width of one printed page in compressed print. For clarity, six formulas are shown. The specific gravities of the ingredients are entered and the ingredient volumes are calculated in the columns to the right of the formula columns (table 3). Cost data could be generated here as well, but we opted for the speed afforded by smaller worksheets and use a separate one for cost calculations when desired. The lower portion of this worksheet includes an area like the previous example in which to record test results.

[TABULAR DATA OMITTED]

A useful feature of a worksheet like this is that the effects of compounding ingredients can be studied by linear regression. In order to perform a regression analysis, the data must be transposed from column- to row-wise orientation. Hardness, then, (as Y, the dependent variable) could be compared to the carbon black and plasticizer levels (as the X's, or independent variables). If there are interactions between independent variables or if there is excessive test error, it will be indicated by a poor correlation coefficient. Using the constants and coefficients generated by a successful regression analysis, a formula can be written to predict properties based on variable levels.

If graphs are to be constructed (i.e., hardness versus carbon black level) it is desirable to arrange the independent variables in ascending or descending order. Automatic sorting requires that the data be arranged row-wise. If the independent variable is already in order, it does not matter whether it is column- or row-wise.

Compound cost calculation

This worksheet (table 4) generates specific gravity, cost data and a print out of that information. It requires a small database (table 5) containing the raw material data, all of which are copied to the current worksheet. As each code number is entered, the name, gravity and cost of the ingredient are returned to the proper columns. As the corresponding part level of each ingredient is entered, volume and cost per batch figures are calculated.

[TABULAR DATA OMITTED]

This worksheet can produce printed cost data very quickly, and manages a formula with up to 20 ingredients. It could be set up to handle as many ingredients as desired. Non-coded raw materials can be included in a formula by manually entering the name, gravity and cost. The gravity, part level or cost of an ingredient can be changed at will and the effects viewed immediately. If an existing compound is entered with its gravity and cost, an ingredient can be "removed" from that formula by entering a negative part level. Results can be saved for future reference.

Macros are utilized to import the raw material data, set the zeros display, default directory and print the results. Macros executed automatically when the worksheet is retrieved perform the following tasks:

* Set the current directory as the default;

* copy the raw material data to the current worksheet;

* set the zeros display; and

* return the cursor to the starting position.

The search instructions are contained in the cells to which the data are returned. The database is arranged with code numbers, ingredient names, specific gravity and cost per pound column-wise. In order for the search routine to be carried out, the ingredients must be listed in ascending order by code number.

Once the part levels are entered, the volume and cost per batch of each ingredient are calculated. The columns containing phr, volumes and cost per batch are totalled for calculating specific gravity, cost per pound and pound/volume cost.

In setting up a worksheet like this, you need to be aware of how your spreadsheet performs search routines. If a code number that is not on your code list is entered, the search returns the data for the code closest to, but not greater than the one entered. This results in an error if your code list is not sequential. Instructions should be incorporated to display a message when a nonexistent code is entered.

Since spreadsheets were designed to work with numbers, numeric searches are faster and easier to construct than label searches, but either type can be performed. Label searches, to retrieve data by ingredient name, require additional keystrokes which can be encoded in a macro. Input, criteria and output ranges must be specified before the macro will execute; these are "stored" in the worksheet so that they need not be specified each time the worksheet is used. Once the information has been located, it can be copied to the appropriate area of the worksheet.

The print command is also executed by macro - the worksheet remembers the print range, margins, page length and setup string, so the command to print can be executed quickly and easily.

Summary

The ability of the spreadsheet to record data in tabular form makes it ideal for recording historical data. The graphical capabilities of the spreadsheet can aid in the detection of test errors, typographical errors and property trends. The analysis tools included in the spreadsheet permit the chemist or compounder to examine the effects of compound variables on measured properties.

The information stored on computers can be accessed with greater ease than the same information on paper, and requires a fraction of the physical space for storage.

Practically any calculation can be set up in a worksheet so that it can be performed by simply filling in the blanks. This can be particularly useful in calculations that are not done frequently enough to be easily remembered. The calculations can be completed efficiently when the worksheets show the operator what information is required. The ability to perform this wide scope of calculations can permit the performance of tasks specific to the rubber laboratory without the need for dedicated, sometimes expensive, software packages.

Table 6 - raw material database

Code            Name                   Gravity      Cost
   1            NR-1                   0.9200       0.5500
   2            NR-2                   0.9200       0.5000
   3          Chlorobutyl              0.9200       1.1500
   4           SBR 1712                0.9500       0.3500
   5         Polybutadiene             0.9100       0.4300
   6        Polychloroprene            1.2300       1.7800
   7         Polyisoprene              0.9100       0.8000
   8           EPDM 1                  0.8600       1.3000
   9           EPDM 2                  0.8750       0.9500
  10        Ethylene/acrylic           1.0300       2.7500
  11             N-787                 1.8000       0.2200
  12             N-990                 1.8000       0.2800
  13             N-326                 1.8000       0.2500
  14             N-650                 1.8000       0.2200
  15             TBBS                  1.2900       3.0000
  16             DTMQ                  1.0600       2.1500
  17             MBTS                  1.5400       2.0000
  18            Paraffin               0.9770       0.4500
  19        Rubber process oil         0.9000       0.3000
  20          Aromatic oil             0.9760       0,1500
  21           Hard clay               2.6200       0.0300
  22             DOA                   0.9260       0.7500
  23             DOS                   0.9120       1.2000
  24            Silica                 1.9500       0.4500
  25           Whiting                 2.7100       0.0200
  26          Litharge 90%             4.8300       1.3000
  27               Talc                2.7500       0.2500
  28              NDMDC                1.7700       7.4000
  29              ETU-75               1.2000       3.3000
  30               NBC                 1.2600       4.8000
  31          Polymeric plast.         1.0800       1.3000
  32               MBT                 1.5000       1.9000
  33            Polyethylene           0.9200       0.8500
  34            Process aid 1          1.0400       1.3000
  35            Process aid 2          0.9700       2.6000
  36               DPPD                0.9930       3.9000
  37                MgO                1.9000       0.6500
  38            Stearic acid           0.8500       0.3500
  39               Sulfur              2.0500       0.1500
  40                DTDM               1.2600       4.0000
  41          Process aid 3            0.9200       0.8000
  42              DPTT                 1.5300       3.4000
  43              TMTM                 1.3900       2.7000
  44              TMTD                 1.4200       2.0000
  45             ZDBDC                 1.2100       1.8000
  46             ZDMDC                 1.6500       1.9500
  47          Zinc oxide               5.6000       0.6200

References

[1.] T. Kelly, Quality Progress 25(3), 19-81 (1992). [2.] M. Gonzalez and J. Wolak, Quality 30(6), 20-30 (1991). [3.] "Formula plus;" Copyright, Matrix Marketing & Engineering. [4.] "Professional compounder," "Senior compounder," "Junior compounder;" copyright, Elastomeric Information Services. [5.] "CDMS II,;" copyright, RAPRA Technology. [6.] Copyright, Lotus Development Corporation. [7.] Copyright, Microsoft Corporation. [8.] Trademark, Apple Computer, Inc.