eBook
Available on Compatible NOOK devices, the free NOOK App and in My Digital Library.
Related collections and offers
Overview
Product Details
| ISBN-13: | 9781615473151 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 01/02/2007 |
| Series: | Excel for Professionals series |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 234 |
| File size: | 4 MB |
About the Author
Read an Excerpt
Excel for the Math Classroom
By Bill Hazlett, Bill Jelen, Linda DeLonais
Holy Macro! Books
Copyright © 2007 Tickling KeysAll rights reserved.
ISBN: 978-1-932802-59-7
CHAPTER 1
Calculation Basics
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.
This section will teach you the basic math operators and the functions available for demonstrating classroom math.
There are at least three common methods of entering formulas. In the first three examples below, you will learn these three methods of entering formulas. You can then choose whichever method is the easiest for you.
Using the Touch-Typing Method (Addition)
Figure 1 shows a story problem. You want to enter a formula in cell B6 that will add cells B4 and B5.
1. Start your formula with an equals sign.
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. Type in the rest of the formula.
In this example, you will use the Touch-typing method of entering the formula. Without typing any spaces, finish typing the formula as follows: B4+B5
3. Press Enter to tell Excel that the formula is complete.
When your screen looks like Figure 3, press the Enter key on the keyboard. After you type Enter, Excel will calculate that the sum is 30. Excel will also move the cellpointer down one cell to B7.
4. Look at the formula.
Press the Up arrow one 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 actually contains a formula of =B4+B5.
5. See how the formula result changes when the elements change.
Here is the "miracle" of spreadsheets. Move the cellpointer up to cell B4 and type a different number instead of the 20. Type 200 and press Enter. The cellpointer will move down to cell B5, but all formulas that reference B4 in the entire worksheet will instantly recalculate. Thus, cell B6 becomes 210.
Using the Mouse Method (Subtraction)
Figure 7 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 method for entering parts of the formula.
1. Start your formula with an equals sign.
As before, you have to type the equals sign on the keyboard to start the formula.
2. Select the first term.
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. Enter a minus sign.
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.
4. Select the second element.
With the mouse, touch the 30 in cell B5. Excel will enter B5 in the formula.
5. Press Enter to tell Excel that the formula is complete.
When you type the Enter key, Excel calculates 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. Start your formula with an equals sign.
As shown in the image below, type an equals sign in cell B7.
2. Select the first element.
1. 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.
2. That is OK. Ignore the screen and press the Up arrow key a second time. Now the screen thinks that you must want to start your formula with =B5.
3. 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. Enter a plus sign to the first element.
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. Select the second element.
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. Enter a minus sign and select the third element.
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. Press Enter to tell Excel that the formula is complete.
When you type Enter, Excel calculates 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.
Entering Multiplication Problems
Problems requiring multiplication use the Asterisk key for the multiplication operator. Type an asterisk using the Shift+8 keys 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 it could become confusing 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 below shows a division problem.
Entering Fraction Problems
Sometimes Excel expects your 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 22.
Using Parentheses to Control the Order of Operations
When it comes to math operating signs in an Excel formula, Excel understands and uses the order of operations correctly. It does not necessarily move from left to right within the formula. Instead, it follows the old mnemonic phrase "Please Excuse My Dear Aunt Sally": Parentheses (P) and other grouping symbols take precedence, followed by Exponents (E). Next, Multiplication (M) and Division (D) are done in order from left to right, and then Addition (A) and Subtraction (S), also from left to right. The only exception is that if you use a minus sign in front of a number, Excel assumes it to be a negative number first before performing any other operation. If your students understand how to calculate correctly using the order of operations, then they should have few problems writing formulas.
A very simple word problem can explain how this works.
Jimmy brought 4 candy bars to the club house.
Calvin brought 2 candy bars.
Suzy brought 6 candy bars.
They agreed to split the candy bars equally. How many
candy bars does each club member get?
The obvious solution is to add the numbers together and divide by 3. However, using the formula =4+2+6/3 results in the wrong answer of 8. To fix this, you need to enclose the addition in parenthesis so that Excel knows to do the adding first, followed by division. The formula =(4+2+6)/3 gives the correct result of 4.
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.
Look at Figure 24. 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 parenthesis to start the function.
2. Using the mouse, touch cell C8. Type the closing parenthesis.
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 25.
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 =B5^(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 provided a shortcut key to enter sums.
1. Locate the AutoSum button.
Place the cellpointer in cell B12. Look on the Standard toolbar for a Greek letter Sigma (σ). This is the AutoSum button. See Figure 29 below.
2. Select the range to sum.
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.
3. 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 33. 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 2Printing Grid Paper
Opportunity I
You need some grid paper for math class or for mapping or for art class. You realize that you have run out of grid paper in your supply cabinet. The school doesn't have any. Or — you have grid paper with five squares per inch, but you need grid paper with two squares per inch for your younger students.
Solution and Overview
By its nature, Excel is the world's largest sheet of grid paper. With 256 columns and 65,536 rows, it is fairly easy to convert a blank Excel spreadsheet into a printed sheet of grid paper.
Creating the Solution
The main problem is that Excel's cells are rectangular instead of square. This is fairly easy to resolve.
1. Adjust the row height.
Open a blank Excel worksheet. To the left of cell A1 is a gray box with the row number 1 in it. Below the number for row 1 is another gray box with the number 2 in it.
1. Hover your mouse pointer over the line between the gray 1 and the gray 2. When your mouse is in the right position, the mouse pointer will change to a horizontal line with arrows pointing up and down as shown below.
2. When the mouse pointer looks like the one in the figure above, left-click the mouse without moving it up or down. A tooltip appears showing that your rows have a height of 12.75, which corresponds to 17 pixels. You will want to remember the 17 pixels figure. (This will be different on each computer, based on your default font).
2. Next, you will want to adjust all of the columns to be 17 pixels wide. There is an easy way to do this. To select all of the cells on the worksheet, click the gray box above and to the left of cell A1. This will highlight the entire spreadsheet.
1. Position the mouse between the gray A column header and the gray B column header. When the mouse is resting just on the line between the A header and the B header, the cursor will change to a vertical line with arrows pointing left and right.
2. When the mouse pointer looks like the figure above, left-click the mouse and slowly drag to the left. The tooltip will show that you are starting at 56 pixels.
3. As you drag to the left, the width of the column will narrow. When you have reached 17 pixels, release the mouse button.
Because you selected all of the cells, changing the width of column A will change the width of all columns. You have now created cells that are perfectly square.
Using the Application
Even though you will be drawing gridlines, Excel expects there to be something inside of the cells. When you later try to print or use Print Preview, Excel will complain that there is nothing to print.
To prevent this objection from Excel, enter a single spacebar character in cell A1 of the spreadsheet.
Adding Gridlines
In order to print the grid paper, you will have to either turn on gridlines or add borders to the cells. It is easier to turn on gridlines (see Formatting with Gridlines on page 26) but you have more control when you use borders.
Formatting with Cell Borders
1. Open the Format Cells dialog box.
While you have all cells selected, type Ctrl+1 (in case it is hard to read in this font, that is the numeric "one" key while you are holding down the Ctrl key). Ctrl+1 is the shortcut to display the Format Cells dialog box.
2. Format the border.
The Format Cells dialog has six tabs across the top. Choose the Border tab.
1. Choose the Line Style.
The Line section offers 15 different line styles. You can choose to use the default thin solid line (the last choice in the left column), or any of the dotted line styles.
You choice will depend on the project.
If your students are drawing a floor plan of their room, you might want the gridlines to be barely visible. A thin dotted line might be the most appropriate. If the students are plotting points on an XY coordinate, you might want solid lines throughout.
2. Set the line color.
The Color dropdown offers 56 colors. If your classroom has a laser printer capable of printing only black, then one of the three gray options might be appropriate for printing lighter lines.
3. Draw the lines.
Once you have selected a color and a line weight, it is time to draw the lines. Although the Border section would let you draw any combination of lines, in this case it is easiest to use the Presets section.
(Continues...)
Excerpted from Excel for the Math Classroom by Bill Hazlett, Bill Jelen, Linda DeLonais. Copyright © 2007 Tickling Keys. 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,
Dedications,
Acknowledgements,
About the Authors,
Preface,
Calculation Basics,
Printing Grid Paper,
Cartesian Coordinate Grids,
Multiplication Tables,
Math Exercise Sheets,
Arithmetic Facts Quiz,
Homework Checker,
Magic Squares,
Coordinate Grid Matching,
Math Art,
Candy Bar Fractions,
Math Facts Game,
Secret Code Maker,
Probability with Coins or Dice,
Demonstrating and Comparing Fractions with Charts,
Finding Maximum Area and Volume,
Solving Systems of Equations,