SPREADSHEETS


 * This week's Period 6: Log in to @https://www.mobymax.com/pa3152 and work on Writing Workshop,** right where each of you stopped last time. You are working at your own pace, but that pace has to be //FORWARD.// Remember: persuasive writing!

//** All classes: SCROLL DOWN to where you ended last class. Follow your teacher and know you will use this web page for the rest of this week. New lessons will be added at the BOTTOM so look for dates! **//


 * For Monday, October 30 and 31: SUB - READ ALOUD to students and keep them together.**


 * TUESDAY, OCT. 31**: What better way to celebrate Halloween like... TYPING TUESDAY! Lessons only, no games or test practice. That next typing test will be here before you know it and you must show progress. Typing.com, school login only.


 * This is Spreadsheets: they must know it, and it is important. Thank you. October 31 plans will be on this page on Tuesday.**
 * The URL for this page is** http://mrstelnock.wikispaces.com/SPREADSHEETS - please WRITE IT ON THE BOARD FOR STUDENTS to go to and feel free to bookmark. Thank you.

Hello, Ladies and Gentlemen,

Because the Class Page will not let me update anything this entire weekend, I had to make this new page for your substitute to work with and you all must do the work that I assign because tests (including for the vocabulary that you were to write in your notebook last Thursday) are coming soon. If I could be there, believe me, I would.


 * Oct. 30: Let's get started in Excel!** Last Thursday, you were to get the Spreadsheet Vocabulary in your notebooks.

Phrases to **memorize,** so read them together aloud three times each and then WRITE THEM into your notebook:
 * Spreadsheet software is **as important as** Word processing software.
 * Math actions in spreadsheets are called **formulas.**
 * **Never** type numbers (answers) in formula rows or cells or the spreadsheet will not be able to automatically recalculate answers if changes are made in the spreadsheet.
 * All formulas start with an **equal sign**.
 * There are **no spaces** in formulas.

Let's start with something that we can all relate to: **grades**. We are about to create a simple grade book set-up in Excel, so LEAVE THIS TAB OPEN to refer to the on-going directions.

Use recommended settings and open a BLANK WORKBOOK/BLANK SPREADSHEET and //**immediately save as "Excel Grade Book."**//
 * 1. Open Microsoft Excel** (Start > Programs > Microsoft Office > Excel [green icon]
 * To put the shortcut on your desktop, follow the path above, **right click** on the program and select Send to > Desktop Shortcut

//**2.**// **Note** that the spreadsheet is arranged in columns (letters) and rows (numbers). The boxes at all of these letter/row intersections are called - **CELLS.** Tell your neighbor all of this. Now.


 * 3. Click on cell B10**. Now, look at the **Name box - the white field in the upper left (**a vocab word**!).** Does it say B10? Click, DO NOT TYPE, on other cells. See how the name box changes to the name of the cell that you are in? Now, type H15 into the name box and hit Enter. Where are you now? It is EXTREMELY important for you to get comfortable going to the correct cells.

Now that you have had a couple minutes to familiarize yourselves with the spreadsheet cells, let's move on to data entry.


 * STAY TOGETHER.**

Using the following directions, you will recreate this information in your Excel spreadsheet, SAVING EARLY AND OFTEN by using File >Save, or, preferably, Ctrl + S.

Start in cell A1 and try NOT to use your mouse for the rest of the spreadsheet. Type, "Students" then hit TAB key on your keyboard. Where are you now? In cell B1.

In B1, type, "Test 1." TAB.

In C1, type, "Test 2." TAB.

In D1, type, "Test 3." TAB.

In E1, type, "AVG" (means average)

Use your ARROW KEYS to get to cell A2. Type, "Alex," and then hit ENTER. Where are you now?

In cell A3, type, "Debbie," and hit ENTER.

In cell A4, type, "Molly," and hit ENTER.

In cell A5, type, "AVG."

Using ARROW KEYS, type in the exact grades for cells B2 through D4. DO NOT TYPE ANYTHING ELSE IN E column or Row 5.


 * NOW, STOP**. Looking at the spreadsheet, what information will column E tell us? The average for what? Teacher, confirm. How about Row 5? What information will we learn there? Teacher, confirm.

.


 * Nov. 1**: Happy November, All! Teacher: please note where each class ends so you know where to begin each day. At the end of Friday, let me know where each class stopped so I know where to begin on Monday. Thanks so much!

Let's continue - Teacher - please be sure you read this to students so that they can be looking at their spreadsheets and focus. STAY TOGETHER - thanks!

//**OPEN your "Excel Grade Book" spreadsheet from Monday, pictured above.**//

The **E column** will show us EACH STUDENT'S AVERAGE for the marking period.


 * Row 5** will show us the CLASS AVERAGE for every test.

Why is it important for a teacher to know the **class average** of every test and assignment? Would it help you, as a student, to know the class average for each assignment?
 * Discussion Topics Time**:

Let's look at cell E2. That cell will tell us Alex's average for the marking period. That cell will have an answer, thus, a FORMULA is needed. **NEVER use a calculator to find answers in a spreadsheet.** If you just add Alex's three scores and divide by three, you will certainly get a number. But if a test is added, his average will be wrong. By typing in numbers in cells that should have FORMULAS, you are preventing the most wonderful feature of spreadsheet software... (cue dramatic crescendo music) ...**Automatic Recalculation**!


 * Say aloud,** "Never use a calculator to find answers in a spreadsheet." Tell your neighbor. I'll wait. Tell another neighbor. Got it? Ok, let's move on.

Because you were asked to memorize these, and you were told to write them in your notebook, read all aloud, together:
 * Spreadsheet software is **as important as** Word processing software.
 * Math actions in spreadsheets are called **formulas.**
 * **Never** type numbers (answers) in formula rows or cells or the spreadsheet will not be able to automatically recalculate answers if changes are made in the spreadsheet.
 * All formulas start with an **equal sign**.
 * There are **no spaces** in formulas.

**REMEMBER:** //Save early and often// as you work!

The E column and Row 5 are "answers" cells, which means that each cell will have a FORMULA typed in it. From the bullets above, raise your hand and share what you know about formulas.

In cell E2, type =average(B2:D2) and hit enter. The result of the formula is 89.66. Click on E2 ONE TIME, just to select it, not to get in the cell. Look up at the **formula bar.** See how the number is in the cell, but REALLY it is a formula? Nifty, I know. If you ever need to edit a formula, do it in the FORMULA BAR, not in the cell.

Now, we know that the 89.66 will be rounded up to 90, so let's have Excel do that for us. Right click on the cell and go to **format cells**. On the dialog box that pops up, click the Number tab. Now, change the decimals area to no decimals. This action will change the decimal to the appropriate whole number. Yea, you! Now do the same for rest of the cells in the E column, one at a time to learn the skill.

Now, let's pretend that we made an error in Alex's grade enter for test 2 - that's in cell C2. Change the grade to a 94 and hit Enter. See how the grade changed //AUTOMATICALLY?// That is automatic recalculation at work! Now change that grade back to an 84.

Alrighty, here we go. Look at cell E3. What information will this cell give us once the formula is in there? Tick, tock, tick, tock, Debbie's marking period average, right? What formula do you think that cell needs? Click on E2 to see the formula for Alex's grades. Note that Debbie's grades are in Row 3, not row 2, so what is the formula? =average(B3:D3) What is Debbie's average for the quarter?

What will Molly's formula in E4 now be? Here grades are in row 4 so... =average(B4:D4)

Now we know the averages of all students for the quarter. Let's turn our attention to learning the CLASS average for each assessment.

Right click on each of the following cells: B5, C5, D5 and get them ready for answers by removing the decimals. Right click B5, and go to **format cells**. On the dialog box that pops up, click the Number tab. Now, change the decimals area to no decimals. Do the same for C5 and D5. Yes, there are short cuts to do multiple cells at once, but learn the skill first.
 * REMOVING UNWANTED DECIMALS:**

B5 through D5 will have answers, thus, FORMULAS! Woohoo!

What information will cell B5 give us? The Class Average for Test 1, yes! What cells (GIVE CELL NAMES, NOT THE NUMBERS IN THEM) are needing to be averaged for find the class average for Test 1? (B2:B4) So then, what formula will average those three cells? =average(B2:B4) What is the class average for Test 1? Be sure all concur. Now then, what formula must be typed in C5? Hint: Only the column name changes. How about D5? What formula goes there?

Let us turn our attention to cell E5. What information will that cell give us? The class's final average. And this is important to know (as a student, as a teacher) because...


 * Look at your completed spreadsheet!** Sure, we can use spreadsheets for grades, but the biggest and best use is for BUSINESS.

First, IN YOUR NOTEBOOK, write the following so you can refer to it as your work on your computer:

First, you should know that the word ' ** sum ** ' means total of an addition problem. The formula for finding totals/sums would be =sum(cell:cell) An example would be =sum(B2:D2) That formula would give you the sum of cells B2, C2 and D2. **Whenever you need to add, use a sum formula.**
 * Total: ** **sum**

Gives you the maximum, or ** highest number ** in a range of cells. Ex.: =max(B2:D2)
 * Maximum: ** **max**

Gives you the minimum, or ** lowest number ** in a range of cells. Ex.: =min(B2:D2) __
 * Minimum: ** **min**

Below is a spreadsheet made in another spreadsheet software. File>New in Excel and **save as "Happy Bellies**." Do NOT worry about making cells gray - I just did that so that you will know where FORMULAS will go, not where numbers will be typed.

NOTE: Column A and row 5 are to be bolded. Click ONCE on the A of the A column, then Ctrl +B. Click ONCE on the 5 of Row 5, then Ctrl +B. Now, anything in those cells typed later will be bolded. DO NOT WORRY about the test in cell A1 and A3 going over the line - leave them alone.

As you type the Expense titles in column A, you may need to MOVE the column a tad to the right to fit the words,but FIRST, type all words (labels) in Column A. Go ahead, type them now. HOME ROW. If you see a capital letter, TYPE A CAPITAL LETTER.

Ok, does any word in A5 to A15 need a bit more room? If so, place your mouse on the short little line between the A and B column names. See how the mouse changes to a double arrow? Click, hold, and slide to the right //only as far as need be for the words from Expenses down to fit in the A column//.


 * Have you saved early and often?**


 * Now,** type all labels in Row 5. Again, if you need room for these labels, put your mouse right on the the short little line between the columns and move right //only as far as need be for the words to fit.// Type in all numbers as shown, SAVING early and often.

Formulas will be next! Refer to your notebook and see if you can figure out the formulas needed for F6, G6, H6 and I6! Rent for the restaurant is the same every quarter, so when you get to the formulas in row 6, you will see some differences, but be SURE that you formulas are correct in Row 6! **HINT:** The range of cells needed are the SAME for every formula for these cells and they are, of course, (B6:E6). Only the action will change.

F6: Total means... (see notebook) //What info will this cell give us? Total rent paid for 2005// G6; Average means use "average" (did this in the grade book spreadsheet, remember?) //What info will this cell give us?// //Average rent paid for 2005// H6: Maximum... (see notebook) //What info will this cell give us? Maximum// //rent paid for 2005// I6: Minimum... (see notebook) //What info will this cell give us? Minimum// //rent paid for 2005//

Hmmm... how do we get rid of those pesky decimals again? If only there was a heading above that said **REMOVING UNWANTED DECIMALS...** oh, wait! There IS!

Raising hands, what is the formula that you should have in cell F6? G6? H6? I6? The cells will all read 3575, but you must have the FORMULA - do NOT type "answers" in formula (grayed) cells!

Raising hands, explain //what the cell will tell us// and what is the formula that you should have in : F7? And the answer? //This cell will tell us the **total amount** spent on **utilities** (electric, gas, sewer, water, etc.) for 2005// G7? And the answer? What will this cell tell us? H7? And the answer? What will this cell tell us? I7? And the answer? What will this cell tell us?

Complete all formulas in F through I columns - check for understanding. Only then move on the Rows 11, 13, 14 and 15. These COLUMNS will give us the totals, averages, maximums and minimums for each QUARTER of the year. Three months = a quarter, so Jan-Mar, Apr-June, July-Sept, and Oct-Dec.


 * HINT:** The range of cells needed are the SAME for every formula in **each column**. Only the action will change. For example, the B column formulas will all have (B6:B10). The C column formulas (C6:C10) and so on.




 * Nov. 3: Teacher Notes:**


 * Open your Happy Bellies spreadsheet from yesterday.**

Often, I open Excel first, then go to File > Open to retrieve my file.

Remember, as we move forward, that Ctrl + Z (undo) is your friend. Also, click on a cell just one time and look up at the Formula Bar to what is really in the cell - a label (word), number or formula.


 * CAUTION: do NOT click on or near the little bl****ack square to the lower right of each cell.** That is the autoformat handle (vocab word!), which we will use later. Meanwhile, it can really mess things up, so remember Ctrl + Z is your friend! Tell your neighbor. Tell another neighbor. Now, moving on...

Let's review formulas from yesterday to be sure that we are on the same page, and to give you a preview of spreadsheet tests in the near future. Also some discussion is due about what information we are getting from each cell. Look at your spreadsheet to answer all of the questions as your teacher asks you.

1. What **formula** should be typed in **F6? =sum(B6:E6)** What information does F6 give us? 2. What **formula** should be typed in **G6? =average(B6:E6)** What information does G6 give us? 3. What **number** shows up (as a result of the formula that you wrote) in **H7? 6997** What information does H7 give us? 4. What **formula** should be typed in **B11? =sum(B6:B10)** What information does B11 give us? 5. What **formula** should be in **B13? =average(B6:B10)** What information does B13 give us? 6. Why do we NOT include the F column in the formulas in cells G, H and I? 7. Why do we NOT include row 11 cells in any formulas in rows 13 through 15? 8. F11 shows **ANNUAL EXPENSE TOTAL for Happy Bellies Restaurant in 2005.** There are two feasible formulas that could be in that cell. What are they? =sum(bB:B10) OR =sum(B11:E11) Which one did you use? Why?

Hopefully you have noticed that our numbers in this spreadsheet do no have dollar signs (PUT ON BOARD), which go **IN FRONT of numbers, never after. 6.00$ is not a thing. $6.00 is written like this and is spoken, "six dollars."**

It made data entry (typing all of the numbers in correctly, which is a skill) easier when you were not worried about punctuation, just numbers.

Now we are going to format the numbers with commas all at once! I //**KNOW**//!

Find cell B6. Put your mouse right on the 3 then click and drag to the middle of cell I (eye)15. When you let go, all cells should be selected and the contents unchanged. Anywhere in the now-selected area, RIGHT CLICK and go to format cells. Number tab. Pick the choice with thousands separator and no decimals and enter your decision. Dollar signs everywhere would make it sloppy, so, for now, the cell formatting is finished.

Bold cell F11. (Ctrl+B) It is special because it tells us, as previously discussed, the total amount of money spent to keep this restaurant going.

Be certain that you have formulas, no typed numbers, in all grayed out cells in your Happy Bellies spreadsheet.