I teach English in China to seven year old students. We have been learning phonics and if effort to help my students pay attention to phonetic words, I decided to create some random BINGO cards. It is quite simple to do.
The first thing we will want to do is create our word list. This can be as many or as few words as we want. You should leave a few rows empty before the start of your list. Write all of your words in a column (see above screenshot).
Now that we have a word list, we will need to have the spreadsheet count the number of words (this is so we can snatch a random word from the word list). This is the code to use:
=COUNTIF(B5:B999,">.")I used the COUNTIF command to count from the B5:B999 (the last number should be larger than the number of items you plan on having in your word list). Now we have the number of words in our list.
Now we will display a random word from the list. See code below:
=INDEX(B5:B999,((RAND()*B4)+1))From the same group of numbers (B5:B999) we will pick a random number. The *B4)+1 means that it will only look as far as the number of items that we have (otherwise we would most likely end up with nothing because our search field is 994 cells, but only 57 of them actually have content).
In a new sheet we can create our BINGO template. This you can do the formatting to your choosing so that it looks nice (I promise the only reason I use the Comic Sans font is not because it looks nice, but because it is closest to how I expect my students to write letters).
Here is the code we will put in every BINGO cell:
=INDEX($'Word List and Instructions'.$B$5:$B$999,((RAND()*$'Word List and Instructions'.$B$4)+1))We are extracting the data from B4 in the previous sheet. Make sure to include the dollar signs. This makes sure that when you copy and paste, it will still reference B4.
Our last step is to print or export to PDF (make sure you only do one sheet/page).
Once this has been done, you can press the F9 key to generate a new random sheet.
Here is a link to the file on Google Docs if you would like to see how it works.
Here is a link to the original Libreoffice file.