<style> .reveal { font-size: 33px; } </style> # COMP1010 ## 1.2 - Spreadsheets --- ## Poll * What do you use spreadsheets for? ("Nothing" is a valid answer.) --- ## Motivation * Handbook * Useful * An opportunity to lay the groundwork for programming --- ## Google Sheets --- ## Introduction to Spreadsheets * Lists * Tables * Each **cell** has a name, and contents * Functions * Equality * Fill down * Absolute references * Strings * (Demo) --- ## Working on Spreadsheets Provided 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 <a href="https://drive.google.com/drive/my-drive">https://drive.google.com/drive/my-drive</a>.) --- ## Examples/Activities * Grade Book * Splitting the Bill --- <!-- todo make these come up one by one in fragments--> ## Grade Book * Set up: Create unique student numbers * Sum marks (Ctrl-D to fill down) * Conditional formatting on totals * Get the maximum and minimum mark achieved --- ## Functions * Name * Parameters * Return type / Return value --- ## Functions | | | | -------- | --- | | **Name** | SUM | | **Parameters** | values to add together | | **Return type** | number | --- ## Functions | | | | -------- | --- | | **Name** | MAX | | **Parameters** | values to consider | | **Return type** | number | | **Return value** | the largest value of those provided in the parameters | --- ## Functions * Things going in, things coming out. * What we need to know, \<the job we do\>, the results we produce. * Going for coffee. --- ## Conditionals ```excel =IF(logical_expression, value_if_true, value_if_false) ``` --- ## Logical Expressions<br>(in spreadsheets) | Symbol | Meaning | | ------ | ------- | | = |equals| | > | is greater than | | < | is less than | | >= | is greater than or equal to | | <= | is less than or equal to | --- ## Conditionals "If the value in cell A2 is greater than or equal to 50, then this cell should display PS (for PASS), otherwise, this cell should display FL (for fail)." <br> <span> | | | | ---- | ---- | | **logical_expression** | <span> the value in cell A2 is greater than or equal to 50 <!-- .element: class="fragment" data-fragment-index="2" --> </span> | | **value_if_true** | <span> this cell should display PS <!-- .element: class="fragment" data-fragment-index="3" --> </span> | | **value_if_false** | <span> this cell should display FL <!-- .element: class="fragment" data-fragment-index="4" --> </span> | <!-- .element: class="fragment" data-fragment-index="1" --> </span> --- ## Conditionals * **logical_expression** the value in cell A2 is greater than or equal to 50 * **value_if_true** this cell should display PS * **value_if_false** this cell should display FL <br> <span> | | | | ---- | ---- | | **logical_expression** | <span> A2>=50 <!-- .element: class="fragment" data-fragment-index="2" --> </span> | | **value_if_true** | <span> "PS" <!-- .element: class="fragment" data-fragment-index="3" --> </span> | | **value_if_false** | <span> "FL" <!-- .element: class="fragment" data-fragment-index="4" --> </span> | <!-- .element: class="fragment" data-fragment-index="1" --> </span> --- ## Conditionals <br> ```excel =IF(logical_expression, value_if_true, value_if_false) ``` <br> * **logical_expression** A2>=50 * **value_if_true** "PS" * **value_if_false** "FL" <br> ```excel =IF(A2>=50, "PS", "FL") ``` --- ## Functions | | | | -------- | --- | | **Name** | IF | | **Parameters** | logical_expression, value_if_true, value_if_false | | **Return type** | boolean | --- ## Grade Book (continued) * Pass/Fail * Give HD, DN, etc * Get list of failing students * Graph how many students got each total * Graph how many students got each grade --- ## AND and OR and NOT <img src="https://i.imgur.com/8PxIcOM.jpg" alt="https://i.imgur.com/8PxIcOM.jpg" width="200"/> 1. and and or and not (what we expect) 2. and and or and not (spreadsheet) --- TODO Lab/tutorial exercises Gradebook where you need to pass the exam as well as pass the overall grade in order to pass. --- ## So Many Functions!! * Insert->Function * [https://support.google.com/docs/table/25273?hl=en&ref_topic=9054531] --- ## Feedback Lecture: 1.2 Spreadsheets - Grade Book  [Link to feedback form](https://forms.gle/NdAhw7ZMJ2eBEydd7)