Using formulas and functions - Google Sheets Tutorial

- [Instructor] Using formulas and functions in Google Sheets allows you to perform basic math calculations such as adding, subtracting, multiplying, and dividing contents of cells. You can also perform more sophisticated calculations with Google Sheets' built in functions. A formula is the mathematical equation that's used to calculate the result. A function is used inside a formula and is sort of a code that's built into Google Sheets that makes the formula much easier to write. Let's walk through how to create a formula that uses one of the built-in functions available in Google Sheets. I'll show you using this sales spreadsheet. This file is named formulas and you'll find it in the exercise files chapter six folder. The first thing you'll want to do before you start adding formulas to your spreadsheet is make sure the formula bar is selected. Click the view menu and make sure there's a check mark next to formula bar. As soon as formula bar is selected, you'll see a new bar at the top of your sheet with an FX icon to the left of the bar. This formula bar allows you to see the result of the formula you enter in the cell while seeing the formula itself entered in the formula bar. I'll select the cell where I'd like to add the formula and perform my calculation. I'll use my items sold by month sheet to show you how this works. I'm going to add a formula to find and display the sum of all the items that were sold in January. I'm going to add this formula at the bottom of the January column so I'll select cell B6. The next step is to type an equals sign as the first character in the cell. Typing that equals sign tells Google Sheets you're about to write a formula. If you know the name of the function you'll be using in your formula, type it in here. My function will be the sum function so I'll start typing sum. As you can see, Google Sheets starts suggesting a list of formulas starting with S and their brief descriptions just as soon as I start typing. I'll select sum, sum of a series of numbers and/or cells. Now Google Sheets shows a box displaying the name and the syntax of the formula I'm building. I can even click this little learn more link to go and open a full article about that function. Once you understand how the function works, you can enter the values by clicking on the range of cells you want to add to the function. A range in a formula means the cells that the formula and the function refer to to return the calculation. When editing a formula, a very small light gray bracket will appear next to your cursor where you're likely to need a range in that formula. When you see that signal, you can select a range of cells using your mouse. I'll select the three numerical values in column B for my sum range. You'll see in the formula that Google Sheets denotes this range as B2:B4. This formula means the sum of cells B2 through B4. I need to add a close parentheses to signify that I'm done with my formula and to perform and display the calculation on the spreadsheet, I'll press the enter key on my keyboard or I'll just click elsewhere on the sheet. I can see that the sum of these three cells is 109. If I click on the cell again, I can review that formula or change it in the formula bar above. If I want to edit my formula, I can click in the formula bar and select the values or even the whole formula to make a revision. I'll leave mine as is. Here's a neat trick that you can do if you need to perform the same calculation on adjacent cells. In this spreadsheet, if I want to replicate this formula for the February, March, and April data, I select the cell, then I click on the small blue square in the bottom right corner of the cell that contains the formula and drag that formula over the next three cells. The formula will copy using similar ranges for the column it has been copied to. For example, when I click to see the formula for February's total, I see that the range in the formula bar has been adjusted to use cells C2 through C4. You can also select non-adjacent cells for your range. I'll show you how on my average price sheet. I'd like to find the average amount of items sold in February. I'll click on cell I23 since that's where I'd like to place the formula and I'll add that equals sign to the cell. I'll start typing the average function, or average, numerical average value in a dataset. Holding down my control key on my PC keyboard, or my command key on my Mac, I'll only select those price cells for those that are light bulb item types. So I'll select these seven cells. D2, D4, D5, D17, and D18. I'll put a close parentheses to finalize my formula and press enter on my keyboard. And I see that the average price of light bulbs sold was $44.03. Google Sheets supports most functions available in other spreadsheet applications such as Excel. You can click on the function icon on the right side of the toolbar to see a huge list of all the functions available in each category.