Spreadsheets Tutorial: IF functions

Key Takeaways

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 arguments 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 can also count the number of crimes that occurred after 8:00 o'clock by changing the criteria to all crimes with a time greater than 2000 recall that our data list times in 24-hour form we can also use the average if and some if functions to compute GroupWise summary statistics these functions take three arguments instead of the two required by count if they require a criteria range a criterion and arrange 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 x 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 that 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:00 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 average ifs function similarly the order of the arguments in the average of this function is a bit different from that of countifs for example the first argument of average ifs 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