This is a group assignment using spreadsheets in Google Drive. Make sure to use the "copy" hint from class so the professor can edit the spreadsheets without changing the originals.
GPA Calculator
Create a spreadsheet that computes a weighted average for a grade point average.
The spreadsheet should:
Allow students to input course names, letter grade received, and course credits.
A student can enter at least 20 courses.
Provide the weighted GPA (allowing for +/- grades), rounded to two decimal places.
Have clear directions to students about how to use the spreadsheet.
VLOOKUP might be a useful command to assign grades to point values (note: last option "false" for exact lookup)
check answers at UWEC GPA calculator (note: your calculator will work a little differently)
Collatz conjecture
Create a spreadsheet that explores the Collatz conjecture.
Take any natural number, \( n \). If \( n \) is even, compute \( \frac{n}{2} \). If \(n\) is odd, compute \(3n+1\). Repeat the previous process until \(n=1\). The conjecture states that all natural numbers will eventually go to 1.
The spreadsheet should:
Allow a student to input a number. Then the spreadsheet will give the sequence (≤ 150 steps) and the number of steps. (Note: If the number of steps is greater than 150, you may just say so.)
Have clear directions to students about how to use the spreadsheet.
Possible helpful commands: ISEVEN, IF, COUNT, OR
Nested IF may also be useful, e.g., IF(test, true, IF(test, true, false))
The number 27 has 111 steps. This is the largest number of steps for numbers 1-50.
Create a spreadsheet that helps students find the solution to a system of two linear equations.
The spreadsheet should:
Allow a student to input two linear equations in standard form.
Have clear directions to students about how to use the spreadsheet.
Give answers as a clearly labeled ordered pair. If no ordered pair exists, the spreadsheet should let the student know why no solution exists: either parallel (non-intersecting) or same line (infinite solutions).
IF may be a helpful command.
What does an ordered pair look like?
CONCATENATE can be used to combine text and formulas, e.g., =CONCATENATE("x-value:", A1)
Notice the previous example is not an ordered pair!
Challenge +1 MTP
Also show the graph of the intersection, possibly using a scatter plot with treadlines.
Can't Stop (Monte Carlo simulation)
Create a spreadsheet to estimate probabilities for the game Can't Stop.
The spreadsheet should:
Simulate rolling dice at least 1,000 times using random numbers (Monte Carlo simulation).
Allow a student to select three target values between 2 and 12 inclusive.
Compute the experimental probability that at least one target value occurs, based on the game rules.
Have clear directions to students about how to use the spreadsheet.
Explain how the spreadsheet computes the probabilities.
Game rules:
Choose three numbers (2-12).
Roll four six-sided dice.
Separate the dice into two pairs. There will be various ways to do this.
Find the sum of each pair. Is your number among one of the sums?
For example: Say a person chooses 2, 6, and 7 as a target values. Now the student rolls a 3, 3, 4, 4. Depending on how I pair up the dice, I get the following sums: 6, 7, 7, 7, 7, 8, with a total of 5 matches. That said, you should only count this result as 1 towards the probability count, not 5, because each die roll can contribute at most one to the count.
IF may be a helpful command.
COUNTIF may be a helpful command.
RANDBETWEEN may be a helpful command.
\( P(6 \text{ or } 7 \text{ or } 8) ~= 1192/1296 \)
Challenge +1 MTP
Also compute the theoretical probability.
Farkle (Monte Carlo simulation)
Create a spreadsheet to estimate probabilities for the game Farkle.
Note: We will assume that six dice are rolled only once representing the first roll of a player's turn.
The spreadsheet should:
Simulate rolling six dice at least 1,000 times using random numbers (Monte Carlo simulation).
Compute the highest possible score of each roll based on Farkle's standard scoring.
Compute the expected value based on the computed scores.
Compute the probability of having a FARKLE, which means no dice scored.
Explain how the spreadsheet works.
RANDBETWEEN may be a helpful command.
COUNTIF may be a helpful command.
IF may be a helpful command.
Nested IF statements may be helpful.
Be careful of 6-of-a-kind, which is two 3-of-a-kinds.
Challenge +1 MTP
Also include scoring and expected value for the "adding" scoring variation for 4-of-a-kind, 5-of-a-kind, and 6-of-a-kind.
Example: Let's say the roll was 3-3-3-3-5-6. Three 3's are worth 300 points. The fourth 3 adds on that value, so 3-3-3-3 is worth 300+300 = 600 points!. Don't forget the 5 is worth 50. So this situation is worth 650 points. The effect is cumulative so a roll of 3-3-3-3-3-6 would score 300+300+300 = 900 points.
Discussion questions
N/A
Submission
Submit the URL for your assignment to the correct Canvas discussion board. Recall that the discussion board is used as a way to share ideas between (preservice) teachers.
Please print relevant pages if you want more detailed feedback.
Special note: To get a link for your spreadsheet, please use the following directions to share in copy mode
Collaborative spreadsheets scoring guide 20 MTP
GPA calculator
Requirements
Allows for 20 classes, letter grades, and course credits 1 MTP
Computed GPA is correct 1 MTP
Directions for students are _____ clear.
2 - very
1
0 - not
Collatz conjecture
Requirements
Student can enter number and see the Collatz sequence (up to 150 steps) 1 MTP
Student can enter number and see how many steps (if less than 150) it takes 1 MTP
Directions for students are _____ clear.
2 - very
1
0 - not
Linear system
Requirements
Students can input linear equations in standard form 1 MTP
Clearly identifies "infinite solutions" because lines are identical 1 MTP
Clearly identifies "no solutions" because lines are parallel 1 MTP
Clearly gives an ordered pair when single solution exists 1 MTP
Directions for students _____ clear.
2 - very
1
0 - not
Can't Stop probabilities
Requirements
Students can input a target sum for computation 1 MTP
Probability is approximately correct 1 MTP
Directions for students are _____ clear.
2 - very
1
0 - not
Explanation for probability computation is _____ clear.
2 - very
1
0 - not
Farkle
Requirements
Simulates at least 1,000 rolls of six dice 1 MTP
Expected value is approximately correct 1 MTP
Scoring is correct for 1's and 5's 1 MTP
Scoring is correct for 2's, 3's, 4's, 6's 1 MTP
Directions are _____ clear.
2 - very
1
0 - not
Challenges
Linear: Shows graph of the intersection point between two lines +1 MTP
Can't Stop: Computes theoretical probabilities for the Can't Stop game +1 MTP
Farkle: Includes variant scoring & expected value +1 MTP
Aesthetic bonus
Instructor subjectively feels submission was particularly well-designed +1 MTP