Google Sheets: Basic Functions – Cole Davis

 This is the second tutorial about Google Sheets by Cole Davis. For part one, click here.


INTRODUCTION

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.

blank spreadsheet.png

Easy right?


MATH OPERATORS

Standard operators

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’. 


AVERAGE

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.

results

That would have taken forever.

 


COUNTA

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.


COUNTIF

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.

 

File_000

Bio:

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.


 

Creating and Using a Q & A in Google Slides – Paul Chavez

Creating a question and answer in Google Slides provides an extra tool for teachers and presenters to utilize for discussion in the classroom.

Steps

  1. Select a previous existing or create a Presentation in Google Slides
Untitled drawing (1)
To create a new presentation go to slides.google.com and click “Blank”

To begin the process of creating a Question and Answer response system, you must first have a specific Google Slides Presentation that you would like the Q & A to be linked to. 

     2. Start a Q & A by going into “Presenter View”

Screenshot 2016-08-29 at 10.30.37 AM  Screenshot

Once you have your presentation opened, click on the drop-down menu by the “Present” button in the upper right corner. Choose the option “Presenter View” from the drop-down menu. When the Presenter View window opens click “Start new” under “Audience Tools”.

 

      3. Tools to use

Once you have your Q & A created there are some features to keep in mind while using it. You can choose which slide to feature the Q & A link by using the drop down menu on the left side of the presenter view window. You can also filter the audience of the Q&A by selecting who can view by clicking on the drop down menu that follows “Accepting questions from” in the presenter view. The speaker notes tab in the presenter view allows you to store various information or data that you may need during a presentation.

Screenshot 2016-08-29 at 10.37.35 AM

       4. Asked Questions

Questions or comments that have been posted on the link provided can be viewed from that page or from the presenter view under “Audience Tools”. These questions or comments can be displayed during the presentation if the presenter wishes to do so by clicking the “present” button under the desired Question. Questions can be submitted anonymously by anyone who has the web address to the Q & A page or can be recorded by the email of the user.

 

Bio

Paul Chavez – Student

 

Paul (@ChavezHelpDesk) is a senior at Ash Grove High School. He is involved in Student Help Desk and Beta Club. He is also on the football, basketball, and baseball teams. He is one of nine children in his family. In the future, Paul will be attending college at a college that is yet to be determined.

20160908_100249.jpg

How to Upload and Edit a video using YouTube – Mason

Have you ever wanted to make YouTube videos but could not because of the high priced video editing software’s?  Did you know that you can do everything a video editing software can do just using YouTube? YouTube gives its users the capabilities to make/crop their own videos for free. I will show you some steps on how to successfully do this so many of you can save money.

Steps:

1. Once you are logged into your email, you will need to click the upload button if you want to begin cropping your video. ezgif.com-video-to-gif (1).gif

2. From here you will see an upload page where you can either select a file or drag a download into the Dropbox.

ezgif.com-video-to-gif (3).gif

3. Once your video is done processing, go to the bottom right of the screen and click on video manager. Video Manager is the editing tool for any video you make. All your videos that have been uploaded can be re-edited and uploaded through here.

ezgif.com-video-to-gif (4).gif

4. Click edit to the video you want to use

ezgif.com-video-to-gif (5).gif

5. Now you will have all the tools to edit your video. The video link provided below shows the options you can use to help make a video

6. The final step is to click Publish. The video will be shown on your channel after this step

ezgif.com-video-to-gif (6).gif

Hopefully these 6 easy steps will help you figure out how to make your own videos at no cost. YouTube is not only for watching videos, but it is also a handy tool. Thanks for viewing/watching and now you can start your YouTube journey!

 

___________________________________________________________

Bio

IMG_0844Mason Keith (@maskeithHD) is a Senior at Ash Grove High School. He is a member of the Ash Grove Student Help Desk. Mason plays baseball and football and is also involved in BETA, FBLA, and STUCCO. He plans on attending OTC and then Missouri State pursing a career in Business.

How to Efficiently Use Google Drive

How to Efficiently Use Google Drive

By: Nick Manning

Google Drive – Folders

Using Folders in Google Drive are an essential tool to staying organized and avoiding clutter.

  1. To do this, you would go to your Google Drive, (by going to drive.google.com in chrome) and click the new button on the left tab bar. When you do this, a pop-up should show up and you would click folder.
  2. After you do that, a pop-up will show up where it prompts you to name the folder.
    1. Tip: I recommend having a folder for each class, so do this for each of your classes.
  3. Now once you have those, whenever you make a document for a class, you simply right click on the document and choose, “move to” and then specify the folder.

ezgif.com-video-to-gif (3)

When finished, your folders should look something like this

Screenshot 2016-08-15 at 10.35.02 AM

Google Drive – Deleting Old Files

This is a fairly easy and common step in keeping your google drive orderly.

  1. When you think you do not need an old file anymore, all you have to do to delete it is right click on the file and choose remove.

    ezgif.com-video-to-gif.gif
    Tip: To delete multiple files at once, just click off of a file and drag your mouse across the files you wish to delete. This selects multiple files allowing you to interact with multiple files at once.

Google Drive – Starring Important Files

Starring important projects or files is very similar to deleting files

1. The only difference is that you choose, “add star” instead of remove.

ezgif.com-video-to-gif (2)

Google Drive – Recent Tab

Now over time, you’ll most likely to forget to put a document in one of your folders. So instead of spending precious time looking for said file, you can use your recent tab to better locate those lost files.

1. This tab will be in the left tab bar, and once you click on it, it will take you to the recent page. This page has the files sorted by date accessed, the most recently accessed files at the top and descending by last accessed.

ezgif.com-video-to-gif (1)

Google Drive – Name Files

In Google Drive, naming files are an important part of staying organized.

  1. To name a file you need to open/make a file
  2. Next no matter the file, the Title/name of the file will be in the top left of the page. To change it, all you have to do is click on the, “untitled” and type in your desired name.

    ezgif.com-video-to-gif (4).gif
    Tip: Name files intelligently by abbreviating class names for easy searching. For example, abbreviate English to Eng. or Algebra to Alg.

Bio – Nick Manning

20160830_102530

Nick (@ManningHelpDesk) is a Junior at Ash Grove High School and a student in the ASSIST help desk. He is brother to Lindsay Buckley, (@Lbuckley08) the Librarian at the Ash Grove School District in southwest Missouri. His dream is to attend college for computer science and make innovations in the computer and technology industry. His Google Apps Jam is working with Google Forms and creating polls and tests.