I have been working on a data collection and analysis research project for over a year now and I had to learn to properly collect and analyze a lot of data. In this blog submission, I will introduce a few basic techniques that can be used to compare data using Microsoft Excel or Google Sheets. I prefer Goggle Sheets since it requires no previous installation and allows you to work anywhere (even offline). All the tables and charts in blog submission are based on Google Sheets. Luckily, these two programs don’t differ that much in terms of functions, so you can switch from one to another with minimal or no difficulties. The main differences between the two are found in the graphics and the menu appearance from which is selected the test to use. First, let's begin with what lab equipment and chemicals are needed.
EQUIPMENT and CHEMICALS
- FCF Brilliant Blue (Sigma Aldrich)
- Pasco Spectrometer and cuvettes
- XLMiner ToolPak (Google Sheets) or Analysis ToolPak (Microsoft Excel)
Prepare several solutions of FCF Brilliant Blue (see Table 1 below) from a stock solution of 9.5mg dye dissolved in 100mL distilled water using a volumetric flask. This stock solution will build a standard Absorbance-Concentration curve. Absorbance is measured using the Pasco Spectrometer. Absorbance wavelength maximum = 622nm (see both Table 1 and Figure 2). The Pasco Spectrometer only does full visible wavelength scans and not single wavlength analysis. Absorbance values for each concentration were taken every 60 seconds.
From data in Table 1, a standard Absorbance-Concentration curve of the dye is below. The calculated molar extinction coefficient, ε = 0,0923 μM-1cm-1:
COMPARISON OF TWO AQUEOUS COLORED SOLUTIONS
Now we have all the elements to run the main experiment. Imagine that we have to different (supposedly) equimolar aqueous solutions containing the same dye. Both were made from the stock solution using the same glassware and dilution procedure. We want to determine if the two aqueous solutions are different, and if they are is the difference due to preparation.
To do that, I prepared two separate (and what I think are equimolar) solutions- A and B- of FCF Brilliant Blue by doing a dilution of the stock solution. See Table 2 below for absorbance and concentration data.
As indicated by the absorbance data, there is a small difference (0,010) between the average absorbance value of the two aqueous solutions and a more numerically sizable difference (0,108μM) between the calculated concentrations of the two aqueous solutions. Visual observation of the two colored aqueous solutions indicate no perceptible difference between the two solutions. See figure 4 below. Based on sight, it could be that the two aqueous solutions are identical in concentration. The differences among the absorbance and concentration data are small and based on this we could conclude that there is no meaningful difference between the two aqueous solutions. But the difference exists and perhaps it is significant enough such that the two solutions are indeed different enough to not be identical. In fact, although the solutions look to have the same intensity of blue, there could be a chemical difference. Of course, this is a simulation (solutions were prepared on purpose) but in an actual lab situation details like these can be of value and importance.
What we want to do next is to determine if the Abs/Conc difference between the two solutions is significant. If YES, then is the difference because of chance alone? This aspect is considerably important in quantitative chemistry procedures (such as forensic analysis, pharmaceuticals, etc.) in which even the smallest mass of a chemical can lead to differences in two samples.
The first thing to do when a comparison of two experimental means is required is to formulate the following two hypotheses:
In general, rejection of the Null hypothesis is what you try to get in a series of experiments. Let’s suppose that a team of researchers is developing a new drug which must be better than the previous formulation. In order to achieve that goal, pharmaceutical studies must sustain the hypothesis according to which the new drug differ from the previous one (in terms of safety, effectiveness, etc). If all the trials don’t lead to the rejection of the Null hypothesis (no difference between the new and the previous drug), then another drug formulation strategy needs to be considered. For this example, μ1 and μ1 are selected parameters of the drug (antioxidant power, antimicrobial activity, etc).
In order to decide whether a difference between two means exist, a t-test can be performed. See mathematical equation below. As shown below calculating a t-test requires three key data values.
Source: Statistics and Chemometrics for Analytical Chemistry by James N. Miller and Jane C. Miller
In the numerator is the difference between the calculated means. In the denominator, s is a pooled estimate of the standard deviation whereas n1 and n2 are the sample sizes. An important factor that needs to be considered is the number of degrees of freedom (df); in case of the t-distribution, df is (n1+n2) - 2. This number indicates the number of values that are free to vary. Before doing a t-test, the level of significance, also known as α, needs to be chosen. Most of the time α is set at 0.05 (or 5%). By using this level of significance, it means that there is a 1 in 20 chance that the Null Hypothesis is rejected when it is true. For greater accuracy, α is set at 0.01 or 0.001 (1% or 0.1%). If the absolute value or modulus of t is greater than the Critical value, the Null hypothesis can be rejected. The Critical value can be found in specific tables (t-distribution critical value tables) or is given by the software test output.
Table 3 below shows the results for the comparison between the absorbance values of solution A and solution B. The test was run by XLMiner ToolPak-Excel Analysis Pak. (XLMiner is a Google Sheets Add-on. You can get it from the Add-ons from the Google Sheets page upper menu: Add-ons> Get Add-ons> Search Add-ons and type XLMiner ToolPak.)
[Note: Two-tail (or two-sided) tests are used more frequently than one-tail (or one-sided) tests. I don't discuss the latter in this blog.] As indicated in Table 3, there are different parameters but those to consider are highlighted in green and yellow. P(T<=t) two-tail and t Critical two-tail need to be considered together. During the test setup, Hypothesized Mean Difference was intentionally left blank (= 0). Since the absolute value of t Stat (-13,89758458) > t Critical two-tail (2,306004133), the difference between the two results given by the analysis of the concentrations of solution A and B is significant at the 5% level- H0, the Null hypothesis, is qualified to be rejected. Another way to run the hypothesis test, it is to look at P(T<=t) two-tail, also known as P-value, which represents the probability that t Critical two-tail exceeds the experimental value of t Stat.
At the beginning of the test, the level of significance was set at α = 0.05 (= 5%). Since the P-value two-tail is considerably smaller than that (0,0000006954 versus 0,05), the Null hypothesis, H0, qualifies to be rejected. Conclusion: There is a significant difference between the average absorbance value (and hence calculated concentrations) of solution A and solution B. Although both blue solutions look the same and have very similar absorbances and concentrations, they nevertheless are different enough from each other to not be identical.
In the header of Table 3, note that the t-test was conducted by assuming equal variances. Mathematically speaking, variance is the square of the standard deviation. In a more intuitive way, variance says something about the spread of values in a data set. In general, it is a good idea to compare the variances before running the t-test even though most of the time variances are assumed to be equal. Comparing variances is done using a F-test. See equation below. Keep in mind that F must be greater than or equal to 1; therefore s12 ≥ s22.
The F-test results for the Abs.-Concentration data are reported below in Table 4:
The principle behind the F-test is basically the same as the t-test since we basically formulate the following hypothesis:
Since F is smaller the F Critical value, the Null hypothesis is not qualified to be rejected and hence the variances in the two data sets are equal. The same conclusion is reached by looking at the P-value. The level of significance, α, for the test is 0.05 whereas the probability that the F value is smaller than the F Critical one-tail is 0.446519679, which is much larger than α = 0.05 used in the test. So, for the following t-test, the option “t-test: two sample assuming equal variances” is chosen. Conversely, if the Null hypothesis was rejected, then the option “t-test: two sample assuming unequal variances" would have been chosen.
We can conclude that solution A and solution B are different. At first glance they could have looked practically identical because of the similar (only small diferences) concentration and absorbance values. Of course, the whole experiment was set for instructional purposes in order to show the concepts of t-test, F-test, and so on. Another option for bringing analysis of data into the classroom is to compare results that come from an experiment such as a new chemical synthetic strategy which increases the yield of the product by comparing the yields of the products obtained with the old and new method. Conclusions are drawn from the strategy used but in order to do that one needs to collect as many results as possible. I used this kind of test when it came to analyzing the oxygen content using the Winkler method in different liquids. It turned out to be very useful, both in terms of scientific approach and instructional perspective since students did learn to collect data and base conclusions on them. Even a simple titration is a good option to start with. After determining the content of acetic acid in two different vinegars, a t-test can be performed.
I hope I gave some ideas for introducing new concepts in your lessons. Some of these concepts may not be easy to grasp but they can be very helpful in a scientific lab. As usual, I’m open to suggestions. Happy experimenting!
About 99% of the knowledge I have learned throughout this process has come from these three textbooks:
● Excel for Chemists: A Comprehensive Guide by E. Joseph Billo
● Statistics and Chemometrics for Analytical Chemistry by James N. Miller and Jane C. Miller
● Design and Analysis of Experiments by Douglas C. Montgomery
The last book is more advanced and it requires a deeper knowledge of these topics which are excellently covered in the second book. The first book gives you nice tips and tricks to use on Excel and it can be adapted for Google Sheets as well.
For Laboratory Work: Please refer to the ACS Guidelines for Chemical Laboratory Safety in Secondary Schools (2016).
For Demonstrations: Please refer to the ACS Division of Chemical Education Safety Guidelines for Chemical Demonstrations.
Other Safety resources
RAMP: Recognize hazards; Assess the risks of hazards; Minimize the risks of hazards; Prepare for emergencies