CS130/230 Lecture 6
Logic and Graphing
Thursday, February 19, 2004
Last time you produced a worksheet similar to the following:
LetÕs have a look at what the spreadsheet would look like if we printed it out. Click on the (Print Preview) toolbar menu.
You will probably find that you will need to make some adjustments before you can print the page. You might need to change the page orientation from portrait to landscape. You also might need to adjust the margins. You can do this by going into the Page Setup menu item in the file menu.
One of the useful features under the margins tab is the centre horizontally and centre vertically option. This centres your spreadsheet in the middle of the page.
After your adjustments, the bookshop spreadsheet should look like the following in (Print Preview):
You can use
comparison operators to compare two numbers, functions, formulas, or labels and
return either true or false.
Examples
include:
=2*3=4+2
=A1>0
=average(a1:a10)>50
Every
conditional test must include at least one comparison operator. As an example,
in the formula =A1>0, the comparison operator is >. The following six
comparison operators exist in Excel:
Comparison
Operator |
Definition
|
= |
Equal to |
<> |
Not equal
to |
< |
Less than
|
<= |
Less than
or equal to |
> |
Greater
than |
>= |
Greater
than or equal to |
The IF function checks a condition that must be either true or false. If the condition is true, the function returns one value; if the condition is false, the function returns another value.
The syntax
of the IF function is:
=IF(logical_test,
value_if_true, value_if_false)
You are the
owner of a small business and every month you need to take a person's
hours-worked and hourly wage and calculate gross and net pay. Gross pay is
hours worked times hourly wage and net pay is gross pay minus: (1) Social
Security (7.65%) (2) State Income Tax (8.5%) and (3) Federal Income Tax (15%).
Set up a worksheet that allows the owner the ability to enter hours worked and
hourly wage; your worksheet then displays: Gross Pay, Social Security, State
Income Tax, Federal Income Tax, Net Pay. Your worksheet must handle overtime
such that any hours over 40 earn time and one half. If a person was making
$5.00 per hour and worked 45 hours, for 5 hours they made $7.50 per hour.
Sometimes
our logical_test needs to use a logical function such as AND and OR:
The syntax
for each of these statements is:
OR(logical
test#1, logical test #2)
AND(logical
test#1, logical test #2)
For the OR
construct, a value of TRUE is returned if EITHER of the logical tests returns a
value of TRUE; otherwise, a value of FALSE is returned to the cell.
For the AND
construct, a value of TRUE is returned if BOTH of the logical tests return a
value of TRUE; otherwise, a value of FALSE is returned to the cell.
Note that
you can have more than TWO logical tests within an OR or AND statement by
simplify separating each subsequent logical test with a comma.
Often, the
OR and AND constructs are used within an IF statement. For example, the
statement
IF(AND(1<2,4=3),"HELLO","GOODBYE")
will return
to the cell "GOODBYE" because both 1<2 and 4=3 are not true.
However, the statement
IF(AND(1<2,4=4),"HELLO","GOODBYE")
will return
"HELLO" to the cell.
Consider
the following data:
Name
|
ID# |
Quiz1
|
Quiz2 |
Midterm |
Final |
Adams |
0001 |
14 |
23 |
82 |
76 |
James |
0002 |
12 |
21 |
76 |
68 |
Jones |
0003 |
15 |
24 |
91 |
93 |
Mann |
0004 |
14 |
19 |
88 |
73 |
Smith |
0005 |
11 |
16 |
79 |
71 |
Tolls |
0006 |
10 |
13 |
62 |
65 |
Wells |
0007 |
5 |
10 |
43 |
55 |
Points |
|
15 |
25 |
100 |
100 |
Part I: Add two additional columns as
follows: (a) Average is a person's total points are divided by the total points
possible (b) Grade is 90-100 A, 80-90 B, 70-80 C, 60-70 D, 0-60 F.
Part II: Create a Pie Chart that shows the
percentage of A's, B's, etc. Hint: you will need to use the COUNTIF function.
You can look up how it works in Excel help.
The file Ôeducation.txtÕ in the ÔCS130 PubÕ folder contains information about education in the United States divided by state. The numbers listed next to each state represent the following:
- Cost per pupil
- Pupil/teacher ratio
- Mean annual teacher salary
- Percentage of students that take the SAT test
- Mean verbal score on the SAT test
- Mean math score on the SAT test
- Mean total score on the SAT test
Copy the data in this file into an Excel worksheet. You will need to format the data using the ÔText to ColumnsÕ option in the ÔDataÕ menu.
A useful feature in Excel is the ÔFreeze PanesÕ feature in the ÔWindowÕ menu. Select the top left number in the table and click on ÔFreeze PanesÕ. This will make any rows above this cell and any columns to the left of the cells freeze, while you can still scroll the rest of the spreadsheet.
Add a column chart to your spreadsheet that will show the differences in SAT scores (verbal, math and total) between two states of your choice. Label your chart correctly.