Statistics for everyone: Excel and Statistics(17), Independent t-test

Excel and Statistics, everybody should know. : Contents

Independent t-test

With ‘example1. xlsx’, select ‘t-Test: Two-Sample Assuming Equal Variances’.

Enter the range of variables1 and variables2. Hypothetical mean differences usually use 0 (blank means 0), but if you expect the difference between the two groups to be about 10, you can enter 10.

Calculate the mean and standard deviation of the two groups, and then show the p value.

In general, when the p-value in column 13 is less than 0.05, it is considered ‘statistically significant’.

Select ‘t-Test: Two-Sample Assuming Unequal Variances’

Let's type exactly the same.

You will get almost similar results.

For the equal variance assumption and the unequal variance assumption, you can F-test, which you will learn later. The initial t-test (sometimes called as Student t-test) assumed equal variances, but later expanded to include unequal variances, and sometimes the ‘t-test of the unequal variances’ is separately called the ‘Welch t-test’.

It uses the ‘mean’, ‘variance’ and ‘number of observations’ of both groups to calculate other values such as the ‘t statistic’.

Since for now, the variances on both sides are almost the same, it does not significantly violate the equal variance assumption. If the variances are different, consider the ‘unequal assumption’.

‘t-Test: Two-Sample Assuming Equal Variances’ is using ‘Pooled Variance’.

If the numbers on both are different, enter them accordingly.

It will be reflected in the results accordingly.

Select two columnss, and then click Insert > Box and whiskers (or Box-Whisker plots shortly Box plot).

This can be created from Excel 2016, and in versions below 2013, there are various butts on the Internet to make them, and some I have uploaded them. For now, let’s learn how to make it in 2016.

Open ‘10. Dot plot, Jitter plot_dotplot_1.xlsx’, erase the values in the yellow fields in column DŒ, and paste your own data values. You can only enter up to 100 data. I could have done more, but I just limited it to 100. You can stretch it more if you want, but if there are too many dots, the shape of the Jitter plot is not pretty, so I just made it 100.

Five summarized values are calculated, and a Jitter plot and a Box plot are drawnŽ.

You can adjust the spacing between the Jitter plots and Box plot. Select the number of Box plots to delete. I don’t usually use numbers, but I put numbers in on purpose because it’s more convenient to erase them.

Let’s open ‘10. Dot plot, Jitter plot_dot plot-2.xlsx’. In a similar way, you can enter up to 100 values for two Œgroups and express them as dot plots. On the right, you can put the two dots right next to each other, and on the left, you can make them slightly apart. It is a visual representation of the distribution of the two populations.

You can rename the two groups and adjust their position accordinglyŽ. You can change the color of the dots or adjust the value of the axes, etc., using the functions of Excel itself.

Depending on the statistical method, it is highly desirable to express it as an appropriate plot.

Excel and Statistics, everybody should know. : Contents

=================================================