Getting Data into Excel

Since you use Excel largely for the analysis of experimental data, the problem of how to get that data into Excel is crucial. Certainly you don t want to spend time transcribing data from a piece of paper into an Excel worksheet. There are several ways of transferring data to Excel. In order of decreasing preference, they are  

---

Chemometrics uses a very large variety of software. In this book we recommend two main environments, Excel and Matlab the examples have been tried using both environments, and you should be able to get the same answers in both cases. Users of this book will vary from people who simply want to plug the data into existing packages to those that are curious and want to reproduce the methods in their own favourite language such as Matlab, VBA or even C. In some cases instructors may use the information available with this book to tailor examples for problem classes. Extra software supplements are available via the publisher s www.SpectroscopyNOW.com Website, together with all the datasets and solutions associated with this book. 

You can use a scanner and a software program that performs optical character recognition to create a data file from hard copy. Once imported into Excel, the data may have to be manipulated to get it into a useable form. The following example shows how to record a simple Excel macro for converting scarmed data with blank lines and other undesired features into useful columnar data. 

To export from Excel, select the columns you wish to export. Copy, change to another application file and Paste. To do the reverse operation, copy the text data, open Excel, put the cursor in one cell, and choose Paste. If the original text hie had tabs in it (is tab delimited), each entry will go into a different cell. If you do not prepare the text hie appropriately, you may get a mess (the whole hie put into one cell or one column. If you create several columns in Word using tabs, you can copy the columns and paste them into the spreadsheet and the entries will go into successive cells. 

Let us consider synthetic data for Enzyme-X which is similar to the actual data for pancreafic carboxypeptidase [13,14] (note the name of an enzyme ends in -ase ). We use synthetic data, so we can insert key points into an Excel plot. It should be clear that at low substrate concentrafion, the rate increases rapidly as more substrate is added. However, in spite of the efficiency of an enzyme, there is only a small amount in solution. Thus, the rate approaches a limit as more and more substrate saturates the active sites of the enzymes in solution and eventually reaches a limit, V , as shown in Figure 8.7. Special points have been inserted into the data so that you can see the limiting rate of0.090 mM/s and see that at half that rate, 0.045 mM/s, the substrate concentrafion is 0.0065 mM which is the value of Km- The value of Km is not easily seen on the first plot but when we show the double reciprocal plot in Figure 8.8 we get a more precise value of Km value in two ways. There are really two intercepts in the double-reciprocal plot, which apparently was the innovation of Lineweaver and Burk [15] in 1934. We can use the third and fourth columns of Table 8.1 to make such aplotforour Enzyme-X data in Figure 8.8. 

To transfer the R output to MS Excel, copy the first section of the table without the column headers (for example the PC1-PC8 data before the first section break) and paste it into an MS Word document. Change the font to Courier and the size to 5 pt such that the text in the document resembles a table. Save the file as a Text-only (.txt) file. From MS Excel, import the data using Data > Get External Data. Select the file, then choose the Fixed width option in Step 1 of the Text Import Wizard, click on Next , verify column divisions, and then click on Finish . 

---