Conditional Statistics 1

Description

Conditional statistics are statistics of a subset of the data, a subset that meets certain criteria (“conditions”). In this screencast we will learn what native Excel functions exist for conditional statistics and how to calculate conditional statistics more generally, using the FILTER function to feed the data subset into the function that calculates the statistic.

Learning Goals

Learn the syntax of Excel’s COUNTIF and AVERAGEIF functions.
Learn how to use FILTER to calculate any conditional statistic

Tasks

List the unique semesters in the dataset (using UNIQUE)
Use COUNTIF to count the number of observations for each semester
Use AVERAGEIF to calculate the average error for each semester
Use FILTER + AVERAGE to calculate those same conditional averages
Use FILTER to calculate the median and the 25% trimmed mean for each semester
Use COUNTIF in a cell formula to calculate the fraction of errors that are negative

By the end of the screencast your spreadsheet should look something like the image below.

Sulfate data with a table of count, mean, median and 25% trimmed mean of the error by semester, and the fraction of errors below zero (86.5%)

Filtering Data Conditional Statistics 2