Aporras1 Week 8 - Revision history

Aporras1: /* Calculate the Benjamini & Hochberg p value Correction */ fixed more cells

2017-11-29T00:46:37Z

‎Calculate the Benjamini & Hochberg p value Correction: fixed more cells

Aporras1: /* Calculate the Bonferroni and p value Correction */ cell edits on cell edits

2017-11-29T00:36:56Z

‎Calculate the Bonferroni and p value Correction: cell edits on cell edits

Aporras1: /* Statistical Analysis Part 1 */ final cell fixes

2017-11-29T00:31:20Z

‎Statistical Analysis Part 1: final cell fixes

Aporras1: /* Statistical Analysis Part 1 */ more cell fixes

2017-11-29T00:25:21Z

‎Statistical Analysis Part 1: more cell fixes

Aporras1: /* Statistical Analysis Part 1 */ more edit to code

2017-11-29T00:21:46Z

‎Statistical Analysis Part 1: more edit to code

Aporras1: /* Statistical Analysis Part 1 */ more cells

2017-11-29T00:15:26Z

‎Statistical Analysis Part 1: more cells

Aporras1: /* Statistical Analysis Part 1 */ code

2017-11-29T00:10:27Z

‎Statistical Analysis Part 1: code

Aporras1: /* Statistical Analysis Part 1 */ cells

2017-11-29T00:08:28Z

‎Statistical Analysis Part 1: cells

Aporras1: /* Statistical Analysis Part 1 */ time cells

2017-11-28T23:57:23Z

‎Statistical Analysis Part 1: time cells

Aporras1: /* Statistical Analysis Part 1 */ headers

2017-11-28T23:49:57Z

‎Statistical Analysis Part 1: headers

@@ Line 74: / Line 74: @@
 # Inserted a new worksheet named "dZAP1_ANOVA_B-H".
-# Copied and pasted the "MasterIndex", "ID", and "Standard Name" columns from the previous worksheet into the first two columns of the new worksheet.
+# Copied and pasted the "MasterIndex", "ID", and "Standard Name" columns from columns A, B, and C from the previous worksheet into the first three columns A, B and C respectively of the new worksheet.
-# For the following, used Paste special > Paste values.  Copied the unadjusted p values from theANOVA worksheet and pasted it into Column D.
+# For the following, used Paste special > Paste values.  Copied the unadjusted p values from column AK in the dZAP1_ANOVA worksheet and pasted it into Column D.
 # Selected all of columns A, B, C, and D. Sorted by ascending values on Column D. Clicked the sort button from A to Z on the toolbar, in the window that appeared, sorted by column D, smallest to largest.
 # Typed the header "Rank" in cell E1. Typed "1" into cell E2 and "2" into cell E3. Selected both cells E2 and E3. Double-clicked on the plus sign on the lower right-hand corner of the selection to fill the column with a series of numbers from 1 to 6189.

@@ Line 67: / Line 67: @@
 === Calculate the Bonferroni and p value Correction ===
-# Now we will perform adjustments to the p value to correct for the [https://xkcd.com/882/ multiple testing problem].  Labeled the next two columns to the right with the same label, dZAP1_Bonferroni_p-value.
+# Labeled the next two cells AL1 and AM1 with the same label: dZAP1_Bonferroni_p-value.
-# Typed the equation <code>=<dZAP1_p-value>*6189</code>, Upon completion of this single computation, used the Step (7) trick to copy the formula throughout the column.
+# Typed the equation <code>=AK2*6189</code>, Upon completion of this single computation, used the Step (7) trick to copy the formula throughout the column.
-# Replaced any corrected p value that is greater than 1 by the number 1 and typed the following formula into the first cell below the second dZAP1_Bonferroni_p-value header: <code>=IF(dZAP1_Bonferroni_p-value>1,1,dZAP1_Bonferroni_p-value)</code>, where "dZAP1_Bonferroni_p-value" referred to the cell in which the first Bonferroni p value computation was made.  Used the Step (7) trick to copy the formula throughout the column.
+# Replaced any corrected p value that is greater than 1 by the number 1 and typed the following formula into the first cell below the second dZAP1_Bonferroni_p-value header: <code>=IF(AL2>1,1,AL2)</code>, where "dZAP1_Bonferroni_p-value" referred to the cell in which the first Bonferroni p value computation was made.  Used the Step (7) trick to copy the formula throughout the column.
 === Calculate the Benjamini & Hochberg p value Correction ===

@@ Line 56: / Line 56: @@
 # In cell AK1, created the header by typing dZAP1_p-value.
 # Recalled the number of data points: n=20
-# In the first cell of the dZAP1_Fstat column, typed <code>=((20-5)/5)*(<dZAP1_ss_HO>-<dZAP1_SS_full>)/<dZAP1_SS_full></code> and hit enter.
+# In cell AJ2 of the dZAP1_Fstat column, typed <code>=((20-5)/5)*(AC2-AI2)/AI2</code> and hit enter.
 #* Copied to the whole column using step 7 again.
-# In the first cell below the dZAP1_p-value header, typed <code>=FDIST(<dZAP1_Fstat>,5,n-5)</code> replaced the phrase <dZAP1_Fstat> with the cell designation and the "n" as in 20. Copied to the whole column.
+# In cell AK2 of the dZAP1_p-value column, typed <code>=FDIST(AJ2,5,20-5)</code>
 # Before we move on to the next step, I performed a quick sanity check to see if I did all of these computations correctly.
 #* Clicked on cell A1 and clicked on the Data tab.  Selected the Filter icon.
-#* Clicked on the drop-down arrow on dZAP1_p-value column. Selected "Number Filters". In the window that appeared, set a criterion that would filter the data so that the p value has to be less than 0.05.
+#* Clicked on the drop-down arrow on cell AK1 dZAP1_p-value column. Selected "Number Filters". In the window that appeared, set a criterion that would filter the data so that the p value has to be less than 0.05.
-#* Excel will now only displayed the rows that correspond to data meeting that filtering criterion which was 2485 out of 6189.
+#* Excel only displayed the rows that correspond to data meeting that filtering criterion which was 2485 out of 6189.
 === Calculate the Bonferroni and p value Correction ===

@@ Line 44: / Line 44: @@
 #* Upon completion of this single computation, used the Step (7) trick to copy the formula throughout the column.
 # In cell AE2, I typed <code>=SUMSQ(H2:K2)-COUNTA(H2:K2)*Y2^2</code> and pressed "enter".
 # In cell AF2, I typed <code>=SUMSQ(L2:O2)-COUNTA(L2:O2)*Z2^2</code> and pressed "enter".
 # In cell AG2, I typed <code>=SUMSQ(P2:S2)-COUNTA(P2:S2)*AA2^2</code> and pressed "enter".
 # In cell AH2, I typed <code>=SUMSQ(T2:W2)-COUNTA(T2:W2)*AB2^2</code> and pressed "enter".
-# In the first column to the right of dZAP1_ss_t120, created the column header dZAP_SS_full.
+#* Upon completion of this single computation, used the Step (7) trick to copy the formula throughout the column.
-# In the first row below this header, typed <code>=sum(<range of cells containing "ss" for each timepoint>)</code> and hit enter.
+# In cell AI1, created the header by typing dZAP_SS_full.
-# In the next two columns to the right, created the headers dZAP1_Fstat and dZAP1_p-value.
+# In cell AI2, typed <code>=sum(AD2:AH2)</code> and hit enter.
 # Recalled the number of data points: n=20
 # In the first cell of the dZAP1_Fstat column, typed <code>=((20-5)/5)*(<dZAP1_ss_HO>-<dZAP1_SS_full>)/<dZAP1_SS_full></code> and hit enter.

@@ Line 37: / Line 37: @@
 # In the "Replace" tab, typed "NA" and clicked "Replace All".
 # Found that it replaced 5100 "NA"s.
-# In the first cell below the header dZAP1_ss_t15, typed <code>=SUMSQ(<range of cells for logFC_t15>)-COUNTA(<range of cells for logFC_t15>)*<AvgLogFC_t15>^2</code> and pressed "enter".
+# In cell AD2, I typed <code>=SUMSQ(D2:G2)-COUNTA(D2:G2)*X2^2</code> and pressed "enter".
 #* The <code>COUNTA</code> function counted the number of cells in the specified range that have data in them (i.e., does not count cells with missing values).
 #* The phrase <range of cells for logFC_t15> was replaced by the data range associated with t15.
@@ Line 43: / Line 43: @@
 #* The phrase <AvgLogFC_t15> was replaced by the cell number in which I computed the AvgLogFC for t15, and the "^2" squares that value.
 #* Upon completion of this single computation, used the Step (7) trick to copy the formula throughout the column.
-# Repeated this computation for the t30 through t120 data points.
+# In cell AE2, I typed <code>=SUMSQ(H2:K2)-COUNTA(H2:K2)*Y2^2</code> and pressed "enter".
 # In the first column to the right of dZAP1_ss_t120, created the column header dZAP_SS_full.
 # In the first row below this header, typed <code>=sum(<range of cells containing "ss" for each timepoint>)</code> and hit enter.

@@ Line 29: / Line 29: @@
 # Now in cell AC1, I created the column header dZAP1_ss_HO.
 # In cell AC2, I typed <code>=SUMSQ(D2:W2)</code>
-# I then highlighted all the LogFC data in row 2 for dZAP1 for the respective time, excluding the AvgLogFC, pressed the closing parenthesis key, and pressed the "enter" key.
+# Pressed the "enter" key.
-# Clicked on the cell with <code>=SUMSQ(</code> data of dZAP1_ss_HO and saw the cursor change to a thin black plus sign and double clicked.
+# Clicked on the cell AC1 with <code>=SUMSQ(D2:W2)</code> data of dZAP1_ss_HO and saw the cursor change to a thin black plus sign and double clicked.
-# In the next empty column to the right of dZAP1_ss_HO, I created the column headers dZAP1_ss_t(TIME) for times 15, 30, 60, 90, 120.
+# In cells AD1, AE1, AF1, AG1, AH1 I created the column headers dZAP1_ss_t15, dZAP1_ss_t30, dZAP1_ss_t60, dZAP1_ss_t90, dZAP1_ss_t120 for AD1 through AH1 respectively.
 # I found these data points: t15 had 4 data points, t30 had 4 data points, t60 had 4 data points, t90 had 4 data points, and t120 had 4 data points.
 # Counted the total data points to be 20.
-# Highlighted the data columns of dZAP1_LogFC_t15 through dZAP1_LogFC_t120 excluding the AvgLogFCs and pressed CTL+F.
+# Highlighted the data in columns D through W of dZAP1_LogFC_t15 through dZAP1_LogFC_t120 and pressed CTL+F.
 # In the "Replace" tab, typed "NA" and clicked "Replace All".
 # Found that it replaced 5100 "NA"s.

@@ Line 14: / Line 14: @@
 # In the cell below the dZAP1_AvgLogFC_t15 header, typed <code>=AVERAGE(D2:G2)</code>
 # Pressed the "enter" key.
-# Clicked on cell AC1 and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
+# Clicked on cell X2 and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
-# Repeated steps (5) through (7) with the t30, t60, t90, and the t120 data.
+# In the cell below the dZAP1_AvgLogFC_t30 header, typed <code>=AVERAGE(H2:K2)</code>
-#* With time t30, in the cell below the dZAP1_AvgLogFC_t30 header, typed <code>=AVERAGE(H2:K2)</code>
+# Pressed the "enter" key.
-#**
+# Clicked on cell Y2 and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
-#* With time t60, the cell below the dZAP1_AvgLogFC_t60 header, typed <code>=AVERAGE(L2:O2)</code>
+# In the cell below the dZAP1_AvgLogFC_t60 header, typed <code>=AVERAGE(L2:O2)</code>
-#**
+# Pressed the "enter" key.
-#* With time t90, the cell below the dZAP1_AvgLogFC_t90 header, typed <code>=AVERAGE(P2:S2)</code>
+# Clicked on cell X2 and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
-#**
+# In the cell below the dZAP1_AvgLogFC_t90 header, typed <code>=AVERAGE(P2:S2)</code>
-#* With time t120, the cell below the dZAP1_AvgLogFC_t120 header, typed <code>=AVERAGE(T2:W2)</code>
+# Pressed the "enter" key.
-#**
+# Clicked on cell AA2 and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
 # Now in the first empty column to the right of the dZAP1_AvgLogFC_t120 calculation, I created the column header dZAP1_ss_HO.
 # In the first cell below this header, I typed <code>=SUMSQ(</code>

@@ Line 11: / Line 11: @@
 # Created a new worksheet, naming it dZAP1_ANOVA".
 # Copied the first three columns A, B, and C containing the "MasterIndex", "ID", and "Standard Name" from the "Master_Sheet" worksheet for the strain and pasted it into the new worksheet dZAP1_ANOVA columns A, B and C. Copied the entire columns EI through FB containing the data for dZAP1 strain and pasted it into the worksheet columns D through W.
-# In columns X, Y, Z, AA, and AB: created five column headers dZAP1_AvgLogFC_t15, dZAP1_AvgLogFC_t30, dZAP1_AvgLogFC_t60, dZAP1_AvgLogFC_t90, dZAP1_AvgLogFC_t120 respectively in that order.
+# In columns X, Y, Z, AA, and AB: created five column headers dZAP1_AvgLogFC_t15, dZAP1_AvgLogFC_t30, dZAP1_AvgLogFC_t60, dZAP1_AvgLogFC_t90, dZAP1_AvgLogFC_t120 respectively in that order X through AB.
-# In the cell below the dZAP1_AvgLogFC_t15 header, typed <code>=AVERAGE(</code>
+# In the cell below the dZAP1_AvgLogFC_t15 header, typed <code>=AVERAGE(D2:G2)</code>
-# Then highlighted all the data in row 2 associated with dZAP1 and t15, pressed the closing parenthesis key, and pressed the "enter" key.
+# Pressed the "enter" key.
-# Clicked on this cell and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
+# Clicked on cell AC1 and positioned my cursor at the bottom right corner. Saw my cursor change to a thin black plus sign and double clicked.
 # Repeated steps (5) through (7) with the t30, t60, t90, and the t120 data.
 # Now in the first empty column to the right of the dZAP1_AvgLogFC_t120 calculation, I created the column header dZAP1_ss_HO.
 # In the first cell below this header, I typed <code>=SUMSQ(</code>