Excel Tutorial Assignment
This is an extra credit assignment. It is worth up to 4 percentage points (see comment at end). It is designed to introduce you to Microsoft Excel, an excellent way to easily ask some simple scientific questions about things including statistics and probability, and which can also actually be a full-fledged scientific modeling platform when needed.
Follow these instructions carefully. Full credit is not guaranteed, and will require strict adherence to the instructions. If you have questions, email me. There are thinking/observation/description questions in this assignment. You can answer those in a different worksheet in the file. If there is not obvious tab at the bottom of the window that says "Sheet 2", then go to the Insert menu and choose "Worksheet". You can put your answers in there.
- Make a static list of numbers.
- Click on cell 1A and type "Static (1)".
- Click on cell 2A and type "1".
- Select the range of cells from 2A to 100A by clicking on cell 2A and dragging downward (it should scroll when you are near the bottom of the window).
- Select from the edit menu: Edit>Fill>Series... (In some versions of Excel, it will be in the Data menu: Data>Fill>Series...).
- Choose Columns, Linear, and a step value of 1.
- You should now have a list of numbers from 1 to 99 in cells 2A to 100A.
- Try it again with other values in the window.
- Make a second static list (easier).
- Click on cell 1B and type "Static (2)".
- Click on cell 2B and type "1", and type "2" into cell 3B.
- Select the range of cells from 2B to 3B by clicking on cell 2B and dragging downward.
- Move the cursor near the lower-right corner of cell 3B. It should change from the hollow plus sign to a smaller, solid black plus sign.
- While the cursor is the solid plus sign, click and drag downward in column B.
- You should get a list of numbers.
- Try it again with a value other than 1 and 2 in the cells. It should extend the pattern.
- Make a calculated list. This is the first calculated formula you will create.
- Click on cell 1C and type "Calculated".
- Click on cell 2C and type "1".
- Click on cell 3C and type "=2C+1". Hit return and it should say "2". This is the result of a formula, which uses the contents of another cell.
- Try changing the value in cell 2C and the formula in cell 3C. What happens if you remove the equals sign?
- Put the regular formula back in cell 3C and the value of "1" back in cell 2C.
- Click on cell 4C and type "=C3+1". Hit return and it should say "3". You have the beginning of a calculated list of numbers.
- Select cell 4C and copy it downward by changing the cursor to a solid plus and dragging downward to cell 100C. You should have a list from 1 to 99.
- What happens when you change the value in cell 2C?
- What is the formula in cell 99C? Describe what happens when you copy a formula that relies on another cell as you did here.
- Make a calculated list that uses values in another list.
- Click on cell 1D and type "Double".
- Click on cell 2D and type "=C2*2". (The asterisk is the Excel symbol for multiplication).
- Using the technique in instruction 3g, above, copy this formula down to cells 2D-100D.
- You should have a list of values that are double those in column C.
- Make a list of random numbers.
- Click on cell 1E and type "Random".
- Click on cell 2E and type "=RAND()".
- Using the technique in instruction 3g, above, copy this formula down to cells 2E-100E.
- Describe the set of values you observe in the list.
- Describe what happens when you make any changes to the document, such as typing a value in some other cell.
- Make a list of random numbers with a different range.
- Click on cell 1F and type "Random 0-2".
- Click on cell 2F and type "=RAND() * 2".
- Using the technique in instruction 3.g, above, copy this formula down to cells 2F-100F.
- Describe the set of values you observe in the list.
- Translate the above values into a coin flip. We'll do this by arbitrarily deciding that values from 0-1 are "heads" and values from 1-2 are "tails".
- Click on cell 1G and type "coin flip".
- Click on cell 2G and type "=IF(F2<1,"Heads","Tails")". This means that to calculate the contents of the current cell, it makes a decision (the "IF" statement). The IF statement has three parts: the test, the value to use if the test is true, and the value to use if the test is false. So, in this case, if 2F is less than 1, it will put the word "Heads" in cell 2G, and otherwise it will put "Tails" in 2G.
- Using the technique in instruction 3g, above, copy this formula down to cells 2G-100G.
- Count the number of heads and tails in the list.
- Click on cell 102G, and type "=COUNTIF(G2:G100,"Heads")". This means it will count all the cells between 2G and 100G that contain the text "Heads".
- Create a formula in 103G to count the number of cells that represent "Tails".
- What is the sum of these two values?
- Make a list of random "die rolls".
- Type a heading in 1H, as we did above.
- Fill column H from 2-100 with random numbers between 0 and 5, as we did above.
- Now we could make a column I with a bunch of IF statements to call all the values between zero and one a die roll of "one", etc., but there's an easier way, using math.
- First you'll do it with two separate cells, then turn them into a single formula: Go back to cell 2H, and type "=(RAND() * 6) + 1".
- Now go to cell 3H, and type "=FLOOR(H2,1)". This command will discard all of the value following the decimal point.
- You can combine these two commands into one as follows: go back to cell 2H, and type "=FLOOR((RAND() * 6) + 1, 1)".
- Using the techniques above, copy this cell down into 2H-100H.
- Count the number of sixes you've rolled in cell 101H.
- Finally, use these techniques to simulate drawing a card from a standard 52-card deck.
- You should keep track of only the suit of the card, not the rank.
- Use columns J and K for this part.
- Tabulate at the bottom of the column the number of hearts drawn.
- Email the file you make to Dave (email@example.com).
- Use "[Geo 204] Excel Assignment" as the subject line.
Credit for this assignment will be in the form of percentage points towards a perfect course score (1000).
What this means:
- If, after the final exam, you have a score of 900, and have received full credit (4 percentage points) for this extra credit assignment, then your final score will be moved 4% of the way towards 1000. So you would get 4 points (4% of 100) making your final score 904.
- If, after the final exam, you have a score of 500, and have received full credit (4 percentage points) for this extra credit assignment, then your final score will be moved 4% of the way towards 1000. So you would get 20 points (4% of 500) making your final score 520.
Why am I using this weird extra credit system?
I'm doing it this way because in past courses, people have complained that they felt that they had too much work to do, and that they felt like they had to do the extra credit to keep up. This way, the extra credit helps those at the bottom of the class more than those at the top, and since there is no final course curve, nobody should feel obligated to do this assignment (well, unless they are failing, I suppose).
David M. Hirsch
Modified on Tue, Sep 06, 2011 at 3:36 PM