Data type spreadsheet

Figure 8.8 Relation of some data types in the overall architecture. Major record record collections like those at Virgin University and University of British Columbia are usually stored in spreadsheet-like form.

VBA recognizes three different data types textual, numerical, and logical. There are two types of numbers general numbers and integers, and both can be represented in single or double precision. Note that the spreadsheet always uses double precision, but that VBA needs to be told specifically to do so, otherwise the macro computes in single precision instead. 

At this point, although we have dealt with all of the necessary formulas and data, the spreadsheet seems almost empty. This can be readily corrected by means of the Copy function. The copy command is /EC /BC /C ). The prompt will ask for the source block of cells. Respond by typing B2..F2 (where you entered the formulas earlier), then Enter. You will now be asked for the destination. Enter the uppermost cell on the left (B2) and the lowermost right one (F15), thus B2..F15, then Enter. You will be rewarded by seeing the entire spreadsheet generated quickly (Table 1.2). 

The result of a query will be a table of summarized results, including name, cytological location, accession number, or Bloomington stock number each name hyperlinks to a full report (described below for each data type under Scope of FlyBase Data Types). The results can be saved as a text file for import into any common spreadsheet software that can interpret tab-delimited files (such as Excel or Filemaker), or as a FASTA formatted file of the sequences for sequence analysis. 

A typical plot of x vs./(x) is considered to have one coordinate dimension, the X, and one data dimension,/(x). These data sets are plotted as line graphs, bar graphs, and so forth. These types of plots are readily made with most spreadsheet programs as well as dedicated graphing programs. Figure 13.1 shows two graphs that are considered to have a one-dimensional data space. 

Spreadsheet Applications. The types of appHcations handled with spreadsheets are a microcosm of the types of problems and situations handled with fuU-blown appHcation programs that are mn on microcomputers, minis, and mainframes and include engineering computations, process simulation, equipment design and rating, process optimization, reactor kinetics—design, cost estimation, feedback control, data analysis, and unsteady-state simulation (eg, batch distillation optimization). 

Ternary-phase equilibrium data can be tabulated as in Table 15-1 and then worked into an electronic spreadsheet as in Table 15-2 to be presented as a right-triangular diagram as shown in Fig. 15-7. The weight-fraction solute is on the horizontal axis and the weight-fraciion extraciion-solvent is on the veriical axis. The tie-lines connect the points that are in equilibrium. For low-solute concentrations the horizontal scale can be expanded. The water-acetic acid-methylisobutylketone ternary is a Type I system where only one of the binary pairs, water-MIBK, is immiscible. In a Type II system two of the binary pairs are immiscible, i.e. the solute is not totally miscible in one of the liquids. 

Each data point must be transferred from data sheets into spreadsheets or databases. Verification of each datum should be performed by an individual who did not enter the data being verified. Audits of each phase of the study should be performed (i.e. preparation of collection forms, application calibration, each type of sample collection, sample transport, each type of chemical analysis, data recording, data entry, data verification and data storage). 

Type in the data for the standard curve in the A and B spreadsheet columns. Use the A column for the concentrations and the B column for the corresponding instrument readout values. For the unknowns and control, type in the instrument readout values in the B column cells, but leave the concentration cells blank. When finished, the A and B columns should appear as in Table 6.1, in which there are four standards with concentrations of 1,2, 3, and 4 ppm, two unknowns, and one control. 

Spreadsheets that can be used for multiple purposes or that can generate multiple types of information are sometimes preferred over single-purpose spreadsheets. An example is a spreadsheet designed to work for different products or a spreadsheet designed to produce multiple result sets from a single data set [e.g., a content uniformity evaluation according to European Pharmacopoeia (Pharm. Eur.), United States Pharmacopoeia (USP), and Japanese Pharmacopoeia (JP) (prior to ICH harmonization)]. 

Since Excel is a powerful tool used widely for many different purposes with many options not all options can be discussed in this chapter. The focus of the chapter is on manually self-created spreadsheets for data calculation and checks against acceptance criteria (logical operations). Excel spreadsheets that are used with other electronic systems for automatic data or information entry, for further operations, or used as a view tool for databases are not within the scope of this chapter. Nevertheless, these types of spreadsheets are viewed as a normal spreadsheet with automatic entry, and validation, including validation of the interface, will be included to cover this item. In this chapter we provide guidance in validation and revalidation of Excel spreadsheets and information about managing validated spreadsheets. 

Testing will include all types of cells mentioned in (a) to (c). All cells that will not be used for data entry are protected. A printout of the spreadsheets documenting the protected cells will be provided. 

The next type of standard output is the spreadsheet. These file formats use comma-separated ASCII code, but also add calculation information and addressing information for the columns and rows they occupy. The simplest of these are. DIF files, which originated to allow information transfer between VisiCalc worksheets in the Apple II computer and have been retained as a standard format.. WK files are Lotus-1,2,3 formats and. XLS are Microsoft Excel formats that have become spreadsheet standards, allowing transfer of data, calculations, addresses, and macro programs. 

There are three types of data that can be entered in the cells of the spreadsheet number, date/time, and text. For multiple entries of serial number with constant increment, enter the initial value into the first cell, highlight the cells, and select Edit -> Fill -> Series. To fill multiple entries of the same value into a range, enter the value into the first cell, move the pointer to the right-hand corner of the active cell to activate fill handle (a bold crosshair), and drag it through the range. Data are edited with the usual cut, copy, and paste operations in the Edit menu and the decimal places of scientific numbers is controlled via dialog box in Format -> Cells. 

One of the most useful tools to spot and eliminate errors is a spreadsheet, such as Excel or QuattroPro. QSAR modelers very frequently use spreadsheets to organize data into columns and rows of standardized values of the independent and dependent parameters. Spreadsheets allow easy sorting and filtering — two important functions used to find problem data and duplicates and other errors. In addition, spreadsheets have search and replace routines, plotting, and correlation functions, which allow the data to be reviewed in various comprehensive ways. The data can also be exported to other file types, which allow analysis by other software for statistics and any types of quantitative and qualitative relationships that may exist. It cannot be emphasized enough that the typical spreadsheet functions (including graphing functions) are excellent tools to find and eliminate erroneous or questionable values, duplicates, and other problem entries. 

The key methods that are the focus of this section are categorized as analytical versus numerical methods. Analytical methods can be solved using explicit equations. In some cases, the methods can be conveniently applied using pencil and paper, although for most practical problems, such methods are more commonly coded into a spreadsheet or other software. Analytical methods can provide exact solutions for some specific situations. Unfortunately, such situations are not often encountered in practice. Numerical methods require the use of a computer simulation package. They offer the advantage of broader applicability and flexibility to deal with a wide range of input distribution types and model functional forms and can produce a wide variety of output data. 

A very important facility is to be able to pass information to and from die spreadsheet. For many purposes die Cells statement is adequate. Each cell is addressed with its row first and column second, so diat die cell number (3,2) corresponds to B3 in alphanumeric format. It is dien easy to pass data back and forth. The statement x = Cells (5,1) places die value of A5 into x. Equivalently, Cells (i%, k%) = scores (i%, k%) places die relevant value of scores into the cell in die /th row and ktli column. Note diat any type of information including character and logical information can be passed to and from die spreadsheet, so Cells (7,3) = "Result" places die word Result in C7, whereas Cells (7,3) = Result places the numerical value of a variable called Result (if it exists) in this cell. 

---