Calculating percentages is particularly suitable for integrating basic information technology education into mathematics lessons. In this subject, the PC can be used in many ways as an exercise and work medium.
- In the Internet one finds numerous exercises for the percentage calculation, which z.T. can be done online.
- With the help of a spreadsheet, different types of percentage charts can be created effortlessly.
- If students have mastered the use of formulas in a spreadsheet, they can creatively solve simple and complex tasks involving percentages and interest calculations.
- When drawing up savings and redemption plans, the advantage of the medium becomes obvious through the saving of writing, calculating and therefore time.
- The understanding of the content of formulas is deepened by suitable tasks.
Calculating percentages with Excel
The following pages are intended to help students in secondary school 1 to solve tasks from the percentage calculation with a spreadsheet program, here MS – Excel. It is helpful if the basic use of a spreadsheet has already been practiced. The use of formulas is first worked out with the basic arithmetic operations, followed by exercises in percentage calculation. The exercises are all in an Excel file, the solutions and explanations are in this html document. Online you can work with these two documents in parallel. Alternatively, this content can also be downloaded as a zipped Word – document in the attachment and modified. The Excel file, consisting of 7 tables with 8 exercises can also be downloaded there. Use the tabs at the bottom of the table to access the individual exercises.
1. Addition – subtraction – multiplication and division with Excel
Excel, like a pocket calculator, can perform arithmetic operations. If you work with Excel z.B. If you want to perform an addition, as in example 1 below, the program must know in which cells the numbers to be added are located. In this context, one speaks of Cell references . In our example (1. Sum) there is a 9 in cell C4 and a 3 in cell C5. The sum should be in cell C6. For this you have to enter into this cell a so-called Formula with the corresponding Cell references type. In cell C6 the value of the sum, i.e. 12, is then displayed.
What are formulas ?
In order for Excel to recognize an input as a formula, certain rules must be followed. So there must be an equal sign at the beginning of each formula. Proceed in this way when entering formulas:
1. Mark the cell in which the result should appear
2. Type the equal sign
3. Then enter the coordinates of the cell (the 1. cell reference), in which the first number is*
4. Enter the appropriate arithmetic symbol**
5. Then enter the coordinates of the cell where the second number is located (the 2. cell reference) *
6. Complete the entries with the Enter key.
* Instead of entering the coordinates of the cells (cell references) by hand, one can also click on the corresponding cells with the mouse !
** You will find the corresponding arithmetic symbols on the keyboard in the so-called numeric part !
The advantage of a formula is that the result immediately adapts to the new contents of a cell.
2. Fill in formulas downwards or to the right
Exercises 1 and 2 repeat formulas. It would be very time-consuming to type in all formulas. It is quicker to use the menu "Edit/Fill in/". " This command automatically adjusts the cell references. The following is even faster:
Mark the cell in which the formula is located. In the lower right corner a small black square appears.
Go with the mouse pointer on the small black square. The mouse pointer becomes a small black cross. Hold down the left mouse button and select the cells below. The formula is copied into the selected cells and the cell references are automatically adjusted.
3. Basic tasks of percentage calculation
a. Calculation of the percentage value P
In this case you have to perform 2 arithmetic operations. As with the pocket calculator, the correct order is then to be paid attention to. This is defined for Rechentermen by rules, such as z.B. Set dot before dash calculations, or the setting of brackets.. There are several possibilities for the calculation of P. Usually the percentage p is first divided by 100, then this quotient is multiplied by the basic value. The instructions for the spreadsheet will look like this:
1. Mark the cell where you want the percentage value to appear
2. Type an equal sign
3. Then enter the coordinates of the cell in which the percentage is located
4. Divide this value by one hundred
5. Multiply this quotient by the basic value
6. finish the entries with the enter key.
In Excel it can look like this:
b. Calculate the basic value G
Use the percent formulas in Exercise 4 and perform the calculations using the spreadsheet. Here are the formulas as a reminder:
c. Calculate the percentage p
4. Increased – and decreased basic value
Often the percentage value is added to the base value. The increase is usually given as a percentage. First calculate the percentage value P and then add it to the basic value.
Multiply basic value = G + P !
Or the percentage value is subtracted from the basic value. The reduction is usually given as a percentage. First calculate the percentage value P and then subtract it from the basic value.
Decreased fundamental value = G – P !
It is even faster to calculate an increase or decrease with the corresponding factor q = 1 + p/100 calculated. Surely you have already made such calculations. Reminder:
An increase of 20% leads to q = 1 + 20/100 = 1.2 , so basic value + 20% = G * 1.2
An increase of 3% leads to q = 1 + 3/100 = 1.03 , so basic value + 3% = G * 1.03
A decrease of 20% leads to q = 1 – 20/100 = 0.8 , so basic value – 20% = G * 0.8
A decrease of 3% leads to q = 1 – 3/100 = 0.97 , so basic value – 3% = G * 0.97