Office hacks: math training without worrying about the use of Excel to create the four arithmetic questions

In order to improve the speed and accuracy of the four operations, many parents of elementary school students now have some training questions for their children, which are time-consuming and laborious to produce manually, and not easy to view the results. With the help of our everyday Excel software, we can quickly and batch produce a variety of questions that meet the requirements of the four operations, and can also directly review and give scores.

○Randomly generated addition/multiplication problems

RANDBETWEEN is Excel comes with a random function, it can randomly generate a specified interval of numbers, we can easily generate a specified interval of mathematical problems based on this number and then combined with the algorithm. The following is an example of the generation of addition / multiplication within 10 questions to introduce.

Create a new worksheet, positioned to cell A2 and enter the formula “= RANDBETWEEN (1,9)” (said to generate a random integer between 1 and 9), and then fill it down to cell A11, select the data in A2:A11 and fill to the right to column B. Then positioned to cell C2, enter the formula “= A2 &” + “& B2 &” = “” (here the use of hyphens and “+”, “=” to form an additive equation), continue down pull down the fill to cell C11, so you can batch generation of addition topics (Figure 1).


Figure 1 Generating addition topics

Firelink: for more introduction to generating random numbers, you can also refer to the article “Fast random number generation in Windows environment” in Issue 17, 2018 of this journal.

But RANDBETWEEN function default cell data changes are automatically refreshed after the generation of new random numbers, so the topic will always change, not easy to practice. At this point you can click on the “File → Excel Options → Formula”, in the right-hand side of the “workbook calculation” option, select “manual recalculation”, so that only when we save the work book or press the “F9” key when the topic will change (Figure 2).


Figure 2 Formula settings

Multiplication of the topic is similar to the method of generation, positioning to cell D2, enter the formula “= A2 & “×” & B2 & “=””, and then drop down to fill the cell D11 can be (Figure 3).


Figure 3 Generating multiplication

○ Generate subtraction training questions

For addition/multiplication problems we do not need to consider the size of the number, but for elementary school subtraction, in the design of the topic to consider the subtracted number to be greater than the subtracted number, to avoid negative numbers, and therefore can not be generated directly using the above function, we can operate as follows.

New worksheet, positioned to cell A2, enter the formula “= RANDBETWEEN (1,9)”, then positioned to cell C2 and enter the formula “= RANDBETWEEN (MAX (A2 +1, RANDBETWEEN (1,10) ), 10)”, and then the formula drop-down fill, so that the random numbers generated by column C are larger than those in column A, which will be subtracted as the number can be (Figure 4).


Figure 4 The random number generated by column C is larger than column A

Formula explanation.

Here first use the inner RANDBETWEEN (1,10) to generate a random number (the maximum number to take 10, so that in column A to generate 9, column C can generate the maximum value of 10, otherwise there will be a negative situation), then use the MAX function in the “A2 +1” and the generated random number to take the maximum value as the outer nested RANDBETWEEN function of the minimum interval value, so that the final RANDBETWEEN function generated by the random number is always larger than the value of A2.

Continue to locate the E2 cell, enter the formula “= C2 &” – “& A2 &” = “” (here in the “=” sign after adding a space is to facilitate the alignment of the title format), after the drop-down fill formula will be set to the text alignment of the column right-justified. so that the “=” in the formula can be automatically aligned. Finally, the irrelevant columns will be hidden, and will be copied to the above addition / multiplication worksheet can be (Figure 5).


Figure 5 Subtraction exercise

○ Generate division training questions

For division training questions, in addition to considering the divisor is greater than the divisor, but also consider the ability to divide the whole, to avoid the remainder. Operation as above, first in cell A2 enter the formula “= RANDBETWEEN (1,9)”, then in cell B2 enter the formula “= RANDBETWEEN (1,9)*A2”.

Formula explanation.

The number generated in cell B2 is “RANDBETWEEN (1,9) generated by the random number * A2”, so B2> A2, and B2/A2 = RANDBETWEEN generated by the random integer, that is, “B2/A2 ” is possible to achieve integer division, so that we can create the equation by simply using B2 as the divisor.

Continue to locate cell C2, enter the formula “= B2 &” ÷ “& A2 &” = “”, and set the alignment to right-justified, and finally pull down the formula to generate the division formula (Figure 6). Ditto the generated division equation copied to the addition/multiplication/subtraction worksheet, and then just press the “F9” key each time you can automatically generate the addition, subtraction, multiplication and division topics.


Figure 6 Division equation

○Calculation test with automatic scoring

In order to facilitate the view of the score, we can also use the IF function to answer the results of the judgment and statistics. Here to add the settings as an example, in Figure 5, select column D, click the right mouse button to insert two columns, column D, column E, respectively, as the answer column and score, then enter the formula in cell O2 “= A2 + B2” (that is, the use of the formula automatically calculate the answer), and then drop down the formula to fill. The same operation, set the answer column of the other equations in turn, the answer to all equations are calculated (Figure 7).


Figure 7 Set the answer

Press “F9” to generate the latest equation, then select all the equation questions and copy them, create a new worksheet, and select “Paste→Selective Paste→Value” in order to convert the randomly generated equation questions into fixed equation questions, and calculate them in the new table. . Continue to locate cell C2 and enter the formula “= IF (B2 = M2, 10, “not scored”)” (that is, if the answer is the same as the answer to 10 points, otherwise show “not scored”), pull down the fill formula, so as long as the answer column enter the answer content can directly see the score of each question. Finally, you can also add the summation formula in cell C12 to count the total score (Figure 8).


Figure 8 Design Score

Leave a Comment