How To Compare Two Sets Of Data In Excel

Close

How to compare two means using Excel



This tutorial will take you through the steps needed to use Excel to compare two sets of measured data. I have used some sample data from an investigation on the effect of two fertilisers on potato growth. Click on the link to open the spreadsheet. You can substitute your own results if you have them.

Launch Microsoft Excel. Type in a heading for your first (input) variable in cell A1. Don�t forget the units! Type in a heading for your second (outcome) variable in cell B1. Now enter your data down columns A and B. Save your file.

Enter formulae to calculate means for each set of data in the row below the last data items. The general folmula is: =AVERAGE(RANGE) e.g. =AVERAGE(B2:B11) The example shows an extra column used to clarify the headings. If you want to do the same thing, insert a column to the left of the data.

Enter formulae to calculate the standard deviations for each mean. The general formula is: =STDEV(RANGE) The standard deviation gives an indication of the degree of spread of the data around the mean. A high standard deviation suggests that there is a lot of variation in the data. The cells in the example have been formatted as number with the contents displayed to 2 decimal places. Select the cells and click on Format Cells� Number and then check that 2 decimal places is selected.

It is useful to display the data as a bar graph with error bars showing the standard deviations. The standard deviations give an idea of the shape of the distribution around each mean and the degree of overlap may help you decide if they are significantly different.

Highlight the two cells containing the two means. Then click on the Chart Wizard button in the toolbar or select Insert and then Chart� Choose the column chart type and the clustered column sub-type in the Chart Wizard that appears. Click on the Next button to go on.

The chart preview in the next step should look about right. If it doesn�t, cancel and check that you selected the correct cells before starting the chart wizard. Click the Next button.

Enter appropriate titles and labels in the Titles section of the next step. Click on the Gridlines tab and turn off any gridlines by making sure that all the tick boxes are empty. Click on the Legend tab and clear the tick box that displays the legend. Click on the Next button.

In the final step, choose the option to place the chart as an object in the current sheet and then click on the Finish button.

Right-click on one of the bars and select Format Data Series� from the menu that appears. Select the Y-error bars tab and select Display Both (i.e. plus and minus). Select the Custom option and then use the button at the end of the + box to select the cells that contain the standard deviations. Repeat this for the � box. You may want to tidy up the graph in other ways. Right-click on an object that you want to change and select the object�s formatting dialog.

Finally we can test the null hypothesis that there is no difference between the two means using the t-test. The general formula is: =TTEST(RANGE1,RANGE2,2,2) The numbers at the end indicate the type of test to be performed. If your data items are paired e.g. heart rates of people before and then after a meal, end the formula with 2,1.

The cell containing the t-test formula has been formatted as percentage. This makes the result easier to interpret.

The t-test gives the probability that the null hypothesis is true. So, a value of 5% or greater suggests that there is no significant difference between the means. Any actual difference is likely to be due to chance. If the value is less than 5%, an alternative hypothesis needs to be made since the means appear to be significantly different since they do not overlap significantly.