Tuesday, April 26, 2016

Multiple t-test in Excel

Doing multiple comparisons can easily get people in troubles like P-hacking. But sometimes we just want to know the difference between particular groups. As we know that Prism can do Tukey’s test as unplanned comparison in post-hoc analysis. It is very efficient, but not very illustrative in my opinion. I came across Student’s t-test in Excel, which helps visualize the result better, as shown below. The significant differences can be color coded automatically for you to see. Also, I liked it because you can work with raw data and you do not to process or copy and paste data in anyway. 

In this example data set, percentage inhibition to an bacterium by extractions from different parts of a plant were compared. In this case, each plant part is a group. Starting with building a table, you do not need half of them since "branch vs leaf" is redundant to "leaf vs. branch".

Next is an important step to make the result visual: conditional formatting. Set the rule as filling the cell with color when the number falls into a range of value in this cell. We can set the range to "0 to 0.05" working with traditional p-value. But notice here, we are attempting to avoid p-hacking so we need to use the p-value for each comparison after Bonferroni's Correction. So remember to divide the p value (usually 0.05) by the number of groups that you are comparing. In my case, 10 comparison, so 0.005 for every comparison. 

Do the t-test by typing the function “=T.TEST." "Array 1" and "array 2" stand for the two groups that you are comparing, again, the sequence does not matter. Then pick one or two tail; I picked two-tailed, so I typed 2. Then choose the type; there are three choices: "1" for paired t-test; "2" for two-sample t-test with equal variance; "3" for two-sample t-test with unequal variance. 

After you do the t-test for all the comparison, your significant results are highlighted in color. 
I have looked at the ANOVAs and Tukey's test in the Excel, but they are more complicated than they are in Prism. But when it comes to seeking for the difference between groups, I liked this color-coded style t-test chart (with Bonferroni's correction) so far.

No comments:

Post a Comment