Tech Camp Presentation, Fall 2011, CCSF

Fun with Random Numbers in Excel (and most other spreadsheet applications)

With Excel's random number generator, ESL students can create word games and other engaging applications while getting a little taste of computer programming.

How to begin? First, play some Boggle.

(In case you have never played boggle, the idea is to make as many words as possible using letters from a grid of random letters. The letters of a word do not need to be in a straight line, but their squares must be touching each other. At the end of the allotted time, play stops and the letters in all the words formed by each player or team are counted up. The player or team with the highest letter count wins.)


Start with a completed Excel version of Boggle. You can find my version here:
Boggle. Put the Boggle board up on the big classroom screen. Find a few words so students will get an idea of how to play the game. Divide the class into two teams, and decide on a duration for the game (8 min. is good). Open two Word documents for use as score sheets, one for each team, and put them on the big screen, on either side of the Boggle board. Click anywhere in the Excel document then press F9 a few times until you get what looks like a good assortment of letters. Start the game. When a student shouts out a word, verify that it's correct, then type it into the score sheet for that student's team. When time is up, count up the letters in the words to see which team won.

Next, show students how to make the Boggle game

...without going into detail about how it works. Just have students do this:

1. Copy the following formula into some cell in the upper left portion of their screen: =CHAR(RANDBETWEEN(65,90))
2. Press enter.
3. Click on the cell.
4. Drag the little black square in the bottom right corner of the cell down 4 cells.
5. Drag that little square 4 cells to the right.
6. Boost the font size up to something like 30 points.
7. Click anywhere to deselect.

You should wind up with a 5 x 5 grid of random letters. Press F9 and the letters should change. Students can add colors and other formatting as desired.
Finally, invite students to form groups of 2 to 4 to play boggle. Two teams of 2 might also work well. Each group gathers around one computer and each student or team writes the words they come up with on a piece of paper. An online timer can be used to time the game. (Search for "countdown timer".)

Finally, explain how it works

1. Show that the formula used in the boggle game really consists of two separate parts-- RANDBETWEEN and CHAR.
2. Show how the CHAR function works by doing some examples and encouraging students to try others-- =CHAR(1), =CHAR(100), =CHAR(88), etc.
3. Figure out by trial and error the numeric codes for "a" and "z" and a few other letters.
4. Show how the RANDBETWEEN function works by doing some examples and encouraging students to try others-- RANDBETWEEN(10,12), RANDBETWEEN(500,509), etc.
5. Show how the RANDBETWEEN function is nested inside of the CHAR function and explain that RANDBETWEEN creates random numbers which are then fed into the CHAR function which turns the numbers into letters.

A variation on Boggle: Jumble

I haven't tried this, but one might also make a simpler game which I'll call "Jumble". To make Jumble, use the same formula as that used in Boggle, but instead of a grid, just make a row of 5 to 10 random letters. The rules would be the same as in Boggle, except that any of the letters can be used in any order to create words.

Another simple program: the math trainer.

This one is less fun and less language oriented, but can be useful for students who are studying math, or who have children who are studying math, or who, like me, are getting older and beginning to lose their minds.
You can find a completed program here:
Math practice spreadsheet

Press F9 to get a fresh set of math problems. I'll leave it up to you to figure out how to make this one. Just click on a cell to see the formula it contains in the formula bar up above. Let me know if you have any questions. You know my e-mail.
Now I want to go off topic a little bit to mention a couple of...

Excel projects you might want to do before any of the above (no formulas involved)

Countries project

Put the names of countries students are from in column A. Put the number of students from each country in column B. Use the data to make various kinds of bar graphs and

Average Monthly Temperatures project

Put the names of the months in column A. Put San Francisco's average low temperature for each month in column B. Put San Francisco's average high temperature for each month in column C. (The data is easy to find online.) Make a double bar graph showing average monthly high and low temperatures for San Francisco.

Another Excel project I recommend involving formulas (but no random numbers)

This project uses formulas in more traditional, business-oriented manner.
First, make sure everyone knows their farm animal vocabulary by practicing here:
Farm animal vocabulary practice

Next, make a spreadsheet like this one: Farm animal monthly sales report
You can find more good ideas for business-oriented projects here:
Basic Excel worksheets

One suggestion: Make your spreadsheet projects deal with highly visualizable things (like farm animals), so that students with an artistic bent can go crazy with clipart and images found online.

Some general suggestions for computer lab work

Don't worry too much about formatting. Keep it basic, then wait for a student to come up with something fabulous. Copy their work onto your flash drive, show it on the big screen, and, ideally, get the student to explain how to do it to the class.

Always carry something pointy like a pencil for pointing at things on students' monitors. Fingers are too fat.