cs140 lab3 -- spreadsheet  (also, intro to variables and ifs)

I) From your cs account, copy ~cs104/labs/lab3/petstore.xls to your home. Then 
start EXCEL and load that file (files, open, ... .) If you like, browse around your
cs directory. I know it creeps me out seeing my files that way.

   It's my lame petstore example from class. The first part of the lab is to mostly 
duplicate what I was doing on Friday.


WRITING/SLIDING FORMULAS:

   Under TOTAL use the sum function to find the total number of pets sold for Sid. 
Slide it down (click on the lower-right corner and pull down) to Nancy and Richard.
   Write a formula to the right for Total$. Use the catsSold*catPrice+dogsSold*dogPrice
... method, using the cells I filled in for costs of pets. It shouldn't have any 
numbers in it -- just lots of battleship things (cell references.) Try changing some
prices for animals and number of animals sold to check it really does recompute.
   Slide that down. Yikes! It is using the wrong pet prices! Change to absolute
references (like $J$18) and slide it down again.

   To see how these work, go to a cell in the middle of nowhere. Type some things and
slide them down, then to the right to see what they do. Try: B2, $B2, B$2, $B$2.
Exactly what does the $ fix in place?


   Go to an empty cell and click on the down-arrow (V) next to the sigma (greek E) on
top. Go to AllFunctions, Math and find the function to get the biggest number in a
range (you can probably guess it, but it's good to know where to find new ones.) Use
that to compute the most cats/dogs/gerbils/panthers sold. Instead of typing the range
by hand (B2:B4) type just the ( and then try getting the range these two ways:
    a) "click in" the range        b) go to B2, hold shift and arrow down

   Under the COMMENTS, use an "if" to show "bonus check" (if they sold more than $100)
or "fired". Look up how to use "if" under logic functions.
   After that, add another if inside that one (move to the cell then click on the top
bar to edit.) Where it says "fired" change it so 50-99.99 is nothing and <50 is fired
(100 or more is still "bonus check".) Pull it down to the other employees.
   This can go on with as many ifs as you like, but a better way is [hv]lookup (later)
   If you didn't make this mistake already: reedit that big if, delete the last ) and ENTER. 
Excel should give you a pretty error message?

   Fill in the formula for total sales and try changing some of the pet sales. That's 
a lot of computing it's doing!


II)  OTHER STUFF:

   TOTAL$ should be in $ format. Select the boxes and change them (top row of 
pictures.) Or, change one and slide it down -- that's what those options are about how 
to copy. It normally copies contents and format.
   Use the colors on the far right to make pet and employee names look pretty 
(background and letters.) Center them (middle part of top bar.)
   Try to center the totals. It didn't work for me -- oh, well.

   The name row is too small. Enlarge it to show all of Richard's name (click between
the A and B across the top.) Make all of the employee rows 50% wider (select them,
click on (format, rows) and type in a bigger number.)

   Make two more employees by sliding down the Richard data. Hmmm...total sales is
wrong. Have him sell 200 gerbils. That's wrong, too. Check the formulas, then erase
that stuff (simplest way: select them and DELETE.)
   A better way to add rows, so that all formulas that go "across" that range
automatically update, is to use insert (top bar), row. Try that just after Richard and
then on Richard and see which one works. Add some numbers to check (but really, you
could just check the bottom formulas to see if they change.)

   Finally, let's make our top bar look nicer. Click on the down-arrow on the far
right and select "custom toolbar." Add "insert row" somewhere to the toolbar (the row
across the top.) Only losers use Left and Right justify -- take them out of the 
toolbar.


III) GRADES "DATABASE"

   This is due next lab, or you can have your TA check it off during office hours (or 
finish it now.)

   Back in your cs account, copy ~cs104/labs/lab3/nameslist.txt into your account. In
Excel, we will IMPORT this. Select Data (top bar), import and that file. A delimiter
is the thing that lets us know where one thing ends and another begins. Select : for
the delimiter and load it into cell A3. Use delete columns to get rid of everything 
but name and Student ID.

   Make a database for grades (use those names) that looks like this:

     hw1 hw2 hw3 hw4 test1 test2   %   grade
MAX   10  15  20  20   50   100

   The % should be calculated using 30% weight for homework and 70% weight for tests, 
using the actual total points listed (in other words, it should be: 
homeworkPoints/maxHwPts*0.3 + testPoints/maxTestPts*0.70. maxTestPts should change 
if you change the cells under test1 or test2.)

   Compute the grade using [vh]lookup with 90,80,70,60,0 for ABCDF.

   Somewhere below, make 3 boxes for As, Bs and Cs and compute the number of each. One 
way to do this is make an extra column (maybe down below) using "if grade="A"" to put 
a 0 or 1 for each student. Then sum those. A better way is to use countif on the row 
with grades in it.

   You do not need to make this pretty. Save it in your account -- not on the desktop 
-- or it might get deleted.

   Finally, the ranges and grades (90 A, 80 B, ...) mess up our picture. We could put
them waaaay off to the side, but instead, let's put them on sheet2. You can copy
things between sheets. If you can't remember how to talk about "A1 on sheet2" (for the
vlookup) try typing just "=" (start a formula) and then select a box from another
sheet and see what it makes.