This is the second tutorial about Google Sheets by Cole Davis. For part one, click here.
Thanks to the prior tutorial, we now know how to enter raw data and format it. Now we can begin preforming calculations, such as finding the average score of tests, with our data. In order to do this, we need to start referencing cells and using things called functions. The functions we’ll use today include COUNTA, AVERAGE, and COUNTIF. We’ll also review some basic math operators.
USING CELL REFERENCES
Functions and mathematic formulas require us to specify the address or ‘name’ of the cells we want to manipulate. In order to do so, we need to learn how to refer to cells with the name Google Sheets automatically assigns them.
The yellow highlighted lines in the image below contain letters on the top and numbers down the side. These are used to give an address for each and every cell within the spreadsheet. The red lines are empty cells and the green line is the formula bar.
This concept of assigning an address to points on a grid based on the x axis (left to right) and y axis(up and down) axes might intimidate some, but it is no different than the board game Battleship. You have a grid and two labeled axes. Points on the grid are given a name based on where they intersect the two axes.
These will be used for calculations on our spreadsheet.
Here are some examples of using cell references alongside math operators. Note how the cells referenced can be from anywhere on the spreadsheet.
IMPORTANT: Your formula must begin with an equals sign (=) so Google Sheets knows to make the selected cell the result of your formula.
In this video, I enter a number in some cells and use a mathematic formula to give a new cell the value of the sum of the two other cells. So, at first I enter ‘4’ in cell A1. Second, I enter ‘6’ in cell A2. I then select cell A3, go to the formula bar, and type ‘ = A1+A2 ‘. This sets the active cells value to the sum of A1 and A2 which in this case is ’10’.
Now that we have an understanding of cell references, we can start incorporating functions. There are all sorts of handy functions that quicken the pace or your work. For example, the AVERAGE function automatically calculates the average (mean) of whatever cells you tell it to! Let’s see some specifics…
In the clip below I refer to a range of cells by using the colon ‘ : ‘. This allow you to include the cells between the two you specify. In the example, I refer to the cells B2 through D2.
In order to tell the function what cells you want to change, you must wrap your choosen cells within parenthesis. This step seems like just an extra step at first, but is necessary when more advanced formulas are to be applied.
Formula Used: =AVERAGE(B2:D2)
Those percentages were formatted strangely. Let’s fix that.
There are certain times where the format of your data improves the readability of your spreadsheet. Here is how you can change those percentages to a more familiar format.
Select your cell you want to re-format, click Format, Number, More Formats, and then Custom number format.
Let’s calculate the rest of the averages.
I know what some of you must be thinking. “Do I have to write out that whole formula for each row? That seems like a major inconvenience that should be addressed.” But fear not, there is a simple (and really awesome) feature that automatically applies the formula to the cells you select. Here’s how to do it:
That’s right, all you have to do is hold down the mouse and grab the bottom-right corner of the result cell and drag down the remainder of the rows. By dragging down the corner and applying it, these formulas in the purple column are automatically generated by Sheets.
That would have taken forever.
This function returns a number that represents how many cells have text in them. This can be useful for counting how many students put their name on their paper.
In this example, there is some missing data. Since there is no data there, it isn’t counted.
Formula used: =COUNTA(B2:D6)
Now we have a count of our students.
This formula returns a number that represents the number of cells that meet a condition you set. In this example, the condition is “greater than 75”. So, Sheets goes through the range you specify and counts the number of cells with a value greater than 75.
Formula Used: =COUNTIF(B2:D6, “>75”)
Now we have a count of our tests with a score greater than 75.
Cole Davis (@Cole_Davis64) is a student at Ash Grove High who attends classes at OTC for Computer Information Science. He dabbles in 3D animation in his spare time, and likes to help people make the most out of their computers. It’s not uncommon to find him browsing spicy memes, or making some internet.