Learn how to use IF functions and their variants to analyze a crime dataset. COUNTIF counts records matching one condition, while COUNTIFS combines multiple conditions like precinct and time. SUMIF and AVERAGEIF let you total or average values for specific groups.
Chapters
Nice work summarizing the data. You probably noticed that these summaries don't help us much yet. They would be more useful if we could calculate summaries based on different groups of data, such as by precinct or crime type. In this video, you will learn about functions that only include data if they meet certain conditions. Let's take a look. To count the number of crimes by precinct or compute the average time by precinct, we first need a list of all the possible values of that column. This is a perfect use for the unique function. It takes a set of data and returns an unduplicated list of the distinct values in the column. For example, calling the unique function on the C column will give you a list of all the precinct names in our data set, listing each precinct only once. Now that you have a list of all possible values, you can easily compute summary statistics for groups within the data set. For example, you might want to count the number of records for each group, such as the number of crimes by precinct. You can use the countif function to do this. It takes two the range refers to the cells that you want to count, and the criterion refers to the value that you want to count. So you can use the unique list of precincts and the countif function to count the number of crimes per precinct. You could also count the number of crimes that occurred after 8 o' clock by changing the criteria to all crimes with a time greater than 2000. Recall that our data lists times in 24 hour format. We can also use the averageif and sumif functions to compute groupwise summary statistics. These functions take three arguments instead of the two required by countif. They require a criteria range, a criterion, and a range to which they apply the function. For example, you can calculate the average time at which a crime occurred by specifying the criterion range as the column that contains precinct names, the criterion as the name of the precinct of interest, and the column containing times as the range to which to apply the function. Similarly, you can calculate the sum of crimes in various precincts by specifying the range of columns that contain precinct names, the criterion to evaluate, and the column containing counts of crimes in those precincts as the range to apply the sum function. So that will help us improve the insights from our data. But what if you want to restrict your counts, averages, or sums based on multiple criteria? Fortunately, Spreadsheets has functions that do just that. Let's start with the countifs function. It takes a practically unlimited number of pairs of arguments, though we typically provide no more than three criteria. And it allows for multiple criteria. You can even apply different criteria to the same range as you might need to if you only wanted to count crimes between 8 o' clock and midnight. Let's consider an example. Say you want to count the number of crimes in the west precinct that occurred after 8 o' clock in the evening. You would call the countifs function, specify the first range, then the criteria to check, then the second range and the criteria to check. We can use the averageifs function. Similarly, the order of the arguments in the averageifs function is a bit different from that of countifs. For example, the first argument of averageifs is the range of cells to include in the average, not the first criteria range. It also takes pairs of ranges and criteria, and allows for multiple criteria for the same range. Now you're armed with multiple functions for creating and counting groups of data. Let's get some.
