## Practice basic arithmetic calculations in Excel

This article demonstrates a workbook that allows children to practice basic mathematics or more specifically arithmetic calculations. The image above shows a worksheet named "Addition", it allows the user to practice adding numbers.

There are four worksheets in the workbook and each worksheet shows numbers in columns B and D. The user then types the result in column F and Excel responds in column G if the answer was right or wrong.

Row 20 lets you control the numerical range that is used, for example, a range between 5 and 10 generates random whole numbers between 5 and 10 in column s B and D. This allows you to control the level of calculation, older children can use more difficult numbers to create a better challenge.

Row 22 contains a button that is linked to a macro named "Newnumbers()", click the button to create new numbers in columns B and D.

The workbook contains four Excel worksheets named "Addition", "Subtraction", "Multiplication" and "Division". Click on one of the tabs below the cell grid to change the worksheet, shown in the image above.

Formula in cell B2:

This formula uses a User Defined Function that I created. It has two arguments, the lower bound and the upper bound specified in cell C20 and E20.

The VBA code for User Defined Function RandomBetween is shown below.

'Name UDF and dimension argument variables and declare data types Function RandomBetween(Low As Long, High As Long) 'Initialize random-number generator Randomize 'Create a whole random number based on arguments High and Low and return it to the cell in the worksheet RandomBetween = Int(Rnd * (High - Low + 1)) + Low End Function

Formula in cell G2:

The formula in cell G2 has two nested IF functions. The first IF function checks if cell F2 is empty and returns nothing if this is true. Another IF function is executed if this is false, it adds the numbers in cell B2 and D2 and checks if it is equal to the value in cell F2.

If they are equal then text string "Good!" is returned, if not equal text string "Try again" is returned.

The following macro clears cell range F2:F18 in all worksheets in the active workbook. This macro is used in all four worksheets and linked to a button in each worksheet.

'Name macro Sub Newnumbers() 'Dimension variables and declare data types Dim sht As Worksheet 'Iterate through each worksheet in active workbook using variable sht as worksheet For Each sht In ActiveWorkbook.Worksheets 'Clear cell range F2:F18 based on variable sht sht.Range("F2:F18") = "" 'Continue with next worksheet Next sht 'Forces a full calculation of the data in all open workbooks. Application.CalculateFull End Sub

### Subtraction

Worksheet "Subtraction" lets the user train their subtraction skills. The result is always 0 (zero) or higher. The same User Defined function is used in this worksheet as in worksheet "Addition", however, the second argument is different in column D.

Formula in cell B2:

Formula in cell D2:

If you want to allow negative results change the formula in column D to:

Formula in cell G2:

The formula in cell G2 has two nested IF functions. The first IF function checks if cell F2 is empty and returns nothing if this is true. Another IF function is executed if this is false, it subtracts the number in cell B2 with cell D2 and checks if it is equal to the value in cell F2.

If they are equal then text string "Good!" is returned, if not equal text string "Try again" is returned.

### Multiplication

Formula in cell B2:

Formula in cell B2:

Formula in cell G2:

The formula in cell G2 has two nested IF functions. The first IF function checks if cell F2 is empty and returns nothing if this is true. Another IF function is executed if this is false, it multiplies the number in cell B2 with cell D2 and checks if it is equal to the value in cell F2.

If they are equal then text string "Good!" is returned, if not equal text string "Try again" is returned.

### Division

This worksheet contains math problems that lets you train divison. Dividend / Divisor = Quotient

This worksheet requires a different User Defined Function to avoid remainders. The number left over is called a remainder.

Formula in cell B2:

Formula in cell D2:

'Name user defined function and dimension parameters and their data types. Paramter Num is optional meaning it isn't required. Function RandomBetweenDivison(Low As Long, High As Long, Optional Num As Variant) 'Dimension variables and declare data types Dim i As Long, str() As Long 'The ReDim statement resizes a dynamic array that has already been declared ReDim str(0) 'Initialize random-number generator Randomize 'Check Optional variable Num is missing If IsMissing(Num) Then 'Create random whole numbers based on parameters High and Low RandomBetweenDivison = (Int(Rnd * (High - Low + 1)) + Low) * (Int(Rnd * (High - Low + 1)) + Low) Else 'Iterate from number stored in parameter Num to 1 based on -1 increments For i = Num To 1 Step -1 'Check if paramater Num divided by variable i is a whole number If Num / i = Int(Num / i) Then 'Save number in variable i to array variable str str(UBound(str)) = i 'Add a new container to array variable str ReDim Preserve str(UBound(str) + 1) End If Next i 'Delete last container in array variable str ReDim Preserve str(UBound(str) - 1) 'Save random whole number based on the number of containers in array variable str to variable i i = Int(Rnd * (UBound(str))) 'Return value stored in array variable str based on variable i RandomBetweenDivison = str(i) End If End Function

### Where to put VBA code?

- Copy above VBA code.
- Press shortcut keys Alt + F11 to open the Visual Basic Editor.
- Click on "Insert" on the menu, see image above.
- Click on "Module".
- Paste VBA code to module window.
- Return to Excel.

### Recommended links

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form