====== experimental probability ======
===== simple =====
The goal of these spreadsheets is:
  * to build up context and knowledge about probability
  * to better appreciate the value of theoretical probability
    * theory can be clearer or even simpler to understand than summaries of many cases
  * to create ways to verify theoretical probabilities
    * answers to problems in textbooks can be wrong
    * not all problems come from textbooks

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   |
^ [[https://docs.google.com/spreadsheets/d/1rG7rnPE_heGyNUEpV7ANx9mVjiYV-H7NLBkcLWi91KU/edit?usp=sharing|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  |
^ [[https://docs.google.com/spreadsheets/d/1Wc3VfVuSWAMv7IJMiehbBPuye5C-iz5Ipi0C-ZSxdaE/edit?usp=sharing|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   |
^ [[https://docs.google.com/spreadsheets/d/1y-Q-sN0hZGOSKzODSS74Q6uKsKIYTCUPIUZu7NBI_28/edit?usp=sharing|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:{{:carda.pdf|card (short)}}, {{:cardb.pdf|card }}, and {{:domino_data.pdf|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 [[https://docs.google.com/spreadsheets/d/1AeMsyCWsXbSd8TTgAYwMrhOYcdSPaI5CG5ha3AipYAI/edit?usp=sharing|shuffling]] spreadsheet to make  [[https://docs.google.com/spreadsheets/d/10DZHQc7crryLPi46o3R_J5KIhYqX_alLXU09byxmD9w/edit?usp=sharing| paper data]] for tweaking, formatting, and printing. 
===== more involved =====
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   |
^ [[https://docs.google.com/spreadsheets/d/17WVMgKNUhYY_juEoREN39d3zDVsWESbPxjjO_Kb9krQ/edit?usp=sharing|nonTrans dice]]| offset(), sign(), conditional formatting | this one is more a demo of non-transitive dice...| 
^ [[https://docs.google.com/spreadsheets/d/1sZRjlQQxFn1YF_zclQK1x6KdlnCW4WhGz7mNQ8zRt8k/edit?usp=sharing|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  |
^ [[https://docs.google.com/spreadsheets/d/1et0SBEuS71ygaYioP7M3sSqBF7PdhSIfBr83DngaV2w/edit?usp=sharing|in between Play]] |lots | but mainly for use rather than constructing....   |
^ [[https://docs.google.com/spreadsheets/d/1FEIUHknXm4LQJPFIDW_ToxK7a1dKII1LwxG6kvy9hCY/edit?usp=sharing|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  |
^ [[https://docs.google.com/spreadsheets/d/1yAdz3uJr88WWoTVB6K2wX20v75OJhgd7Zdf16KFfQHw/edit?usp=sharing|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 |
^ [[https://docs.google.com/spreadsheets/d/1dKts7MNF15ncNJMcX8xRE2S61D-4faLYh52nRdBZa-0/edit?usp=sharing|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  |
^ [[https://docs.google.com/spreadsheets/d/1BmR0FhsjLkSP7g87Szcd5VK8I_0S_fIRBOHTXqCGFQc/edit?usp=sharing|list all combos ]] | nested ifs| computes a list of all combinations of 3 from n |
^ [[https://docs.google.com/spreadsheets/d/1BtDCJEuQwVBHxiUOvZdK3zIfv_izCNoX-Wvzv1XmzGQ/edit?usp=sharing|finding triangles]] | offset, if,... | combines the separate letters together  |


====== summaries, graphs, and statistics ======

===== misc. =====

^ spreadsheet ^   sheet   ^   tech   ^   need   |
^ [[https://docs.google.com/spreadsheets/d/1AE9TOjF-Uw4s5IfEmfgB7KL6YLrFSI45YCvwBFD03ZA/edit?usp=sharing
|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 |
^ [[https://docs.google.com/spreadsheets/d/1Wc-Ul4EMfGXKCHG9oUvNFWwj3hq9wkVaybEw0C9vxPk/edit?usp=sharing|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  |
^ [[https://docs.google.com/spreadsheets/d/1C64dgPHJQkhm36gwBnl_wTXQBm-7KQG3l03EkgsOBts/edit?usp=sharing|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 |
^ [[https://docs.google.com/spreadsheets/d/15fzpWJ6YIk4OWLDca22St3ZPtYa8HMmYEOryTNiEJfQ/edit?usp=sharing|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  |
^ [[https://docs.google.com/spreadsheets/d/1nVvgE41Qk3BCCYWiDEDBEKJ9qvAPCu2q51bbnVq5KYU/edit?usp=sharing|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) |
===== graphing =====

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   |
^[[https://docs.google.com/spreadsheets/d/12Xyr9L50CWg-FFbCkDDfWvB8etj9Meck_uuyU5C5wzs/edit?usp=sharing|fake normal data ]] | compare | norminv(), rand(), countifs(B:B,">"&H3,B:B,%%"<="%%&H4)  | frequency table  | 
^ ::: | ::: | **histogram** | show the frequency data |
^[[https://docs.google.com/spreadsheets/d/10uH6QuCxXdaS1os3oUB4Bjp6rxFH5Clx0PI7EDkx4l8/edit?usp=sharing| 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 |
^ [[https://docs.google.com/spreadsheets/d/1SwrkBRw_t5gr9yP8p9C9-uBq5LZ9cGAMlISdkOCu4vw/edit?usp=sharing|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 |
^ [[https://docs.google.com/spreadsheets/d/14C5rmyrWjsarS39iwIe03RcB2fM9B96Xaayr5HAX8Ys/edit?usp=sharing|Maxmillion]] | analysis | countifs, mod, **small**, **large**  | finds 4 most and 4 least frequent numbers | 
^ ::: | ::: | **histogram** | show simple frequency data |

====== admin. ======

^ spreadsheet ^      tech   ^   need   |
^ [[https://docs.google.com/spreadsheets/d/18tKrf8XrNBrv2rfkvABqYDTLj7skJ2Jk7nTNvkLV3ls/edit?usp=sharing|feedback]] | substitute() | text replacement for pronouns etc  |
^ ::: | lookup() or index(match())| using codes to reuse comments   |
^ [[https://docs.google.com/spreadsheets/d/1XYNBxISoSa-9KVMGaO4Xgjmx4RioFAtEOZderRAbub8/edit?usp=sharing|tracking]] | countif(), sumif() | collecting summary data   |