Angela A. Garibaldi Week 12

• S. aureus data ready for import into GenMAPP

Calculations of Microarray Data

1. use 3 groups: A/Z, B, E
2. Use 6 replicates as 6 biological replicates each

Normalize the log ratios for the set of slides in the experiment

To scale and center the data (between chip normalization) perform the following operations:

• Insert a new Worksheet into your Excel file, and name it "scaled_centered".
• Go back to the "compiled_raw_data" worksheet, Select All and Copy. Go to your new "scaled_centered" worksheet, click on the upper, left-hand cell (cell A1) and Paste.
• Insert two rows in between the top row of headers and the first data row.
• In cell A2, type "Average" and in cell A3, type "StdDev".
• You will now compute the Average log ratio for each chip (each column of data). In cell B2, type the following equation:

=AVERAGE(B4:B5487)

and press "Enter". Excel is computing the average value of the cells specified in the range given inside the parentheses. Instead of typing the cell designations, you can click on the beginning cell, scroll down to the bottom of the worksheet, and shift-click on the ending cell.

• You will now compute the Standard Deviation of the log ratios on each chip (each column of data). In cell B3, type the following equation:

=STDEV(B4:B5487)

and press "Enter".

• Excel will now do some work for you. Copy these two equations (cells B2 and B3) and paste them into the empty cells in the rest of the columns. Excel will automatically change the equation to match the cell designations for those columns.
• You have now computed the average and standard deviation of the log ratios for each chip. Now we will actually do the scaling and centering based on these values.
• Insert a new column to the right of each data column and label the top of the column as follows: A1_scaled_centered, A2_scaled_centered, etc.
• In cell C4, type the following equation:

=(B4-$B$2)/$B$3

In this case, we want the data in cell B4 to have the average subtracted from it (cell B2) and be divided by the standard deviation (cell B3). We use the dollar sign symbols surrounding the "B" to tell Excel to always reference that cell in the equation, even though we will paste it for the entire column. Why is this important?

• Copy and paste this equation into the entire column.
• Repeat the scaling and centering equation for each of the columns of data. Be sure that your equation is correct for the column you are calculating.

Perform statistical analysis on the ratios

We are going to perform this step on the scaled and centered data you produced in the previous step.

• Insert a new worksheet and name it "statistics".
• Go back to the "scaling_centering" worksheet and copy the first column ("ID").
• Paste the data into the first column of your new "statistics" worksheet.
• Go back to the "scaling_centering" worksheet and copy Column C ("A1_scaled_centered).
• Go to your new worksheet and click on the B1 cell. Select "Paste Special" from the Edit menu. A window will open: click on the radio button for "Values" and click OK. This will paste the numerical result into your new worksheet instead of the equation which must make calculations on the fly.
• Go to a new column on the right of your worksheet. Type the header "Avg_LogFC_MurB", "Avg_LogFC_MurE", and "Avg_LogFC_MurA" into the top cell of the next three columns. Delete the "Average" and "Standard Deviation" that was used in the last sheet.
• Insert a new column next to each average fold change (FC) column that you computed in the previous step. Label the column "Tstat". There should be:Tstat MurE, Tstat MurE, and Tstat MurA. Therfore, in the following equation the number of replicates will be 6 and the range of averages will be the entire Avg_LogFC column for that Mur. This will compute a T statistic that tells us whether the scaled and centered average log ratio is significantly different than 0 (no change). Enter the equation:

=AVERAGE(N2:P2)/(STDEV(N2:P2)/SQRT(number of replicates))

(NOTE: in this case the number of replicates is 3. Be careful that you are using the correct number of parentheses.) Copy the equation and paste it into all rows in that column.

• Label the top cell in the next column "Pvalue". In the cell below the label, enter the equation:

=TDIST(ABS(R2),degrees of freedom,2)

The number of degrees of freedom is the number of replicates minus one, so in our case there are 2 degrees of freedom. Copy the equation and paste it into all rows in that column.

• Insert a new worksheet and name it "forGenMAPP".
• Go back to the "foldchange_stats" worksheet and Select All and Copy.
• Go to your new sheet and click on cell A1 and selct Paste Special, click on the Values radio button, and click OK. We will now format this worksheet for import into GenMAPP.
• Select Columns B through Q (all the fold changes). Select the menu item Format > Cells. Under the number tab, select 2 decimal places. Click OK.
• Select Columns R and S. Select the menu item Format > Cells. Under the number tab, select 4 decimal places. Click OK.
• Select Columns N through S and Cut. Select Column B by left-clicking on the "B" at the top of the column. Then right-click on the Column B header and select "Insert Cut Cells". This will insert the data without writing over your existing columns.
• Delete Rows 2 and 3 where it says "Average" and "StDev" so that your data rows with gene IDs are immediately below the header row 1.
• Insert a column to the right of the "ID" column. Type the header "SystemCode" into the top cell of this column. Fill the entire column (each cell) with the letter "N".
• Select the menu item File > Save As, and choose "Text (Tab-delimited) (*.txt)" from the file type drop-down menu. Excel will make you click through a couple of warnings because it doesn't like you going all independent and choosing a different file type than the native .xls. This is OK. Your new *.txt file is now ready for import into GenMAPP. But before we do that, we want to know a few things about our data as shown in the next section.
  • Upload both the .xls and .txt files that you have just created to your journal page in the class wiki. Make sure that your file name is distinct from your other classmates so that nobody overwrites anyone else's file.