eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473144 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 01/01/2006 |
| Series: | Excel for Professionals series |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 236 |
| File size: | 7 MB |
About the Author
Read an Excerpt
Excel for Teachers
By Colleen Conmy, Bill Hazlett, Bill Jelen, Adrienne Soucy, Linda DeLonais
Holy Macro! Books
Copyright © 2006 Holy Macro! BooksAll rights reserved.
ISBN: 978-1-932802-20-7
CHAPTER 1
Calculation Basics
Opportunity
Excel is great at doing math. When Dan Bricklin conceived of the first spreadsheet in 1978, he envisioned a calculator where you could set up a math problem, but then scroll backwards in time and change the terms in the problem to see a new answer. Along with Bob Frankston, he developed VisiCalc – a Visible Calculator. Since VisiCalc in 1979, all spreadsheets have been able to calculate.
Solution and Overview
This section will teach you the basic math operators and functions available for demonstrating classroom math.
There are also at least three common methods of entering formulas. In the first three examples below, you will learn the three methods of entering formulas. You can choose whichever method is the easiest for you.
Using the Touch-Typing Method (Addition)
Figure 8 shows a story problem. You want to enter a formula in cell B6 that will add cells B4 and B5.
1. With the mouse, single click in cell B6 to move the cellpointer to that cell. Every formula must start with an equals sign, so type the equals sign to start entering the formula.
2. In this example, you will use the touch-typing method of entering the formula. Without typing any spaces, finish typing the formula as B4+B5. When your screen looks like Figure 10, type the Enter key on the keyboard.
3. After you type Enter, Excel will calculate that the sum is 30. Excel will also move the cellpointer down one cell to B7.
4. Press the Up-Arrow 1 time to move the cellpointer back to cell B6. When B6 is selected, look at the formula bar just above the spreadsheet. Although the spreadsheet shows a value of 30 in the cell, the formula bar reveals that this cell really contains a formula of =B4+B5.
5. Here is the "miracle" of spreadsheets. Move the cellpointer up to cell B4 and type a different number instead of the 20. Type 200, type Enter. The cellpointer will move down to cell B5, but all formulas that reference B4 in the entire worksheet would instantly recalculate. Thus, cell B6 becomes 210.
Using the Mouse Method (Subtraction)
Figure 14 shows a subtraction story problem. In this case, you will want to set up a formula that subtracts B5 from B4. In this example, you will use the mouse for entering parts of the formula.
1. As before, you will have to type the equals sign on the keyboard to start the formula.
2. After typing the equals sign, use the mouse to touch cell B4. Because you are in formula entry mode, the formula in cell B6 automatically types B4 for you.
3. Now, back on the keyboard, type the minus sign. Notice that when you type the minus sign, the flashing dots around B4 become a solid blue color. Excel is waiting for you to touch another cell with the mouse.
Tip:
If your keyboard has a numeric keypad, the upper right keys on the keypad will let you type the common operator keys without using the Shift key.
4. With the mouse, touch the 30 in cell B5. Excel will enter B5 in the formula.
5. You can now type the Enter key to have Excel calculate the result.
If you are comfortable using the mouse, this technique of entering formulas is fairly quick and easy
Using the Arrow Key Method (Addition and Subtraction)
The next story problem requires both addition and subtraction. The arrow key method was introduced in 1981 by Lotus 1-2-3. The method became very popular for accountants who hated typing obscure cell references like B4 and AJ62. This was before computers typically had a mouse (the first Macintosh didn't come out until late 1983).
1. As shown in the image below, start by typing an equals sign in cell B7.
2. Next, using the arrow keys on your keyboard, type the Up-Arrow key three times. After the first press of the Up-Arrow key, the screen will think that you want to start your formula as =B6.
2a. That is OK. Ignore the screen and type the Up -Arrow key a second time. Now the screen thinks that you must want to start your formula with =B5.
2b. Again, ignore what is on the screen and type the Up-Arrow key one more time. Now the screen suggests that your formula should start with =B4. This is correct.
3. The next part is a little tricky. In your formula, you want to add B5 to the formula. Type the plus sign on your keyboard. This tells Excel that you are accepting the B4 portion of the formula and that you are ready to enter another cell. Instead of a flashing box around B4, you now have a solid box around B4. Here is the tricky part: as soon as you type the plus sign, Excel returns the focus back to the original cell location of B7.
4. You want to point to the 3 in cell B5 now. Many people trying this method for the first time think that they should type the Down-Arrow to move down from B4. This is not correct. You actually have to type the Up-Arrow twice to move up from cell B7 to B5. Type the Up-Arrow twice and Excel will propose a formula of = B4+B5.
5. Next, type the minus sign on the keyboard. Excel will return the focus to the original location of B7. Press the Up-Arrow one time to subtract B6 from the formula.
6. Type Enter and Excel will calculate an answer of 3. Move the cellpointer back to B7 to examine the formula in the formula bar.
Now that you have learned the three methods for entering formulas, you can use whichever method suits you the best. In the remaining sections of this chapter, you will see the formulas to use for various mathematical operations. You can use whichever method you prefer for entering these formulas.
Note:
Once you get used to the arrow key method, it is the absolute fastest way to enter formulas. The act of moving your hands from the keyboard to the mouse then back to the keyboard is relatively slow. The process to enter the formula above requires only nine keystrokes, and many of those are repetitive strokes of the Up-Arrow.
Entering Multiplication Problems
Problems requiring multiplication use the Asterisk key for the multiplication operator. Type an asterisk using either the Shift-8 key, or the Asterisk key on the numeric keypad. Yes – it would be a lot easier if they used the X key for multiplication, but then we could get confused whether the X was referencing a cell location, as in X2, or was meant to multiply by 2.
Entering Division Problems
There are two slash signs on the keyboard. File paths in Windows typically require the backslash located above the Enter key. Luckily, division problems require the forward slash located on the same key as the question mark. The image on the following page shows a division problem.
Entering Fraction Problems
Sometimes Excel expects our students to understand higher mathematical concepts, such as that a fraction is really a division problem. That is, one-ninth is actually one divided by nine. For fractions in Excel, enter the numerator, the forward slash, and the denominator, as seen in the formula bar in Figure 29.
Using Parentheses to Control the Order of Operations
The formula for the next problem produces an incorrect result. If three club members are going to split 12 candy bars, they should each get four candy bars instead of eight.
You need to understand Excel's order of operations. In a formula, Excel performs calculations in this order:
1. Unary Minus
2. Exponents
3. Division and Multiplication, left-to-right
4. Addition and Subtraction, left-to-right
Thus, Excel will first divide B10 by 3 to get an intermediate result of 2. It will then add B8 to B9 to get 6. Finally, Excel will add the 6 and the 2 to get 8. For problems that don't follow the Associative law of mathematics, you will want your students to enter parentheses to override Excel's default order of operations.
The following formula will calculate the correct result: =(B8+B9+B10)/3
Calculating Squares, Cubes, Square Roots, Cube Roots
Excel has the tools to figure out exponents and roots. However, it might be a bit confusing to figure out the third or fourth root of a number.
The following problem tests the student's knowledge of the Pythagorean theorem. The student has to square the length of both legs of a right triangle, sum the squares, and then take the square root.
The formula in C6 to square 122 is =B6^2. In Excel, the carat (^) is used to raise a number to a power.
Tip:
You can type a carat by holding down the Shift key and pressing 6.
Look at Figure 33. The formula in C7 is =B7^2. The formula in C8 is =C6+C7. In cell C9, you want to take the square root of cell C8. There are two ways to do this.
First, Excel offers a built in function to calculate square roots.
1. Type =SQRT followed by an open parentheses to start the function.
2. Using the mouse, touch cell C8. Type the closing parentheses.
This formula suggests that it is about 136 miles from A to C.
At some point in the future, much later than eighth grade, your students may learn that taking the square root of a number is the same as raising the number to the one-half (1/2) power.
Thus, the alternate formula for C9 is =C8^(1/2), as shown in Figure 34.
In the next problem, the student is to determine the volume of a cube that is 25 feet on each side. The volume is the length raised to the third power. Again, the carat is used for an exponent.
To raise B6 to the third power, use =B6^3.
The converse problem is trickier. In the following problem, the student would have to take the cube root of 3375. Excel does not offer a Cube Root function like the SQRT function for square roots.
Thus, your student is going to have to use the alternative form of =85^(1/3) in order to take a cube root. This may be confusing for the student, but it is easier than figuring out cube roots by hand!
Adding a Column of Numbers
Consider the problem in the next image. You might be tempted to use a very long formula such as =B5+B6+B7+B8+B9+B10+B11 to calculate the total.
There is a much faster way. Excel offers a SUM function for totaling several cells. Because summing a column of numbers is such a popular task among accountants, Microsoft gave us a shortcut key to enter sums.
1. Place the cellpointer in cell B12. Look on the Standard toolbar for a Greek letter Sigma. This is the AutoSum button. See Figure 38 below.
3. With the cellpointer in B12, press the AutoSum button. Excel will use its Intellisense and propose a formula to sum the range from B5:B11. The program even draws a flashing box around the range that it is proposing to sum.
5. This is the correct range, so simply type Enter to sum this column.
Calculating an Average
In the next problem, you need to figure out the Average of a column of numbers.
Take a look at the AutoSum button in Figure 42. To the right of the button is a dropdown-Arrow. This dropdown-Arrow will allow you to quickly enter formulas that will let you Average, Count, and find the smallest or largest value.
Put the cellpointer in B12. Select the dropdown arrow next to the AutoSum button and choose Average.
The result: Excel will enter a formula using the AVERAGE function to calculate the average.
CHAPTER 2Multiplication Tables
Opportunity
My classic example for demonstrating the various types of mixed references is to create a multiplication table. Although you probably have access to a multiplication table that you can photocopy, this exercise will demonstrate both the AutoFill option and how to use mixed cell references.
Solution and Overview
You will use some efficient tools to create the multiplication table. AutoFill lets you type the first few cells in a series and then extend the series. Transpose lets you turn a range on its side. Finally, you will build one formula that handles the entire multiplication table.
Creating the Solution
Start with a blank Excel workbook. Leave cell A1 blank.
Using the Fill Handle to Extend a Series
In cells A2 and A3, type the numbers "1" and "2". Select a range containing both cells. In the lower right corner of the selection, there is a square dot known as the Fill Handle. With the mouse, grab the fill handle and drag down to row 13.
As you drag, a tooltip appears showing the numbers that will be entered in the last cell. When you get to row 13, the tooltip indicates that the series will extend to 12. Release the mouse button to enter 1 through 12 in the cells.
Copying a Range on its Side
After using the fill handle, the range of A2:A13 will be selected. Use Ctrl+C to copy that range. Move to cell B1.
From the menu, select Edit – Paste Special. In the Paste Special dialog box, choose the checkbox for Transpose. The process of transposing will turn data that goes down a column to data that goes across a row.
Entering a Single Formula for Many Cells
With a little thought, you can usually write one formula that can be copied to many cells. If you think about the formula that is needed to populate the interior of the multiplication table, it can be expressed this way: "For any cell, multiply the number found in row 1 above the cell with the number found in column A to the left of the cell.
One such formula would be =C1*A5, as shown in cell C5.
While the preceding formula will work just fine in cell C5, it will not work when you copy the formula to any other cell in the table. Figure 47 shows the formula after it has been copied to D6. The reference that used to point to C1 is now pointing to D2. The reference that used to point to A5 is now pointing to B6. This behavior is called relative references and it is by design in Excel.
If you change C1 to $C$1, this is called an absolute reference. When you copy a formula with this reference, the formula will always point to cell C1. The dollar signs ($) before C and 1 ensure that neither the C nor the 1 will change as the formula is copied to other cells.
Sometimes, you need a reference that is partially absolute. This is called a mixed reference and has only a single dollar sign.
If you place the dollar sign before the column letter, then the column letter will be fixed but the row number will change as you copy the formula down the rows. In our current example, the portion of the formula pointing at column A would need a dollar sign before the A.
If you place the dollar sign before the row number, then the row number will be fixed, but the column letter will change as you copy the formula across a range. In our current example, the portion of the formula pointing at row 1 would need a dollar sign before the 1.
Move the cellpointer to cell B2. While holding down the Shift key, use the Down- and Right-Arrow keys to select the range of B2:M13.
Any formula that you type will start to appear in cell B2. Type =B$1*$A2. Instead of hitting Enter by itself, type Ctrl+Enter to put a similar formula in the entire selected range.
Using the Application
Print the sheet out and allow your students to study from it.
Excel Details
Simplifying Dollar Sign Entry in Absolute and Mixed References
The process of entering the dollar signs in a reference can be simplified by using the F4 key. As you are entering the formula, pressing F4 immediately after typing the reference will change the reference from relative to absolute; that is, A2 would change to $A$2. Press F4 again to change to a mixed reference where only the row is held constant – A$2. Press F4 again to change to a reference where only the column is fixed – $A2. Press F4 again to toggle back to the relative reference of A2.
Thus, the shortcut for entering the formula in B2 is as follows.
Type an equals sign. Type the Up-Arrow to move to B1. Press F4 twice to lock just the row. Type the Asterisk key on the numeric keypad. Type the Left-Arrow to move to A2. Type the F4 key three times to lock just the column number.
More Cool Fill Handle Tricks
At the start of this chapter, you used the fill handle to extend a series starting with 1, 2. The fill handle can automatically enter many types of data in a range of cells.
Type "Sep" into a cell. Select the cell. Click on the fill handle and drag down or to the right.
Excel will automatically fill in months of the year. As you drag, a tooltip will indicate the last month to be filled in. When you release the mouse button, the selected number of months will appear.
(Continues...)
Excerpted from Excel for Teachers by Colleen Conmy, Bill Hazlett, Bill Jelen, Adrienne Soucy, Linda DeLonais. Copyright © 2006 Holy Macro! Books. Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
Table of Contents
Contents
Title Page,Copyright Page,
Dedication,
Acknowledgements,
About the Authors,
Preface,
Introduction,
Introduction to Excel,
Chapter 1 – Calculation Basics,
Chapter 2 – Multiplication Tables,
Chapter 3 – Calculating with Times,
Chapter 4 – Classroom Libraries,
Chapter 5 – Grade Books,
Chapter 6 – Attendance Records,
Chapter 7 – Lesson Plan Sheets,
Chapter 8 – Random Sequence for Book Reports,
Chapter 9 – Randomly Calling on a Student,
Chapter 10 – Tracking Student Information,
Chapter 11 – Student Mailing Lists,
Chapter 12 – Sorting Class Lists by First Name,
Chapter 13 – Reporting Long Term Testing Results,
Chapter 14 – Reading Grade Level Test Results,
Chapter 15 – Tracking Proficiency,
Chapter 16 – Creating Checklists,
Chapter 17 – Requisition Forms,
Chapter 18 – Printing Grid Paper,
Chapter 19 – Math Exercise Sheets,
Chapter 20 – Homework Checker,
Chapter 21 – Recording Rubric Data,
Chapter 22 – Organizing Vocabulary Lists,
Chapter 23 – Reading Journal,
Chapter 24 – Demonstrating Fractions with Charts,
Chapter 25 – Bar Charts,