Creating your word problem generator using Calc spreadsheet

Posted by Greten on 25 Feb 2012 under Free Instructional Applications

Word problems are one of the essential tools 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. Kindergarden students more enjoy the lessons with one apple plus one apple rather than just plain one plus one. High school students will more appreciate problems about two airplanes from same airport heading to different destination rather than using the Law of Cosine on some plane triangle drawn on the board.

Hence, it will be very much useful for teachers of science and mathematics (and possibly economics and other math-intensive subjects) to be able to somehow "automate" the process of creating problems. While it is not a good to rely on computers to do all the work for us and I'm not even sure if there's already a technology to automate the generation of word problems, we could readily automate the creation of a variety of a word problem using spreadsheets such as LibreOffice/OpenOffice Calc. Click here if you are confused why there are two brands of Calc.

So how does this word problem generator works?

Let's say you have this kind of word problem:

A piece of paper has 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 easily create another very similar problem. Something like:

A piece of paper has 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 similar, with only the numerical values being the difference. These similar word problems that varies 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.

The answers in the two sample problems above are 524.10 cm2 and 659.97 cm2 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 is instantly changed as well. You can then copy and paste this problem, together with the answer, as a new problem in a notepad or word processor.

How to create problem generator using spreadsheets?

While it's 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 scenario of the problem intact while we change the numerical values, the key here is to use the & (AND operator) to combine strings with cell references. The steps that I will discuss in this article can be done in LibreOffice/OpenOffice Calc. It can also be done in MS Excel for those using it; the syntax of formulae is actually the same but the user interface can be different.

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. It might confuse you that I already provided the answer to the problem, but our objective here is not to solve it but to build similar problems using spreadsheet. It is important that you are absolutely certain that we have the correct answer to the template problem before encoding it in the spreadsheet.

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

What I'm trying to say here is this. In our example, the first solution that is likely to come to your mind is to first solve the area of the rectangle (paper), next is to solve the area of the circle, and finally is to subtract the area of the circle from the area of the rectangle. Basically, we use the equations for areas of rectangle and circle separately and then used simple subtraction to get the answer. We cannot use these separate equations and steps in our spreadsheet.

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"):

Aremain = LW - pi*r^2

All the steps that follow will be performed in a spreadsheet.

Step 4: You type the name of the variables side-by-side in adjacent cells or with just one cell in-between each two of the names. I would suggest you type them along the same row, with the name of the value that we are trying to solve distinguished in some ways (say, make it bold or italics).

Step 5, put border on the cells immediately below the cells where you typed the names of the variables. You can put borders in a cell by right-clicking on it. Then, select format cells from the options that appeared. Next select the "borders" tab. Finally, click the button that indicates enclosed border (should be obvious which one) and click "Ok". You can do this in both MS Excel and OO Calc even though their interfaces look slightly different.

If you followed Steps 4 and 5 correctly, you will get something like this:

problem generator labels

Step 6: encode the given values inside the cells with border below them. No need to include the units; just encode the numerical values of the given.

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, in a piece of paper or just remember them. We 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 encode the equation you found in Step 3, with the cell names of the given values in place of variables.

For this example, we should encode:

= B4*D4 - 3.1416 * F4^2

Note that we use pi = 3.1416 instead of the Pi function that comes with Calc (or MS Excel), which generates 3.14159265358979. 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.

At this point, you should have something like this.

problem generator filled

Step 9: highlight some of the cells under the ones that contain the number and then merge them as one cell. The number of the 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 can do this by right-clicking while the merged cell is active, then click "format cells", click the "alignment", and lastly check "Wrap text automatically". You might also want to put border on the merged cell 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 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 comes 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 length of "&B4&" cm and width of "&D4&" cm. if i cut a circle with "&F4&" 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:

problem generator final

Noticed the difference between what you can see inside the cell and what's in the input line above? Simply change the values of some or all the given variables and the numbers in the problem, as well as the final answer, changes automatically,

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


The setting-up of problem generator can consume some time but it's actually easy. Moreover, 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 Open Document Spreadsheet (ODS) file and open it using LibreOffice/OpenOffice Calc to study it. Microsoft Excel 2010 can open ODS file but might not in its best appearance.

You will noticed that I used real numbers here. This technique can be used in any problems that contain only real numbers (integers and decimals) with or without units at the end. Numbers that require certain formatting such as fractions, percentage and currency, are not covered by this tutorial. If you have average proficiency in spreadsheet software, you would be able to figure it out easily. However, I will reserve the problem generator with special number formatting for my next post.

Last updated on 19 Apr 2013. Tags: , , ,

Share your thoughts

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

Free and open source software technology and internet usage guide for teachers and other professionals in the education sector