Notes on Bar Graphs and Statistical Calculations in Excel

Introduction

  • Purpose of the section: Learn to calculate averages and 95% confidence intervals, create and customize bar graphs in Excel.
  • Importance: Excel skills are fundamental in BIOL 1213 and will be used repeatedly.

Calculating Averages in Excel

  1. Selecting Cell: Click on the cell where the average will appear.
  2. Formulas Tab: Navigate to the Formulas tab.
  3. Insert Function: Choose Insert Function to view available formulas.
  4. Choose Average: Select the Average formula and click Insert Function.
  5. Entering Values:
    • If Excel auto-populates values, delete them.
    • Input values manually by:
      • Highlighting cells, or
      • Typing a range (e.g., C3:C14).
  6. Finalize: Click Done to display the average in the selected cell.

Calculating 95% Confidence Interval in Excel

  • Definition: A measure of your confidence in the average estimate; larger deviations indicate lower confidence.
Steps to Calculate the 95% CI:
  1. Alpha: Set alpha as 0.05 to represent a 95% confidence interval (1-0.05).
  2. Count: Determine the number of estimates (5 for lab). Use the steps below to count automatically:
    • Click on the desired cell.
    • Navigate to the Formulas tab and select Insert Function.
    • Choose the Count function.
    • Enter the range for counting (e.g., C3:C14).
  3. Standard Deviation: Represents error around the average value. Follow similar steps:
    • Click on a cell and go to Formulas tab.
    • Select the STDEV function.
    • Input the data range (e.g., C3:C14).
Calculating Confidence Interval:
  1. Insert Function: Open Insert Function and select CONFIDENCE.NORM.
  2. Input Parameters:
    • Alpha: Manually enter 0.05.
    • Standard_dev: Reference the cell with the calculated standard deviation.
    • Size: Use the cell with the count or manually enter 5.
  3. Final Step: Click OK to get the 95% confidence interval value.

Creating a Bar Chart in Excel

  1. Selecting Data: Highlight all data points, including headers.
  2. Insert Chart: Go to Insert and select desired chart type (2-D clustered column).
  3. Editing Default Graph:
    • Remove title: Right-click the textbox and select Delete (titles in captions below).
    • Remove legend: Right-click the legend and delete it (keep if multiple dependent variables).
    • Remove gridlines: Right-click the lines and delete to avoid clutter.
Axis Customization:
  1. Labeling Axes:
    • Use Chart Design > Add Chart Element > Axis Titles for horizontal and vertical titles.
    • Include units in titles (e.g., time (min)).
  2. Formatting Axis Lines:
    • Click an axis and modify the Font and Line colors to black.
  3. Adjusting Line Properties: For one dependent variable:
    • Ensure lines and markers are set to black, and check for shadow effects.
  4. Font Size: Increase size for clarity if necessary.

Finalizing Graph Appearance

  1. Remove Border: Double-click the chart. In Format Chart Area, select No Line under Border.
  2. Adding Tick Marks: Access Format Axis tools when clicking the numbers along each axis.