Skip to main content
Chemistry LibreTexts

Excel Part 2—Statistical Analysis: Putting the Pieces Together to Determine the Concentration of an Analyte and Evaluate the Precision of the Results

  1. Last updated
  2. Save as PDF
  • Page ID
    279114

    • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Student Learning Outcomes:

    At the end of this activity, students should be able to

    • Use Excel to calculate the result for each replicate.
    • Use Excel to evaluate the precision of these results (mean, standard deviation, % RSD)
    • Use Excel to evaluate the accuracy of these results (90% confidence interval)
    • Provide Spreadsheet Documentation for your work.
    • Use the extra features of Excel to communicate your results clearly.

    The Problem

    Suppose that you are a Quality Control analyst for Yummy Salad Dressings, Inc. and it is your job to determine the % (w/v) of acetic acid in the vinegar used for the Balsamic Vinaigrette.  This is a perfect time to exercise those skills and concepts that you developed in Quantitative Analysis!  You decide to run 4 replicates through an acid-base titration protocol using a standardized barium hydroxide solution (0.1475 M).  The following results were obtained:

    Replicate Sample

    Sample Volume (mL)

    Ba(OH)2 sol’n Vol (mL)

    1

    50.00

    43.17

    2

    49.50

    42.68

    3

    25.00

    21.47

    4

    50.00

    43.33

    Your supervisor needs the following analysis:

    1. What is the mean w/v percentage of acetic acid in the vinegar sample?
    2. Calculate the standard deviation and the % RSD for the results. What do these values tell you about the precision of your analysis?
    3. Calculate the 90% confidence interval for the mean. What does this value tell you about the true value for the (w/v)% of acetic acid in this vinegar?
    4. Express the final result using the mean ± the standard deviation (with the appropriate significant figures and units).

    Design an Excel spreadsheet that will calculate these quantities from your results.

    1. Basic Spreadsheet Design

      You need an organized way to display the experimental results (above), the calculated values for each replicate, and the statistical analysis of the final results (i.e. the (w/v) % calculated for each replicate).

    2.  The determination of the w/v % of acetic acid in the sample is just a stoichiometry problem, so do the first one by hand. 
      1. Write the balanced chemical equation for the acid-base reaction.
      2. If you like, set up a roadmap to see how many conversion factors (or steps) are involved. (This will help you think through the steps that you want Excel to calculate.)
      3. Calculate the w/v% for the first replicate. (This will enable you to check the first result that comes through your spreadsheet.)
    3. Write the formula for each calculation that you want Excel to do. 
      • If you need to use reference information in your calculations (e.g. a molar mass or the exact concentration of a standard solution), choose a place to put this in your spreadsheet.
      • Remember when to use relative references and when to use absolute references.
      • Let Excel calculate the w/v% for the first replicate, and see if it matches the answer that you calculated by hand. This will tell you if your formulas are written correctly.  (It’s easier to fix them at this point.)
    4. Write the formulas for the statistical analysis on the final results of your replicates. 
      • In order to assess the quality of your precision, you need to determine the mean, the standard deviation, and the % RSD for these w/v %. Smaller values for standard deviation and % RSD reveal better precision.
      • The 90% confidence interval will reveal how far we think the mean is from the true value (with 90% certainty). A confidence interval takes the place of % error when you don’t have a reference value for comparison.
    5. Reporting the Final Result

      Report the final result like this: mean + standard deviation, using correct significant figures and units.

    6. Getting the Spreadsheet Ready for your Reader
      • Title
      • Balanced Equation(s) for the method
      • Borders
      • Row/Column Headings
      • Design Details: Remember that you can also use color, make headings bold, center the data in a column, right- or left-justify pieces of information, etc. Make it clear, and make it your own!
    7. Spreadsheet Documentation
     

    Excel Part II: Group Questions

    Part 1: The Chemistry

    1. What is the formula for acetic acid? _____________________
    2. Is acetic acid a monoprotic acid, a diprotic acid, or a triprotic acid? _______________
    3. Predict the products for this acid-base reaction, and write the balanced chemical equation.

     

     

     

    1. Calculate the mass of acetic acid used in the first replicate.

     

     

     

     

     

     

    1. Calculate the weight-volume percentage (%(w/v)) of acetic acid in this first replicate.

     

     

     

     

     

    Part 2: Excel Formulas: Write the formula to calculate each of the following:

    1. # mol barium hydroxide

     

     

     

    1. # mol acetic acid

     

     

     

    1. # grams acetic acid

     

     

     

    1. %(w/v) acetic acid

     

     

     

    Part 3: Excel Statistical Analysis:

    Write the formula to calculate each of the following for the % (w/v) of acetic acid:

    1. Mean

     

     

     

     

    1. Sample standard deviation (of this calculated result)

     

     

     

     

    1. % RSD

     

     

     

     

     

     

    This page titled Excel Part 2—Statistical Analysis: Putting the Pieces Together to Determine the Concentration of an Analyte and Evaluate the Precision of the Results is shared under a CC BY-NC-SA 4.0 license and was authored, remixed, and/or curated by Contributor via source content that was edited to the style and standards of the LibreTexts platform; a detailed edit history is available upon request.