Objective:
The objective of this lab is to effectively use Excel to preform statistical analysis on the data we collected from our cell count and behavioral assay experiments. Using statistical analysis, we will be able to construct histograms, preform descriptive analysis, T-tests, and F-tests.
Procedure:
- Download the TookPak Analysis add in for Excel
- Organize your data into 4 columns, the first two for your cell counts and the other two for your behavioral assay.
Preforming Descriptive Statistics: Cell Counts for Control
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Descriptive Statistics.
- Select your input range, the Cell Counts for Control column
- Check the boxes for “summary statistics” and “confidence level for mean”
- Press “OK”
Preforming Descriptive Statistics: Cell Counts for Treatment
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Descriptive Statistics.
- Select your input range, as the Cell Counts for Treatments Column
- Check the boxes for “summary statistics” and “confidence level for mean”
- Press “OK”
Preforming Descriptive Statistics: Control Swim Speed
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Descriptive Statistics.
- Select your input range, as the Control Swim Speed Column
- Check the boxes for “summary statistics” and “confidence level for mean”
- Press “OK”
Preforming Descriptive Statistics: Treatment Swim Speed
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Descriptive Statistics.
- Select your input range, as the Treatment Swim Speed Column
- Check the boxes for “summary statistics” and “confidence level for mean”
- Press “OK”
Creating a Histogram: Cell Count Control
- Observe your data and see where most of your data points are.
- I selected to have a Bin in increments of 2000, and my values were from 2000 to 40,000.
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Histogram
- For the input range, select the Cell Count Control Column
- For the Bin Range, select the range that you set. (2000-40000 in increments of 2000)
- Select “New Worksheet Ply” and “Chart Output”
Creating a Histogram: Cell Count Treatment
- Observe your data and see where most of your data points are.
- I selected to have a Bin in increments of 5000, and my values were from 5000 to 150,000.
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Histogram
- For the input range, select the Cell Count Treatment Column
- For the Bin Range, select the range that you set. (5000-150,000 in increments of 5000)
- Select “New Worksheet Ply” and “Chart Output”
- Select “OK”
Creating a Histogram: Control Swim Speed
- Observe your data and see where most of your data points are.
- I selected to have a Bin in increments of 0.05, and my values were from 0.00 to 1.
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Histogram
- For the input range, select the Control Swim Speed Column
- For the Bin Range, select the range that you set. (0.00-1 in increments of 0.05)
- Select “New Worksheet Ply” and “Chart Output”
- Select “OK”
Creating a Histogram: Treatment Swim Speed
- Observe your data and see where most of your data points are.
- I selected to have a Bin in increments of 0.05, and my values were from 0.00 to 1.
- Click on the Data Tab and select “Data Analysis”
- Under Analysis Tools, select Histogram
- For the input range, select the Treatment Swim Speed Column
- For the Bin Range, select the range that you set. (0.00-1 in increments of 0.05)
- Select “New Worksheet Ply” and “Chart Output”
- Select “OK”
F-Test Two Samples for Cell Counts Procedure:
- Click the Data Tab and Select Data Analysis
- Select F-test: two samples
- For “Variable 1 Range” Select the Cell Count Control Column
- For “Variable 2 Range” Select the Cell Count Treatment Column
- Chose a random output range and press “OK”
T-Test Two Sample Assuming Unequal Variances for Cell Count Procedure
- Click the Data Tab and Select Data Analysis
- Select T-test: two sample assuming unequal variances
- For “Variable 1 Range” Select the Cell Count Control Column
- For “Variable 2 Range” Select the Cell Count Treatment Column
- Chose a random output range and press “OK”
F-Test Two Samples for Cell Swim Speed Procedure:
- Click the Data Tab and Select Data Analysis
- Select F-test: two samples
- For “Variable 1 Range” Select the Cell Swim Speed Control Column
- For “Variable 2 Range” Select the Cell Swim Speed Treatment Column
- Chose a random output range and press “OK”
T-Test Two Sample Assuming Unequal Variances for Cell Swim Speed Procedure:
- Click the Data Tab and Select Data Analysis
- Select T-test: two sample assuming unequal variances
- For “Variable 1 Range” Select the Cell Swim Speed Control Column
- For “Variable 2 Range” Select the Cell Swim Speed Treatment Column
- Chose a random output range and press “OK”
Data and Analysis:
Cell Counts for Control |
|
|
Mean |
19067.61111 |
Standard Error |
2778.754145 |
Median |
17375 |
Mode |
2300 |
Standard Deviation |
16672.52487 |
Sample Variance |
277973085.4 |
Kurtosis |
2.206329842 |
Skewness |
1.48173658 |
Range |
68800 |
Minimum |
1200 |
Maximum |
70000 |
Sum |
686434 |
Count |
36 |
Confidence Level (95.0%) |
5641.170819 |
|
|
Cell Counts for Treatment |
|
|
Mean |
46153.88889 |
Standard Error |
11362.74492 |
Median |
21997.5 |
Mode |
16000 |
Standard Deviation |
68176.46955 |
Sample Variance |
4648031000 |
Kurtosis |
6.571781986 |
Skewness |
2.721715213 |
Range |
265500 |
Minimum |
1500 |
Maximum |
267000 |
Sum |
1661540 |
Count |
36 |
Confidence Level (95.0%) |
23067.59856 |
Control Swim Speed mm/s |
|
|
Mean |
0.39982 |
Standard Error |
0.016672247 |
Median |
0.39 |
Mode |
0.5 |
Standard Deviation |
0.117890591 |
Sample Variance |
0.013898191 |
Kurtosis |
-0.871219591 |
Skewness |
-0.001181841 |
Range |
0.454 |
Minimum |
0.2 |
Maximum |
0.654 |
Sum |
19.991 |
Count |
50 |
Confidence Level (95.0%) |
0.033504135 |
Treatment Swim Speed mm/s |
|
|
Mean |
0.40543425 |
Standard Error |
0.020604526 |
Median |
0.402 |
Mode |
0.33 |
Standard Deviation |
0.130314467 |
Sample Variance |
0.01698186 |
Kurtosis |
1.291242912 |
Skewness |
0.679440683 |
Range |
0.6657 |
Minimum |
0.1603 |
Maximum |
0.826 |
Sum |
16.21737 |
Count |
40 |
Confidence Level (95.0%) |
0.041676588 |
F-Test Two-Sample for Ciliate Counts |
|
|
|
|
|
|
Control |
Treatment |
Mean |
19067.61111 |
46153.88889 |
Variance |
277973085.4 |
4648031000 |
Observations |
36 |
36 |
df |
35 |
35 |
F |
0.059804482 |
|
P(F<=f) one-tail |
1.32006E-13 |
|
F Critical one-tail |
0.56910677 |
|
t-Test: Two-Sample Assuming Unequal Variances for Cell Count |
|
|
|
|
|
|
|
Control |
Treatment |
|
Mean |
19067.61111 |
46153.88889 |
|
Variance |
277973085.4 |
4648031000 |
|
Observations |
36 |
36 |
|
Hypothesized Mean Difference |
0 |
|
|
df |
39 |
|
|
t Stat |
-2.315544835 |
|
|
P(T<=t) one-tail |
0.01296619 |
|
|
t Critical one-tail |
1.684875122 |
|
|
P(T<=t) two-tail |
0.02593238 |
|
|
t Critical two-tail |
2.02269092 |
|
|
F-Test Two-Sample for Variances |
|
|
|
|
|
Control |
Treatment |
Mean |
0.39982 |
0.40543425 |
Variance |
0.013898191 |
0.01698186 |
Observations |
50 |
40 |
df |
49 |
39 |
F |
0.818413958 |
|
P(F<=f) one-tail |
0.251421026 |
|
F Critical one-tail |
0.608734983 |
|
t-Test: Two-Sample Assuming Unequal Variances |
|
|
|
|
|
|
Variable 1 |
Variable 2 |
Mean |
0.39982 |
0.40543425 |
Variance |
0.013898191 |
0.01698186 |
Observations |
50 |
40 |
Hypothesized Mean Difference |
0 |
|
df |
80 |
|
t Stat |
-0.211819232 |
|
P(T<=t) one-tail |
0.416393391 |
|
t Critical one-tail |
1.664124579 |
|
P(T<=t) two-tail |
0.832786783 |
|
t Critical two-tail |
1.990063421 |
|
Storage:
All computers used during lab time were left in their original condition for the next lab. The excel spreadsheet was saved for future use.
Conclusion:
This lab aided me in becoming proficient in the statistical analyses conducted during this lab. Being able to read the data from the descriptive statistics helped me to gauge where by BIN range should be and at what intervals I should include in the range. This lab made me comfortable preforming T-tests and F-tests using the excel program. During the T and F tests, we had to reject the null hypothesis due to the p-value not having any significance.
Future Goals:
I would like to learn about preforming different statistical analyses using the Excel program, because I find it difficult to navigate if you don’t have very clear instructions on how to preform the analysis needed. Stemming from this, I will play around with excel to learn how to do other analyses with my data. I would also like to become faster at transcribing data from one spreadsheet to another, even with the use of then special paste tool.