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
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.

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
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

summaries, graphs, and statistics

misc.

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)

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
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

admin.

spreadsheet tech need
feedback substitute() text replacement for pronouns etc
lookup() using codes to reuse comments
tracking countif(), sumif() collecting summary data
sheets.txt · Last modified: 2020/05/25 01:01 by morey
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki