[MUSIC] One of my favorite things about Google Sheets is how easy we can perform basic calculations using functions and formulas. A function is a built-in operation here in Sheets, such as adding or subtracting, which can be used to calculate data. A formula combines one or more spreadsheet functions to obtain a specific result. I like the QUICK SUM feature because I don't even have to enter a formula. All right, so let's highlight some data on our sheet to analyze, and we're going to pick the budget column. Open the QUICK SUM window and check out some different sum options here. Sheets is going to guess the formula that you're most interested in. If you click on the drop-down, it will expand and give more aggregate stats. For example, we can see the sum, average, minimum, maximum and how many unique budget cells there are in this column. All right, so now we're going to start with some simple formulas, using functions like sum, average and count. The SUM function returns the sum of a series of numbers and or cells. We're going to sum up the Box Office Revenue column of all the movies. To do that, let's type =SUM, open parenthesis. Now select all the data in this column and close parentheses. Hit Enter and there we go, the COUNT function is used to return the number of numeric values in a data set. We're going to use this function to find out how many movie data entries are in our sheet. To get a COUNT, type =COUNT and select the data range you want to count. The COUNT function counts all numeric values in a data set including duplicates. Now, let's use the AVERAGE function, let's say we want to know the average budget spent for all of these movies. Type =AVERAGE, open parentheses and now select all the data in this column and close your parentheses. Go ahead and hit Enter, there we go. We'll learn how to count just unique values in a little bit. We can use the MIN and MAX functions in sheets to observe our data range here. You'll enter =MIN, open parenthesis, enter the budget column and close parenthesis. And for MAX, enter =MAX, open parentheses, budget column, close parentheses. Go ahead and change this cell's format into currency. Go to Format > Number and select Currency. As expected, MIN returns the minimum value and a numeric data set. And MAX returns the maximum value in a numeric data set. Let's look at COUNT again in relation to COUNTA, COUNTUNIQUE and COUNTIF functions. The main difference between COUNT and COUNTA is COUNT gathers the number of numeric values. And COUNTA gathers the number of values. We can also use COUNTA if we want to know how many total movie data entries we have in the sheet. Instead of picking a numeric value column, we can pick the Movie Titles column this time. So enter =COUNTA and Movie Title column. If we have duplicated values in our spreadsheet, they would still count using the COUNTA formula. To make sure we're only counting unique values in our data set, we would use the COUNTUNIQUE formula. COUNTUNIQUE counts the number of unique values in a list of specified values and ranges. Let's check it out, so enter the formula COUNTUNIQUE and our data range, in this case the entire column. There we go, we'll use the COUNTIF function to quickly count how many items in a range meet a given criterion. So, in our Genre section here, I can use COUNTIF to pull out how many movie genres there are. Let's create a new column to start our analysis. There we go, let's enter the COUNTIF formula and we need to point towards all the genres. So select the genres column, after the cell range we have to add which cell value it's comparing the data to, there we go. Now let's use the fill down feature to apply the same formula to all the genres, Sheets will pull out the unique count of each genre. If you want to use the SUMIF function in Google Sheets, you need to define which ranges we want to sum and the criteria and or criterion. The range in the formula is defined as a cell range where you want to sum values in Google Sheets. The criteria range is the range which we want to filter for certain values. And the criterion is the value which we want to take out from the criteria range. Okay, so this process is the same for AVERAGEIF. Instead of summing up every entry of Comedy, let's put them all together and find an average for them. Let's create a new column and enter =AVERAGEIF. Enter the column, select the genre, then enter the budget column. Go ahead and format the cells into Currency. Go to Format > Number and select Currency. Okay, if you have a formula integrated in Sheets and you want to apply it throughout your table. There are a couple ways we can duplicate the formula without manually have to recreate it each time. Click on the bottom-right handle of the cell here, and that formula logic will now apply in all the cells in the column, sweet. Alternatively, we can grab that cell handle and drag it down our table, there we go. When we let go, that formula logic will be applied. Absolute references should be used when you want to lock in the range of data your formula points to. It's going to be helpful if you're going to drag your formula down or across. Or copy and paste the formula for use in a different cell location in your spreadsheet. Without absolute references, your cell range arguments are relative. And will continue to change as a formula is dragged around a sheet. So let's do an example, let's go back to our AVERAGEIF. The cell use in a condition might change but, again, the range should not. We drag the formula to the right column, for example. We notice that the formula is now referencing a different rectangle of data. You can create absolute ranges by locking both rows and columns. So our formula will remain correct as we use the Fill Down feature. If we're going to lock in the genre values, we also want to add a dollar sign in front of this cell. Here we go, now if we drag this formula out to the right. And we enter that cell, we see that same genre data range. The last thing I want to say about the COUNTIF, SUMIF and AVERAGEIF functions are that they make quick calculations very simple within a sheet. For example, I can use COUNTIF to very quickly see how many comedy movies there are. SUMIF tells me that the total revenue for comedy movies and AVERAGEIF lets me know the average revenue for these comedies. Practice with these three functions to amaze your peers in your next meeting. The IF function is invaluable if you have large data sets. It helps you create a rule that calls out relevant data based on your parameters. For example, if I want to create a new column that highlights Hollywood blockbusters, I can create a new rule that pulls out films that grossed more than 50 million. Let's say we want to create a new category called Blockbuster in this column. So what is our rule? Let's say IF this film's revenue is greater than 50 million dollars, then it is a blockbuster and we will display the value Yes. If it is not, the cell value will be No. Nice, that looks good. Select and double-click the bottom of the cell here and there you go. All right, let's learn how to use the AND, OR and NOT functions to create logical arguments. If you want to create more than one condition, you can go beyond the IF-THEN function. The AND function ties together multiple rules. In addition to blockbusters, we want to find out how many comedy blockbusters there are. So we need to enter our IF AND function and we want to call out the blockbuster column and the example genre comedy. If the values are true, this new data column will tell us which of the movies are both blockbusters and of the comedy genre. Likewise for the OR and NOT function, functions like DATE, YEAR, MONTH, HOUR, MINUTE. All these functions allow you to break out certain components of your data. We'll add a link to the full list, but here's a quick example. Let's bring our release date column here in a empty sheet. Here, we can see the value is composed of a year, month and day. We're going to use the YEAR function to isolate just the years. Why is this useful? Because instead of going in and changing the data manually, we can break out specific data components. [MUSIC]
