Excel Worksheet for Data Management - Resurrection C.S.S.



Excel Worksheet for Data Management

Attempt the following activities to develop your skills using a spreadsheet. You may find much of this is very applicable to your project.

For assistance, you can learn about EXCEL functions by clicking the ’fx’ button/icon just over column C.

Simulate Tossing a Coin

Use Column A and B to show a series of coin tosses. Column A should have a heading “TRIAL” and show the trial numbers (1-1000). Column B should have a heading “RESULT” and show the results (H or T).

1. Type in your headings in cells A1 and B1.

2. Fill in the trial numbers by starting the sequence (1,2,3), then filling in the rest. You can do this by dragging the cell pattern downwards using the “Fill Handle”

3. In B2 create a formula that randomly generates a “T” or a “H”. This will require an IF statement and a RANDom number. RAND returns a number between 0 and 1. The IF statement will permit you to change this random number into a “H” or “T”. HINT: IF(RAND()…

4. Drag this formula down to fill the 1000 trials.

5. Determine how many “H”eads appeared in your list of 1000 trials. You can use the COUNTIF function for this.

6. Using you result, calculate the probability of this event (Flipping a Head) over the 1000 trials. Place your results in column D.

Simulate a Die Roll (or Spinner)

Use Column A to E to show a series of dice rolls. Column A should have a heading “TRIAL” and show the trial numbers (1-1000). Column B to D should have headings “DIE 1”, “DIE 2”, and “DIE 3” and showing the results (1 - 6). Column E should have a heading’ “SUM”.

1. Type in your headings in cells A1 to E1.

2. Fill in the trial numbers by starting the sequence (1,2,3), then filling in the rest. You can do this by dragging the cell pattern downwards using the “Fill Handle”

3. In B2 create a formula that randomly generates numbers 1 to 6. Use the ROUNDUP and RAND command to ensure you only get whole numbers between 1 and 6. (RANDBETWEEN is another quick alternative – you may need to install the Analysis ToolPak, under TOOLS, Add-Ins.)

4. Drag this formula down and across to fill the 1000 trials of all three dice.

5. In Column E, add up (SUM) the three dice of that trial (eg. B2, C2, D2)

6. Using the column of sums you can determine the number of times each of the possible sums (from 3 to 18) occurred using the COUNTIF function.

7. Use the chart wizard to graph the probability distribution for the sum of rolling three dice.

Simulate Cutting a Card

Use Column A to F to show cutting a deck of cards a number of times. Column A should have a heading “TRIAL” and show the trial numbers (1-1000). Column B and C should have headings “SUIT”and columns D and E should have the headings “VALUE”. Column F should have a heading “CARD”.

1. Type in your headings in cells A1 to F1.

2. Fill in the trial numbers by starting the sequence (1,2,3), then filling in the rest. You can do this by dragging the cell pattern downwards using the “Fill Handle”.

3. In B2 create a formula that randomly generates numbers 1 to 4. Use the ROUNDUP and RAND command to ensure you only get whole numbers between 1 and 4. (RANDBETWEEN is another quick alternative – you may need to install the Analysis ToolPak.)

4. In C2 create a formula that uses an IF function to determine if the suit of the card is hearts, clubs, diamonds or spades.

5. In D2 create a formula that randomly generates numbers 1 to 13. Use the ROUNDUP and RAND command to ensure you only get whole numbers between 1 and 13. (RANDBETWEEN is another quick alternative – you may need to install the Analysis ToolPak.)

6. In E2 create a formula that uses an IF function to determine if the card is an ace, king, queen, or jack. Otherwise the value of the card will be the number that was generated.

7. In F2 use the CONCATENATE function to combine columns E and C into a text string. Eg “5 of Hearts”. (Mr.Excel p184)

8. Use the COUNTIF function to examine column F to determine the experimental probability of cutting the queen of spades.

9. Use the COUNTIF function to examine column C to determine the probability distribution for the four different suits.

Simulate the Monty Hall Problem

Use Columns A to D to show cutting a deck of cards a number of times. Column A should have a heading “TRIAL” and show the trial numbers (1-1000). Column B should have the heading “Goat”. Column C should have the heading “GUESS”. Column D should have the heading “STAY”. Column E should have the heading SWITCH”. Column F should have a heading “STAY or SWITCH”.

1. Type in your headings in cells A1 to F1.

2. Fill in the trial numbers by starting the sequence (1,2,3), then filling in the rest. You can do this by dragging the cell pattern downwards using the “Fill Handle” (Mr.Excel p115)

3. In B2 create a formula that randomly generates numbers 1 to 3.

4. In C2 create a formula that randomly generates numbers 1 to 3.

5.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download