Calculating with Excel

Excel is a calculation program at its core. The program can replace your calculator and help you with administration. Before you start calculating in Excel, it is wise to first delve into the basics of the program. You can do that in the article ‘Excel: the basics’.

Excel uses formulas. These are calculations applied to numbers. You can type both numbers and formulas yourself in cells. The formula is written out in the formula bar below the Ribbon and the result of the formula is displayed in the cell in which you have typed the formula.

A formula always starts with a =-sign. Then you can type the formula. This can be done with numbers, for example =15*20. You then calculate how much 15 times 20 is. But you can also have cells calculate with each other. You then refer in the formula to the cells that you want to calculate with each other. Calculating with cells is preferred. If a number in a cell changes, the result of your formula will remain correct. Excel will then calculate with the new number in the cell.

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’.
• Then, in the same way, type in cell A3 ‘Type’, in B3 ‘Quantity’, in C3 ‘Price‘ and in D3 ‘Total’.
• On the next line, A4 has ‘Soda’, B4 ’17’ and C4 ‘0.8’ (see example).
  

Now you can calculate using a formula.

• Double click in cell D4. Type = and click in cell B4.
• A moving border appears around the cell. Now type * (on the numeric keypad or Shift+8) and click in cell C4.
• Press the Enter key or click the ‘Enter’ icon for the formula bar to complete the formula.
• In cell D4 the answer of the calculation 17 times 0.8 appears.
  

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 in the cell.
• In the ribbon, select the tab Start.
• In the ‘Number’ group, click on the arrow behind the image of the blue note with the yellow coins. In newer Excel versions, the icon for ‘Financial number format’ looks like this:
  
• Click on the desired currency in the drop-down window.

Excel has already prepared a number of common formulas for you. These pre-programmed formulas are called Functions. In front of the formula bar is the ‘Insert function’ icon (ƒx). This button calls up a screen with the various functions. In this article, we’ll discuss some of these features.

The Sum function adds up 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 in cell D6.
• Press the button Insert function (fx).
• 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.

Cell D6 now contains the amount and the corresponding formula in the formula bar.

Video
Also watch our video on how to make calculations with Excel 2016.

The ‘Average’ function calculates the average of the selected cells.

• Double-click in cell A7 and type “Average.”
• Click in cell B7 and click 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 can be very useful. The function checks whether a condition is met. If the condition is met, a certain answer will come. If the condition is not met, a different answer will be 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 need data again.

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

You will now see a window where three fields must be filled in. After ‘Logical test’ comes the sum you want to test. After ‘Value-if-true’ comes the answer that must be in the cell if the sum is correct. And after ‘Value-if-false’ comes the answer that is shown if the sum is wrong. The answers can be numbers or words.

• Click in the ‘Logical test’ field.
• 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 ‘Value-if-true’ field and type ‘Profit’.
• Click in the ‘Value-if-false’ field and type ‘Loss’.
• click on OK.

Cell D12 now reads ‘Profit’. 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 on 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.
• Count: Counts the number of cells in a list that contain a number.