The goal of these spreadsheets is:
Here are some minimal examples that have spreadsheets simulate random events. They build in difficulty and spreadsheet skills. The aim is to only introduce tech that is necessary for simulations.
An additional reason for using these spreadsheets would be to create endless examples of instances for students to classify.
| spreadsheet | new tech | need |
|---|---|---|
| simple coins | randbetween(0,1) | simulating random events (coin toss) |
| countif(A:A,1) | counting specific things in a column | |
| count(A:A) | counting all the numbers in a column | |
| sum(A1:D1) | summing a part of a row | |
| if(A2=1,”H”,”T”) | using H, and T instead of 0,1 | |
| simple dice | +, -, max, min, abs | using different ways of looking at the dice events |
| offset($P$2,3,0) | to use dice symbols rather than numbers | |
| simple cards | conditional formatting | making aspects clearer / focusing attention in particular events |
As a prep for doing spreadsheets, if may be a good idea to work start with paper tabulation. Here are some pdf data:card (short), card , and domino. The card (short) and domino data can be used as both set with replacement and without depending on the direction you read the data: reading in the row is without replacement, and reading down a column is with replacement. The card data uses bigger images for the cards so each shuffle of the 52 cards takes two rows. Perhaps these could be plasticized and reused…
I used shuffling spreadsheet to make paper data for tweaking, formatting, and printing.
These examples are less stratified and more about using rather than creating (and understanding). So there is no need to do them in order.
| spreadsheet | tech | need |
|---|---|---|
| nonTrans dice | offset(), sign(), conditional formatting | this one is more a demo of non-transitive dice… |
| in between | mod(A2,13) | getting remainders (when dividing by 13) |
| extracting denomination from a card represented by 0-51 | ||
| iferror(L2/sum(L2:M2),”-”) | putting ”-” when dividing by zero | |
| in between Play | lots | but mainly for use rather than constructing…. |
| yahtzee | bitor(), power(), dec2bin() | binary manipulation for finding straights |
| product(), countif($A2:$E2,A2) | calculating the multiples (pair, 2 pair, 3K, full house, 4K, 5K) | |
| lookup() | labeling rolls : pair, 2 pair, 3K, full house, 4K, 5K, small, large | |
| dominoes | product(), countif($A2:$E2,A2) | calculating the multiples (pair, 2 pair, triples, quad…) |
| lookup() | labeling selections of three tiles (with and without replacement) : pair, 2 pair, triples, quad… | |
| randbetween(0,a3) | combines two randbetweens that produces the wrong distribution for random tiles | |
| text combos | mod(), floor(), offset() | turns a random number into letters of the alphabet |
| textjoin() | combines the separate letters together | |
| *a*b*, countif() | uses wildcards to find patterns in text | |
| list all combos | nested ifs | computes a list of all combinations of 3 from n |
| finding triangles | offset, if,… | combines the separate letters together |
| spreadsheet | sheet | tech | need |
|---|---|---|---|
| Pascal's triangle | simple | conditional formatting | colour odd entries |
| skip | =if(N1+P1<>0,(N1+P1),””) | not displaying zeroes | |
| merge | merging cells | making a brick pattern | |
| done with format painter (and alternating rows) | |||
| combination | =if($A2>=B$1,combin($A2,B$1),””) | combin(x,y) is “x choose y” | |
| $A2 and B$1 allow for easy formula copying | |||
| bridges | simple bridge | fill format | add colour for “bridges” |
| bridge prob. maker | IF(D5=1,C5,0)+IF(E4=1,E3,0) | conditionally if a bridge is present, count the paths | |
| and(C3>0,mod(row()+column(),2)=1) | conditional formatting to better show bridges | ||
| quartile | task | web link | describes how to collect data using a web site that times a simple addition task |
| soln. check | small(range,#), quartile.exc() | sorting without using sort / help with calculation | |
| exactlyOneDoubleZero | calculated | named range | more understandable formulas, e.g. ”=numDigits*(B6+C6)“ |
| recursion | this sheet embodies [a,b,c,d] → [a, n(a+b), a+d, n(c+d)] | ||
| direct | textjoin(” ”,true, columns) | combines text columns | |
| normal table | table lookup | normdist($A15-C$1,0,1,true) | used to produce a table of z-score probabilities |
| graph lookup | normdist($A15-C$1,0,1,true) | used to produce a chart (minor and major grid lines added) |
These examples will be more tricky since they rely on Sheet's chart tools which are different from Excel's tools.
| spreadsheet | sheet | tech | need |
|---|---|---|---|
| fake normal data | compare | norminv(), rand(), countifs(B:B,”>”&H3,B:B,"<="&H4) | frequency table |
| histogram | show the frequency data | ||
| fake linear correlation | simple | SLOPE, INTERCEPT,CORREL(C:C,B:B) | create the line of best fit |
| scatter plot, trendline | shows an example of a graph with 2D data | ||
| outliers | scatter plot, if($A3=1,””,B3) | interactively dropping points from data sets | |
| not normal | cantor trans | DEC2BIN(), MOD(), QUOTIENT() | in concert, changes decimal numbers to 16 bit binary |
| sumproduct() | used to change a ternary number (base 3) to decimal | ||
| scatter plot | used to change show [0,1]'s transition to the Cantor set | ||
| norm2cantor | countifs(T:T,”>”&W3,T:T,"<="&W4) | frequency table | |
| norminv(), int(), rand() | create fake data with a normal distribution | ||
| count(), average(), median(), max(), min() | summary statistics | ||
| histograms (overlaying) | show the frequency data | ||
| Maxmillion | analysis | countifs, mod, small, large | finds 4 most and 4 least frequent numbers |
| histogram | show simple frequency data |