Intro: "MadLib" Robot Using Excel
Remember madlibbing with your friends? One person would provide the nouns and verbs, and the other person would provide a sentence with blanks to fill in with them. The fun part was seeing the unexpected connections.
This instructable will show you how to use Microsoft Excel to create many completely random sentences, using the words that you provide.
Here's sentence that my spreadsheet just generated for me: "The sad doll angrily shouts at the squishy boy." and I am quite shocked to learn of it.
What will your spreadsheet tell you?
Step 1: Provide a Simple Vocabulary
Start by opening up a blank document in Microsoft Excel. I am using Excel 2007, but you can do this in earlier versions too. Everything is the same.
You will only need to worry about three areas of the spreadsheet.
1) The active cell will be where most of the typing will happen.
2) You can see the formula from the active cell also in the formula bar. You may prefer to type things there, if your spreadsheet is getting too crowded.
3) We'll be using two sheets in the workbook. You can switch between them, by clicking on these tabs.
Starting with worksheet Sheet1, make a table of words, as shown. To start with, we'll only use nouns, verbs and adjectives. And only four of each.
Later I'll show you how to add more.
Step 2: Prepare a Workspace for Calculations
There are a few formulas that we'll have to write, so go to sheet2 by clicking on the sheet2 tab at the bottom of the window.
Start by labeling rows and columns.
1) Column B is labeled Adjective, Column C is Noun and Column D is Verb.
2) Row 2 will be for counts. Label it '#'.
3) Row 2 and 3 will be for random words. Label them now too.
Our first formula will return the number of words in a particular column.
1) In cell B3 (column B, row 3), type this formula EXACTLY (copy-and-paste from this page, if possible)
This tells Excel, "go to 'sheet1' and count all of the non-blank cells in column A. Subtract one from that" (for the label in the first row.)
2) Copy this formula and paste it into C3. Change the A:A part to B:B
3) Copy to D3 and change A:A to C:C
Step 3: Select a Random Word From List
Its time to select our first random word.
1) In cell B3, enter this formula EXACTLY
2) Paste this formula into B4, as well.
If you're interested (and it is not necessary that you be), there are four function calls in this line. They're nested together so that the inside one is done first.
1) RAND() generates a random number between 0 and 1. That number is multiplied times the number of words in the list. For example, if the random number is .314159 and the number of words is 10, then the formula would return 3.14159.
2) INT trims off any fractional part. In this case, 3.14159 would become simply 3.
3) ADDRESS creates a cell reference. The first parameter is the row and the second parameter is the column. Here, the row would be 3 (from the random number), and the column would be the first column: column A. We also ask for this to be an address in 'sheet1'.
4) INDIRECT goes to the cell reference created in step 3 and finds the word there.
Yes, it's sort of complicated. I didn't just sit down one day and string all of these formulas together. I learned each of them separately over a long time, to solve very different problems. It was a while before I realized that I could use them together in a fun way.
Because I have a weird brain.
Let's move on.
Step 4: Repeat for Other Types of Words
Now that we are successfully generating random Adjectives, we can do the same formulas for Nouns and Verbs
1) Copy formula from B3 to C3
2) Change $B$2 to $C$2
3) Change 1 to 2
4) Copy C3 to C4
1) Copy formula from C3 to D3
2) Change $C$2 to $D$2
3) Change 2 to 3
4) Copy D3 to D4
You should now have a table with a bunch of random words on it.
(Have you noticed that the random words change everytime you change something else on the spreadsheet? That is the coolness of the RAND function. It updates whenever anything in the spreadsheet is changed. :-)
Step 5: Form a Sentence
Now we want to string these words together in a sentence. Type this formula EXACTLY, in cell A7:
="The "&B3&" "&C3&" "&D3&" the "&B4&" "&C4
When you hit return you will see your first random sentence! Want another? Hit 'CTRL+='. (That's the CTRL key, plus the '=', at the same time.) Keep going! The randomness never stops.
How's it work? Excel mashed together everything in that line. The word "The " was smashed into the random word in cell B3, which was smashed into the space (" ") and on into cell C3, etc. Like a five word pile-up, fused together with those ampersands (&).
Step 6: Pretty It Up
When you show this to your friends, you'll probably want to hide all of the calculations and formulas.
You can hide those rows, by
1) click the row labels and drag to select several rows
2) right-click on the row to get a popup menu
3) choose Hide from the popup menu
You can also find many text formatting options on the Home toolbar. I have lost many hours of my life, scrolling through the endless list of fonts that come with Microsoft Office.
In this example, I selected 'Jokerman'.
You may also want to add some instructions, so that your friends know what to do.
Step 7: More, More, More
More randomness results from a longer list of words.
Add as many words as you can think of. Ask your friends for words. Add them to the lists in sheet1.
Step 8: More Better Sentences
Good writers know that, while short sentences get your attention, longer sentences with more modifiers, create a mood. You can add more types of words and change the structure to get more elaborate and outrageous sentences.
FIRST, add another column to sheet1. In this example, I am going to add some adverbs to my sentence.
SECOND, also add another column to sheet2
(you may have to 'unhide' these rows, if you hid them in a previous step. Right Click where the rows should be to get the popup menu. Select Unhide.)
Copy the formulas from the previous columns, and adjust as before.
Thus, E3 should be:
E4 and E5 should be:
FINALLY, add the new word(s) to your sentence. I put my new Adverb in front of the Verb, as is proper.
="The "&B3&" "&C3&" "&E3&" "&D3&" the "&B4&" "&C4
Be careful to join everything with ampersands (&). Also make sure that there are spaces (" ") between words.
Step 9: Cheat
Why not modify the spreadsheet to generate:
- tabloid headlines, like 'Jennifer Aniston and Bigfoot Announce Plans to Marry!'
- new TV series, like 'bionic dog time-travels to mysterious island of supermodels'
- excuses, like 'My bionic time-traveling dog ate my homework.'
- romantic poetry, like 'I love you more than a mysterious island of supermodels'
And now, as a reward for your continued attention, please find my final madlib spreadsheet attached. All of the formulas are ALREADY TYPED IN, but it may need to be prettied up.
Please add comments if you are confused about any of the steps. I will either explain it or fix the step.