Though Microsoft Excel descriptive statistics is not limited to pharmaceutical calculations, it does play an important role for the pharmacy student who advances onto postgraduate study. Below, we outline how you can maximize your understanding of descriptive statistics as found in Excel. First, we examine how you can enable this feature before moving on to understand how best you can use this useful feature
The descriptive statistics feature on MS Excel is a useful tool for performing statistical analyses for sets of experimental data. The likes of the sample mean, sample standard deviation and confidence levels can be determined using this feature. One may access this feature by clicking the “Data Analysis” button under the “Data” tab on Microsoft Excel 2010.
“Data Analysis” is typically disabled on most computers. In order to access this feature, you may need to enable it first.
Step 1:
With Microsoft Excel opened, go to the “Files” and click “Options” to open the “Excel Options” window.
Step 2:
With the “Excel Options” window opened, click “Add-Ins”
Step 3:
Select “Excel Add-ins” on the drop-down menu next to “Manage” and then click “Go…” to open the “Add-Ins” window.
Step 4:
With the Add-ins window opened, click the small square next to “Analysis ToolPak” and click “Ok”.
After step 4, the “Data Analysis” button should now be available under the “Data” tab.
Although the example below shows fluorescence intensity (FI) data from a spectrofluorometry experiment, the descriptive statistics feature may be used for data obtained from other analytical techniques such as UV-Vis spectroscopy and HPLC. As an example, we will use the “Descriptive Statistics” feature on the data for 0.2 ppm. The measurements were obtained in replicate (n = 5).
Step 1:
Tabulate the data as shown below with the replicate measurements in vertical form.
Step 2:
Select the “Data” tab and click “Data Analysis”.
Step 3:
Select “Descriptive Statistics” and click “OK” to open the “Descriptive Statistics” window.
Step 4a:
With the “Descriptive Statistics” window opened, click the small box next to “Labels in First Row”.
Step 4b:
Click the small square to the left of “OK” and select the data points for 0.2 ppm (include the first row, ie. the label).
Step 4c:
Click the square below the “X” button to return to the full “Descriptive Statistics” window.
Step 4d:
Under “Output options”, select the circle next to “Output Range” and then click the button highlighted below.
Step 4e:
Select any empty cell (Cell B9 in this example) and press the button highlighted below to return to the “Descriptive Statistics” window.
Step 4f:
Click all four boxes highlighted below.
Note:
Confidence Level for Mean is set to 95% by default. You may change this to 90% or 99% if you wish by simply deleting “95” and replacing it with the desired value.
Step 4g:
Click OK to generate the table below for the 0.2 ppm data which should look like the image shown below.
Note:
The Coefficient of Variation (Cv) / %Relative Standard Deviation (%RSD) is not calculated by “Descriptive Statistics”. However, given that “Descriptive Statistics” calculates the sample mean and the sample standard deviation, you may easily calculate the Cv / %RSD on Excel.
where s = sample standard deviation and x̄ = sample mean.
Note:
You may also adjust the number of decimal places using Right Click > Format Cells.
Use the “Descriptive Statistics” Microsoft Excel feature on the data for 0.3 ppm and 0.4 ppm and see if you can reproduce something similar to the two tables below.
Using Microsoft Excel for data analysis is one of the fundamental skills you, as a pharmacy student, are expected to know. Pharmacy students need to understand how to analyse data across many different fields – from pharmaceutics and biopharmaceutics to pharmaceutical calculations and pharmaceutical chemistry – descriptive statistics in Excel being just one of those fundamental skills.
