Calculating with Excel 2007

Excel 2007 is a calculation program at its core. The program can replace your calculator and help you with your administration. Before you start calculating in Excel, it is wise to first delve into the basics of the program via other SeniorWeb articles, such as the article Excel: the basics.

• Are you using Excel 2010? Then read the article ‘Calculating with Excel 2010’.
• Are you using Excel 2013? Then read the article ‘Calculating with Excel 2013’.
• Are you using Excel 2016? Then read the article ‘Calculating with Excel 2016’.

In this example we do the administration of a canteen. Before we can start calculating, data must first be entered in Excel. You can enter words, numbers, or formulas in any cell of the spreadsheet. You can use the numbers in calculations (formulas).

• Click in cell A1 and type Canteen income.
• type then in the same way in cell A3 Kind, at B3 Number, in C3 Price and in D3 Total.
• On the next line comes in A4 Soft drink, B4 17 and C4 0.8 (see example).

Now you can start calculating using a formula. Double click in cell D4. Type = and click in cell B4. A moving border appears around the cell. Now type * (on your numeric keypad or above the 8) and click in cell C4. To complete the formula, click the icon Enter for the formula bar or press Enter.

You can also add, subtract and divide in the same way as above. For formulas in Excel, the calculation sequence is the same as in mathematics. Multiplication alone does not precede division. You can also use parentheses in the formulas to determine the calculation order, for example = 5 / (10*4). What is between the brackets is then calculated first.

Do you calculate with amounts of money? Then give the relevant cell a currency format.

• Click on the cell.
• In the ribbon, select the tab Start.
• In the ‘Number’ group, click on the arrow behind Default.
• click on Currencies.

The Sum function adds the numbers in the selected cells. In order to practice this, we first need more data in our example. Fill cell A5 with the word Beer, cell B5 with 32 and cell C5 with 1.00. In cell D5, type a formula (as in step 2) to calculate the total. In cell A6, type Total. Now you can calculate the total of all consumptions using the Sum function.

• Click on cell D6.
• Press the button Insert function.
• Click in the window on Sum > OK.
• In the next window you can specify which cells should add up.
• Click in the first desired cell and drag (with the mouse pressed) over the rest of the cells. When all desired cells are selected, release the mouse button.
• click on OK.
• You now see the amount in cell D6 and the corresponding formula in the formula bar.

The Average function calculates the average of the selected cells.

• Double click on cell A7 and type Average.
• Click on cell B7 and click on the button Insert function.
• click on Average > OK.
• Select cells B4 and B5 and then click in the window on OK.
• Cell B7 now shows the average number of drinks purchased.

The If function is a tricky one, but it can be very useful at times. In addition, the function shows that there are many possibilities in the Excel program. The function checks whether a condition is met. If the condition is met, a certain answer will be given. If the condition is not met, a different answer is given. You can fill in the condition and the possible answers yourself. In the example in this article, we want to know whether a profit or loss has been made in the cafeteria.

Before we can enter the function, we first need data.

• Enter in cell A10 Total expenses, in D10 30 and in A12 Result.
• Now you start with the If function.
• Click on cell D12.
• Press the button Insert function.
• click on if > OK.

You will now see a window where three fields must be filled in. rear Logic test comes the sum that you want to test whether it is correct. rear Value-as-true comes the answer that should be in the cell if the sum is correct. And behind value-if-false comes the answer shown if the sum is wrong. The answers can be numbers or words.

• Click in the field Logic test.
• Click in cell D6. Enter the greater-than sign (shift + >).
• Click in cell D10.
• The sum now means: if the contents of cell D6 are greater than D10 then…
• Click in the field Value-as-true and type Gain.
• Click in the field value-if-false and type Loss.
• click on OK.

In the D12 you will now see Gain. You know the cafeteria has made a profit.

Excel has a large number of functions. You can view all options in the ‘Insert function’ window. Open the window via the button Insert function. Click on the arrow behind Or select a category and click Everything. When you click on a function there is a short explanation.

Some more common features:

• max; returns the largest value of a list.
• Min; returns the smallest value of a list.
• Number; counts the number of cells in a list containing a number.