Spreadsheet problem generator: creating a basic word problem generator

Posted by Greten on 10 May 2019 under Tips

Word problems are essential in teaching science and mathematics. Students are more likely to appreciate the connection of their lessons to real-life scenarios rather than solving them in purely abstract concepts. Kindergarten students enjoy the lessons more with one apple plus one apple rather than just plain one plus one. High school students will more appreciate problems about two airplanes from the same airport heading to different destinations rather than using the Law of Cosine on a triangle drawn on the board.

Hence, it is useful for teachers of science and mathematics, and possibly economics and other math-intensive subjects, to somehow automate the process of creating problems. You can create different versions of a word problem using spreadsheets. I personally tried this method using the following:

• LibreOffice Calc 6.2
• MS Excel 2016
• Google Sheets

So how does this word problem generator works?

For example, you have this word problem:

A piece of paper has a length of 27.9 cm and width of 21.6 cm. If I cut a circle with 5 cm radius from that paper, what is the area of the remaining sheet?

From this, you can create a very similar problem. Something like:

A piece of paper has a length of 30.5 cm and width of 22.9 cm. If I cut a circle with 3.5 cm radius from that paper, what is the area of the remaining sheet?

You can see that this problem is exactly the same, with only the numerical values being the difference.

The answers in the two sample problems above are 524.10 cm² and 659.97 cm² respectively (using π = 3.1416). However, for every variation of the problem that you create, you need to solve them all over again.

The problem generator works by providing you with a template wherein you can change one or more variables, and the answer changes instantly. You can then copy and paste this problem, together with the answer, as a new problem in a notepad or word processor.

These similar word problems that vary only in numerical values can serve different purpose. Say, you can use one as part of Set A and the other as part of Set B, to be distributed alternately to students based on their sitting arrangement. You could also use three similar problems with one as example, one as home work and one as quiz.

If you are using them in an elearning platform, you can even have three or more versions of the same word problem, where the system will pick one at random and assign it to a student. You can do this in Moodle and Articulate Storyline 2 using the question bank feature, and in Adobe Captivate 10 using the question pool feature.

How to create problem generator using spreadsheets?

While it might be easy to encode the numerical values involved in a problem in a spreadsheet, it's sometimes difficult to remember the text and scenario that surrounds the numerical values.

To keep the context of the problem while we change the numerical values, use the & (AND operator) to combine strings with cell references.

Step 1: Identify the given values.

Using the first example as our "template problem", the given values are length and width of the paper and the radius of the cut-off circle, which are 27.9 cm, 21.6 cm, and 5 cm respectively.

Step 2: Identify the value (or values) that we are trying to solve.

Again, using the first problem, the value that we are trying to solve is the area of what remains of the paper after the circle was cut-off.

Step 3: Construct a single equation that will connect all these variables to the answer.

In our example, the first solution that is likely to come to your mind is to solve the areas of the rectangle (paper) and the circle separately and then subtract the area of the circle from the area of the rectangle. While you can spare extra cells in the spreadsheet to contain these areas, a more elegant solution is to combine the equations into one equation.

To make a single equation, we need to combine all the equations to be used. In our example, it will be as follows (A stands for "area"):

Step 4: Type descriptive labels for the variables.

You can type the labels side-by-side in adjacent cells or with just one cell in-between each two of the labels. I would suggest you type them along the same row or along the same column, with the label of the value to be solved distinguished in some ways (say, make it bold or italics). You can also include the units in the labels. Since the mathematical functions of spreadsheets can only process numbers, it is important to be consistent with units.

Step 5: Highlight the cells adjacent to the cells with descriptive labels.

This is the cells where you will enter the values. You can highlight these cells by putting borders or by filling them with a different color. If the labels are in one row, highlight the cells above or below these label cells. If the label cells are in one column, highlight the cells on the right of the labels.

Step 6: Enter the given values inside the highlighted cells adjacent to the labels for given values.

Do not enter anything else aside from numerical values. Also, leave the highlighted cell for the answer blank.

Step 7: Find the cell names of cells where you encoded the given values and take note of them.

You may write them in Notepad or Notepad++, on a piece of paper, or just remember them. You will use these cell names in steps 8 and 10 later. For this particular example, the cell name of the paper length is B4, the paper width is D4 and the circle's radius is F4.

Step 8: Move the cursor to the cell that will contain the answer and type the equation you found in Step 3, with the cell names of the given values in place of variables.

For this example, encode:

= B4*D4 - 3.1416 * F4^2

Note that we used pi = 3.1416 instead of the Pi function that comes with the spreadsheet applications. The reason is that the students will probably solve this problem manually and therefore, we need to use a limited number of decimal digits for pi. It's usually 3.1416 or 3.14 for lower grade levels. Of course, depending on the recipients of your quiz, you may use the Pi function.

Step 9: Highlight some of the cells under the ones that contain the number and then merge them as one cell.

The number of cells that you need to merge depends on how long the word problem is. You might also need to use the wrap text option so that your word problem can occupy two or more lines and not disappear at the right of the last cell.

You might also want to highlight the merge cells by putting a border or changing its color so you could easily find it.

Step 10: Type the word problem in the merged cell as usual but with the following difference:

• Start with an equal sign (=) just like how you start any operations or functions.
• Replace all numerical values with the cell names, which you took note earlier in step 8.
• Put AND operator sign or ampersand (&) before and after each of the cell names. The exceptions are as follows:
  • If the cell name is at the beginning (right after the equal sign), only one ampersand after it is needed.
  • If the cell name is at the end, only one ampersand before it is needed.
  • For two cell names that come in succession, only one ampersand between them is needed.
• All text and symbols that are not cell names must be enclosed in double quotes.

The general form of implementing step 10 is shown below:

=<cell name>&"<text here>"&<cell name>&"<text here>"&<cell name>&<cell name>&"<text here>"&<cell name>

Using earlier example, we will have something like this:

="A piece of paper has a length of "&B4&" cm and width of "&D4&" cm. If I cut a circle with "&F4&" cm radius from that paper, what is the area of the remaining sheet? Answer: "&H4&" squared centimeter"

If you followed steps 9 and 10, you should have something like this:

To copy the problem, select the merged cell, copy, and then paste it in a notepad or a word processor. When copying, select the cell but do not double click it such that the vertical cursor appears and you can edit what's inside the cell.

You can now use this spreadsheet to create similar problems but with altered values for future use in your class.

Conclusion

The setting-up of problem generator can be time consuming. However, the time you spend working on it can be offset by the fact that you can create similar word problems without having to compute the answer again.

You can download the LibreOffice Spreadsheet (ODS) file and open it using LibreOffice Calc to study it. For Excel 2016, you can download this XLSX file. The Google Sheet file is shared here.

Notice that I used whole and decimal numbers here. You can use this method in any problems that contain only whole or decimal numbers with or without units at the end. Numbers that require certain formatting such as fractions, time in hh:mm or hh:mm:ss formats, percentages, and currencies, are not covered in this post. The problem generator with special number formatting is covered in my next post.

Last updated on 28 May 2019.

Share your thoughts

* Required. Your email will never be displayed in public.