home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Using Numbers, Formulas, and Functions with Excel 97
What you will learn from this lesson
With Excel 97 you will:
What you should do before you start this lesson
Exploring the lesson
You can create worksheets to record and calculate grades using Excel 97. The power of Excel 97 is great. Not only can Excel 97 calculate student grades, weighted scores, and program-planning budgets, but it also can maintain the financial records of an entire school. For this lesson, you will explore some basic functions that you can use in your classroom.
Sorting functions of Excel 97
Recording names and addresses of your students can be a full-time job. Just as soon as you have everyone in the correct order, you have a new student whose last name starts with the letter C. With Excel 97, sorting the names by last name, by the enrollment date, or even by birthday is quick and easy.
There are many details to track in a classroom, such as student data, grades, project progress, student participation, homework, and other completed assignments. Using Excel 97, you can track all this information for each student, and then organize it according to importance or category.
Alphabetizing names
# |
A |
B |
C |
3 |
Name |
e-mail address |
Seating |
4 |
Juanita |
juanita@washington.k12.wa.us |
R2-3 |
5 |
John |
john@ washington.k12.wa.us |
R2-1 |
6 |
Roy |
roy@ washington.k12.wa.us |
R1-1 |
7 |
Tom |
tom@ washington.k12.wa.us |
R1-3 |
8 |
Kim |
kim@ washington.k12.wa.us |
R1-2 |
9 |
Sean |
sean@ washington.k12.wa.us |
R3-1 |
10 |
Georgia |
georgia@ washington.k12.wa.us |
R2-1 |
Creating a one-input data table
Using Excel 97, you and your students can learn the costs associated with making payments on purchases. You can easily calculate monthly payments based on interest and balance due on purchases. In the next exercise, you can calculate monthly payments based on interest and total cost of a car, showing how the loan term and interest rates affect monthly payments.
Calculating car payments
Row # |
Column C |
Column D |
2 |
Car Payment Schedule |
|
3 |
||
4 |
Down Payment |
None |
5 |
Interest Rate |
8% |
6 |
# of Months (term) |
36 |
7 |
Loan Amount |
$5000 |
8 |
||
9 |
||
10 |
Payments |
Order of calculation in Excel 97
You have just discovered one easy formula in Excel 97. There are many others that help you calculate or analyze everything from finances to statistical sampling plans. When you combine several mathematical steps in a formula, they are performed in a specific order. Excel 97 will start calculating from the left to the right according to the following order of operations:
For reference, the Comparison operators are:
Hiding columns
Using Excel 97 to maintain all of your student records in one file is handy but sometimes awkward. Hiding columns is an easy technique to “fold” away columns you may need, but not need to see for a particular process such as recording test scores and grades. You will find this technique very useful in maintaining complete records in one file.
Hiding columns with test scores
Calculating grades for equal tests
When all tests are equal, you can easily add the raw data, calculate the average, and determine the grade. Assume that you are grading on a specific range of points for each letter grade. The scale is 90–100=A, 80–89=B, 70–79=C, and 60–69=D. Any score less than 60 is an F.
Average function
In this exercise you have given your students equal weekly tests over two months and want to calculate their average grades.
Averaging test scores
Name |
9/1 |
9/8 |
9/15 |
9/22 |
9/29 |
10/6 |
10/13 |
10/20 |
Juanita |
93 |
97 |
89 |
94 |
88 |
92 |
93 |
95 |
John |
95 |
85 |
83 |
93 |
87 |
85 |
91 |
90 |
Roy |
97 |
95 |
92 |
96 |
91 |
93 |
98 |
99 |
Tom |
85 |
80 |
87 |
83 |
88 |
84 |
82 |
90 |
Kim |
87 |
84 |
90 |
88 |
86 |
82 |
91 |
92 |
Sean |
88 |
95 |
94 |
95 |
91 |
85 |
90 |
93 |
Georgia |
91 |
86 |
89 |
89 |
90 |
94 |
88 |
96 |
Copying formulas
Once you have created a formula, you can quickly and easily copy it into adjacent cells to calculate student records that contain the same type of data.
Copying formulas
Applying a formula to equally weighted tests
Now that you have recorded test scores and calculated averages, you can easily figure letter grades from numbered scores using Excel 97.
For this type of formula, Excel 97 compares data with a formula that evaluates the data to a logical value of true (1) or false (0). Using Excel 97 to calculate grades is quick and easy. The formula interprets a range of data such as >89 (which means 90 or more) and assigns a letter grade if the data meets the criteria (score is >89—true or false). Therefore, you have a conditional calculation commonly known as an If-Then statement for equally weighted tests.
Creating letter grades from number scores
Analyzing scores
Sometimes the tests and assignments you give are not equal in importance, making the grading process more complex. Excel 97 can help you analyze these types of test scores.
Calculating mean, median, and standard deviation for each test
Creating a budget for a school function
Having a successful school function often depends on having a budget in place. For this exercise you will create a budget and test to see what happens to the results when you change any of the components.
Entering budget categories
Creating a budget involves entering the categories and specific items into the worksheet. The first step to creating a budget in Excel 97 is to determine the budget categories and enter them in the worksheet.
Entering categories for a budget
Entering budgeted amounts and formulas
The next step in preparing a budget for the Awards Dinner is to determine budget amounts and place them with formulas in the worksheet.
Placing budgeted amounts in formulas
Entering actual amounts
The final step in using Excel 97 to budget the Awards Dinner is to record the actual amounts spent. Using Excel 97 to budget an event helps track current expenses and makes it easy to plan future events using the same budget process. As you receive and pay the invoices for your Awards Dinner, enter the values into your budget worksheet.
Completing the budget process with actual amounts
How you can use what you learned
As you become more comfortable with worksheets, you will discover many applications for them. Using Excel 97 you can easily and quickly maintain student records. Students can reinforce basic mathematical processes using Excel 97 to produce charts and tables. Furthermore, by adding well-designed charts and tables to reports, students can enhance both their understanding of the content and their presentation of the information. In addition, you can also check student math problems.
Extensions
Exploring the Roman numeral function
Converting Roman numerals is an exercise in logic. Explain to your students that if we were still using Roman numerals, the year 2000 would be easy to represent with the letters MM.
Converting years from Arabic to Roman numerals
Summarizing what you learned
In this chapter you explored and practiced:
scores.