<style> .reveal { font-size: 27px; } .reveal div.para { text-align: left; } .reveal ul { display: block; } .reveal ol { display: block; } img[alt=drawing] { width: 200px; } </style> # COMP1010 ## 2.1 - Spreadsheets --- ## Motivation * Handbook * Useful * An opportunity to lay the groundwork for programming --- ## Structure of Spreadsheets Topic * Week 1: Introduction to spreadsheets and functions * Week 1-8: Python, Web Apps, etc * Week 9: Fancier spreadsheets --- ## Google Sheets * In order to create a new Google Sheet document: * [Create new Google Sheet](https://docs.google.com/spreadsheets/u/0/) * In order to use the Google Sheets documents provided: 1. Click on the link to view the document. 2. Check the top-right corner of the screen for your logo, to see if you are logged in to Google. If it says 'Sign In', click the button and use it to sign in, or sign up to Google. (By the end of this step, you should be logged in, and have the spreadsheet on the screen in front of you.) 3. File -&gt; Make a copy. 4. Save the spreadsheet in your Google Drive folder. (You can now access this spreadsheet, and the changes you make to it, from now on, by going to [Your Google Drive](https://drive.google.com/drive/my-drive").) --- ## Introduction to Spreadsheets * Lists * Tables * Each **cell** has a name, and contents --- ## Data Types * Some different data types include: * Number * String (text) * Boolean (true or false)<br><br> * What do they look like in a spreadsheet? * Which data type would you use to store a phone number? * What are some different things we can do with different data types? * What happens if you add two numbers? * What happens if you add two strings? --- ## Functions * Things going in, things coming out. * What we need to know, \<the job we do\>, the results we produce. * Going for coffee. --- ## Terminology * **Parameters:** The information being given to the function. * **Return value:** The information produced by the function. * **Return type:** The data type of the return value. --- ## Functions in a Spreadsheet * You can find a complete list of the functions available in Google Sheets [here](https://support.google.com/docs/table/25273?hl=en). --- ## Grade Book Demo * [Starting point](https://docs.google.com/spreadsheets/d/1YEpRXD-ki25HufADYQPS10ov2ZqHPrDU2nS5_tYxrj4/edit?usp=sharing) * Functions start with an `=` sign <br> | Function | Description | | -------- | -------- | | `= SUM(value1, [value2, ...])` | Returns the sum of a series of numbers and/or cells. | | `= IF(logical_expression, value_if_true, value_if_false)` | Returns one value if a logical expression is `TRUE` and another if it is `FALSE`. | | `= IFS(condition1, value1, [condition2, value2], …)` | Evaluates multiple conditions and returns a value that corresponds to the first true condition. | <br> * Charts and Graphs ---- | Function | Description | | -------- | -------- | | `= COUNTIF(range, criterion)` | Returns a conditional count across a range. | --- ## AND and OR and NOT | Function | Description | | -------- | -------- | | `= AND(logical_expression1, [logical_expression2, ...])` | Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false. | | `= OR(logical_expression1, [logical_expression2, ...])` | Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false. | | `= NOT(logical_expression)` | Returns the opposite of a logical value - `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`. | --- ## AND and OR and NOT <img src="https://i.imgur.com/8PxIcOM.jpg" alt="https://i.imgur.com/8PxIcOM.jpg" width="200"/> ---  ---  --- # Notes * `= SUM(value1, [value2, ...])` * `= IF(logical_expression, value_if_true, value_if_false)` * `= IFS(condition1, value1, [condition2, value2], …)` * `= COUNTIF(range, criterion)` * `= AND(logical_expression1, [logical_expression2, ...])` * `= OR(logical_expression1, [logical_expression2, ...])` * `= NOT(logical_expression)`