You can combine these formulas for more advance analysis like confidence intervals.

Calcuating Confidence Intervals

Confidence intervals are covered in the Population Statistics (remote) lab as way to quantify uncertainty. They are also an important component of many graphs but are often hard to show (see instructions with bar graphs further down).  Key points to remember

• Confidence intervals are a range that surrounds the estimated mean of your data; theory and simulations show that 95% of the time  you construct one of these intervals  the true mean of the data will fall in this range.

  • Since the mean is either in the interval (or not), this means that on average if you made confidence intervals 20 times the true mean would be outside the interval once (see the simulation for more help!)

• Confidence intervals, therefore,  must have an upper and lower bound that are equally spaced around your estimate of the mean!  This is an easy way to check your calculations.

To produce confidence intervals, first remember what we need:

• the mean of the data

• the standard deviation of the data

• the size (n) of the sample

We can use these respective functions in Sheets to find these values:

• average

• stdev

• count

So to get the lower bound (remember, confidence intervals are a range, update the following formula to use your data:

=AVERAGE(data)−2*STDEV(data)/SQRT(COUNT(data)) 

Similarly, the upper bound can be found with 

=AVERAGE(data) + 2*STDEV(data)/SQRT(COUNT(data)) 

Note the above is giving you the actual confidence interval; below we will note using just the values (standard error or 95% confidence interval) to make an error bar.

Visual Summaries (Graphs)

To produce visual summaries, select the rows you want to include and select Insert Chart (highlighted).  You can then manipulate the type of plot you produce and how it uses the selected data. Examples are given below.  

Scatterplots

Scatterplots are a common way to visualize relationships between two continuous variables. 

This produces a chart with editing options.

In our case a scatter chart may be more appropriate (bar and column charts in general use a lot of color to mask a lack of information).  We can make this change (see Chart style tab) and update our title (see Chart & axis titles tab).  We can delete the legend (see Legend tab, set position to "none") since its not needed for this graph.

Histograms in Google Sheets

Histograms are covered in the Population Statistics (remote) lab and are useful for considering the distribution of continuous data.  See an example in the Histograms and more functions tab.  First, enter your data in a new Google Sheet.  For a histogram you will only have data in one column!

Next, select your data, insert a chart, and select histogram.  Edit the resulting chart labels as needed.  Note that if your data is very small (or large), you may need to change the units manually to ensure x-axis bin labels make sense because the default format only allows for 2 units after the decimal place. For example, if you enter your been beetle weights in grams, all bin labels may be .00.  If you manually convert these to milligrams (divide by 1000) the bin labels will be more useful.

Alternatively, you can calculate summary statistics for various groups (like the mean and standard error) and use them to produce bar charts with confidence intervals.    Find the table below in the 2 groups example: t-tests, bar chart, histograms  tab. You can then select just the Control and mean portions and put them in a bar chart (or even a scatter plot, like I show below). Next, add another series focused on the treatment.  

You can clean up titles and axes here, but the next step is to go to Customize>Series> and then for each series (Control and Treatment) add in  a Constant error bar using the value you calculated for your 95% CI.  

This works for wide format data, but another option is to use pivot tables to summarize long format data (see more below)

Adding multiple data series to a Google Drive graph

To add multiple data series, let the first column be your desired independent variable (for x-axis).  Dependent data series can be placed in adjacent columns. All columns can have identifying labels.  Select all the columns you desire to graph, then insert a chart using the same procedure from the Summarizing Data Lab.  The only modification required is that you may need to specify which series is the X-axis depending on your browser defaults.

Alternatively, you can make a single plot, then return to the Setup portion of the Chart Editor and add additional series.

After you create it, the Pivot table editor opens. You can use it to make tables with various Rows and Columns, which can be filled with different Values. You can initially drag your data columns into these groups, but for more advanced work you may need to use the Add button. You can also filter the table to only include certain rows of the original data.

Here I made a group for each row.  I then made values that included the average mass (by selecting average for the Summarize by). Note you can also turn row and column totals on and off.

I could use this summary to immediately make graphs.  Like our earlier appraoch, the tables and connected graphs will update if we change the data; if we add more cells to our data, though, we'll need to change the range  (see above image and caption).  Once created, you may need to change the Switch rows/columns and  Use columns.. as headers options to get 2 series.  Sometimes not selecting the pivot table header row also helps.

I  can also add another summary focused on the same outcome in the Values area by using the Add button ; dragging won't work as the variable is already there. For example, I can add counts and standard deviations for each group, then calculate the standard error of the mean and the ~95% confidence interval.  See the sheet for examples and formulas. You can then add this information to the graph for each series.