A formula in Excel is an expression that performs an operation, such as a calculation or a search among letters. These expressions usually contain commands and cell addresses, and sometimes contain numbers.
A formula always start with a “=”. In figure 2.1, we have written the price $75 for a product in cell B2. The cells A5:A10 contain different values for number of units sold. To calculate sales (price · number of units) at 100 units we write the formula “=$B$2*A5” in cell B5. We do this by writing “=” and “*” into the cell, and clicking on the cell addresses B2 and A5. To obtain $B$2 (with $-signs) we press F4 after clicking on the cell B2.
Figure 2.1 Calculation in Excel.
To calculate sales (in the cells B6:B10) at the other volumes, we can copy the formula from cell B5 to the cells B6:B10. This can be done by clicking on cell B5 and then clicking and dragging the lower right corner of the cell down to cell B10. A black cross will appear in the lower right corner of the cell when we do this.
Since the cell address $B$2 includes $-signs, the new and copied formulas will contain the same cell address. This means that the price $75 will be used for every calculation of sales in the cells B6:B10. The cell address A5 is not written with $-signs in cell B5. When copying the formula, this address will change relatively, so that A6 appears in the formula in cell B6, A7 in the formula in cell B7, and so on. Then the formula in cell B6, “=$B$2*A6” results in $75 · 200 = $15,000, and so on.
You should never include numbers in your formulas. Parameter values, such as the price in figure 2.1, should always be written in specified cells in the spreadsheet. One of the great ideas with Excel is that we can change the value of a parameter (in a cell) and then observe the changes in the calculated results.
Imagine a spreadsheet model where we have price per unit, number of units sold, unit cost, fixed cost per year, interest rate, tax rate, etc. written in specified cells. Based on these values, the model calculates detailed budgets for the next year by the use of different formulas. Then we can study how the budgets change if we change the price per unit, the number of units sold, etc.
The rule of never including numbers in formulas can be neglected if we have a number that will never change. Examples are 12 months in a year, mathematical constants, and 100 cents in a dollar.
If we want to display all the formulas in a spreadsheet we can choose the tab Formulas and Show Formulas in the group Formula Auditing. In this group we also find the choices Trace Precedents and Trace Dependents. If we click on a cell with a formula and choose Trace Precedents, arrows will appear showing which cells the formula refers to. See figure 2.2. If we choose Trace Precedents several times, we get arrows showing several levels of references between cells and formulas.
Figure 2.2 Formula auditing.
The choice Trace Dependents works in the opposite way. If we click on cell B2 in figure 2.2 and choose Trace Dependents, an arrow from cell B2 to cell B5 will appear showing that the value in cell B2 is used by the formula in cell B5.
Circular references
Suppose we have a spreadsheet with a formula in cell B10 that calculates something based on the result from a formula in cell B18. At the same time the formula in cell B18 calculates its result based on the result from cell B10. This is referred to as circular references, and occurs usually due to errors in one or more formulas.
If we write our formulas in such a way that circular references occur, the warning in figure 2.3 pops up. Here we can click Help to get more information or OK to return to the spreadsheet.
Figure 2.3 Circular reference warning.
The following set of equations can be solved by using circular references in a spreadsheet:
If we want the y-value in cell A2 and the x-value in cell A3, we can write the formula “=(A3+8)/2” in cell A2 and the formula “=A2–3” in cell A3.
In this case we have created circular references by intention. To make the calculations work properly, we need to activate iterative calculations. To do this we choose the tab File and Options. In the dialogue box that appears, we choose Formulas and tick Enable iterative calculation under Calculation options. A click on OK brings us back to the spreadsheet where we find the values 5 and 2 in the cells A2 and A3 respectively. This makes perfect sense, since the solutions of the two equations is y = 5 and x = 2.
Example 2.1
In the spreadsheet in figure 2.4 we find some simplified financial statements for a company. The interest received in cell B10 should be calculated as 5 per cent of the bank deposit in cell B18. 30 per cent taxes should be redrawn from profit before taxes. The company will pay no dividends. Profit after taxes in cell B13 should be transferred to retained earnings in cell B21.
The missing values in column B are calculated in figure 2.5. In addition we have written the formulas as text in column C. The interest received in cell B10 is calculated as 5 per cent of the bank deposit in cell B18 with the formula “=B3*B18”. Then profit before taxes can be calculated as operating result + interest received by the formula “=B9+B10” in cell B11. After a 30 per cent tax deduction, profit after taxes is calculated in cell B13 by the formula “=B11+B12”. This value is transferred to cell B21 as retained earnings.
At this point we have all values in place except the bank deposit in cell B18. To obtain equal sums on the two sides of the balance sheet (assets and equity/debt), we calculate the bank deposit in cell B18 as the difference between equity + retained earnings + debt and machines + goods by the formula “=B20+B21+B22–B16–B17”.
Figure 2.4 Financial statements.
Figure 2.5 Financial statements.
Since the calculation of the bank deposit (in cell B18) is based on the interest received (in cell B10) and vice versa, circular references occur. To get the correct results, as shown in figure 2.5, we need to activate iterative calculations.
2.2 Functions
A function in Excel is a macro (a small program) that returns one or more values calculated from the arguments (input data) we feed to the function. We get access to the group Function Library under the tab Formulas as illustrated in figure 2.6. Here we may choose between different categories. If we click on Financial and choose IRR from the menu that appears, the dialogue box for the IRR function pops up. This function calculates the internal rate of return for a given cash flow.
Figure 2.6 Function library.
We can also get access to the function library by choosing Insert Function under the tab Formulas, or clicking the fx button located to the left of the formula bar. This will open the dialogue box in figure 2.7.
Figure 2.7 The Insert Function dialogue box.
In the uppermost window we can search for a function by writing a name or a description. In the middle window we can choose among the different categories we saw in figure 2.6. All functions under the chosen category will be displayed in the lower window. In figure 2.7 All is chosen as category so that the names of all functions are displayed in the lower window.
In the lower left corner we find the link Help on this function which leads to a dialogue box with information and examples for the actual function.
If we know the first letters in the desired function’s name, we can write “=” and these letters in the cell where we want the function. Then a list of function names appears where we can choose the right one by a double click. Then we can write the arguments directly into the function. Alternatively, if we click the fx button at this stage, the function’s dialogue box pops up.
Assume we want to calculate the standard deviation of a data set located in the cells A3:A13. If we write “=ST” in a cell, a list appears as illustrated in figure 2.8. When we double-click on STDEV we get “=STDEV(” in the formula bar so that we can write the argument A3:A13 directly into the function. The function “=STDEV(A3:A13)” will give the correct standard deviation.
Figure 2.8 Autocomplete.
Let’s take a look at another example of a function. In figure 2.9 we want to multiply each number in the cells A2:E2 with each number in the cells A4:E4 (i.e. 1 · 1, 2 · 2, and so on), and sum the products in cell G4.
While standing in cell G4 we click the fx button. In the Insert Function dialogue box we choose SUMPRODUCT under the category Math & Trig. Then the dialogue box in figure 2.9 appears. As Array 1 we choose A2:E2, and as Array 2 we choose A4:E4. This gives the result 55 in cell G4 when we click OK.
Combining functions
Combined functions can often be the solution of complicated problems in Excel. Let’s study an example where we combine the functions INDEX and MATCH to look up in a table of sales figures. Figure 2.10 show sales figures for different customers in different months. We want to read sale from this overview for the month and the customer given in cells B3 and B4 respectively.
The data set in the cells E4:I9 contains six rows and five columns. In cell B6 the function MATCH(B3;D4:D9;0) compares a month (May) from cell B3 with the months in the cells D4:D9, and returns the corresponding row number (5). The argument 0 must be included to tell the function that it should look up the exact value in the array D4:D9. In cell B7 the function MATCH(B4;E3:I3;0) compares a name (Davis) from cell B4 with the names in the cells E3:I3, and returns the corresponding column number (2).
Figure 2.9 The SUMPRODUCT function.
In cell B9 the function INDEX(E4:I9;B6;B7) uses the row number from cell B6 and the column number from cell B7 to look up the value 33 057 in the array E4:I9.
A more elegant solution to this problem will be to combine the functions in one formula:
“=INDEX(E4:I9;MATCH(B3;D4:D9;0);MATCH(B4; E3:I3; 0))”
Figure 2.10 Look up in a table.
2.3 Array formulas
For systematic calculations on data presented in arrays of cells, array formulas (and functions) can be a good help. An array formula performs calculations on a set of data in a rectangular area in a spreadsheet. With an ordinary formula in Excel, such as “=B2+B3”, the calculation is performed when we press Enter. With an array formula, we must first mark the cells where the results will appear. Then the calculation is performed when we press F2 and then Ctrl+Shift+Enter.
Excel offers several array functions and we shall take a closer look at some of them. We will start with a demonstration of an addition performed with an array formula.
Example 2.2
Add the number 2 to each of the numbers 1, 2, 3, 4 and 5.
We start with the numbers 1–5 in the cells A3:A7 and the number 2 in the cell A9. This problem can be solved by writing the formula “A3+$A$9” in cell B3, and copying this formula to the cells B4:B7. The results 3, 4, 5, 6 and 7 appear in the cells B3:B7 as illustrated in figure 2.11.
Figure 2.11 Addition.
Figure 2.12 Addition with an array formula.
The same calculations can be done with one single array formula. Then we mark the cells B3:B7 and write the formula “=A3:A7+A9” as illustrated to the left in figure 2.12. When we press F2 and Ctrl+Shift+Enter, the result appears as illustrated to the right in figure 2.12.
As shown in figure 2.12, the array formula is presented as {=A3:A7+A9} in the formula bar. This means that the formula is valid for an array of cells (here B3:B7). These cells constitute one unit in the spreadsheet, and they cannot be changed individually.
Example 2.3
The spreadsheet in figure 2.13 contains two arrays of values. Write a formula that calculates the number of identical values to be found in corresponding locations in the two arrays.
Figure 2.13 Arrays.
Two values can be compared with the function IF in Excel. By combining this function with the function SUM, we can write a formula that counts the number of identical values we find in corresponding locations in the two arrays.
In the spreadsheet in figure 2.14 we have written the formula “=SUM(IF(A3:G7= A9:G13;1;0))” in cell I3. The function IF in this formula compares every position in the range A3:G7 with every position in the range A9:G13, and returns 1 each time identical values are found. Otherwise, it returns 0. When all cells have been checked, the formula calculates the sum of the returned ones and zeros. This means that the formula counts identical values found in corresponding locations in the two arrays.
We want this formula to work as an array function. Thus, when we have written the formula in cell I3, we must press F2 and Ctrl+Shift+Enter to obtain the correct result. In this case we see that there are three identical values in corresponding locations in the two arrays.
Figure 2.14 Arrays.
If we want to systemize large amounts of data and present them in groups, the function FREQUENCY can be useful. Let’s illustrate with an example.
Example 2.4
The spreadsheet in figure 2.15 shows a set of data. We want to generate a frequency table and count the number of values in the interval 0–100, in the interval 100–200, and so on up to the interval 600–700.
Figure 2.15 A set of data and intervals.
The FREQUENCY function in Excel can be applied to perform this operation. First we need to define the desired intervals. This is done in the spreadsheet and shown to the right in figure 2.15. The first interval (0–100) is represented by 100 in cell M3.
The FREQUENCY function will read the content of this cell as all values up to 100. The number 200 in cell M4 represents the interval 100–200, and so on.
To generate the requested frequency table we first mark the cells N3:N9 as shown in figure 2.16, and open the dialogue box for the FREQUENCY function. As Data_array we choose the data set in the cells A3:J12, and as Bins_array the intervals in the cells M3:M9. When we click OK and press F2, the spreadsheet will look like figure 2.16.
Figure 2.16 The FREQUENCY function.
When we press Ctrl+Shift+Enter, the frequency table will appear in the cells N3:N9 as illustrated in figure 2.17.
Figure 2.17 Frequency table.
In linear algebra a matrix is defined as a rectangular array of values. A mathematical operation on matrices such as a multiplication can be carried out with the function MMULT in Excel. Other helpful functions are MINVERSE for finding the inverse of a matrix, TRANSPOSE to transpose a matrix, and MDETERM to calculate the determinant of a matrix.
An elementary application of linear algebra is to find the solution of a system of linear equations in several unknowns. Let’s take a look at an example where we find the solution of three linear equations with three unknowns.
Find x1, x2 and x3 in the following system of linear equations:
A system of linear equations on the form:
can be expressed as Ax = b, where A, x and b are the following matrices:
When we reformulate Ax = b, we get the solution of the equations:
In the spreadsheet in figure 2.18 we find matrix A in the cells A4:C6 and matrix b in the cells E4:E6. The function MINVERSE is used to calculate the inverse matrix A–1 in the cells G4:I6. Thus matrix x, with the solution x1 = 3, x2 = 7 and x3 = 8, can be calculated using the function MMULT in the cells K4:K6.
Figure 2.18 Solving a system of linear equations by matrix calculations.
2-1. If nominal interest per year is r and the interest is settled m times a year, the effective interest per year can be calculated as reff = (1 + r/m)m – 1. Calculate the effective interest per year when the nominal interest per year is 7.3 per cent and the interest is settled four times a year.
2-2. a) Find a suitable function in Excel and write 1389 in Roman numerals.
b) Computers uses binary numbers where 1, 2, 3, etc. are written as 0001, 0010, 0011, etc. Find a suitable function in Excel and write the binary number 1101 as a decimal number.
2-3. Write the numbers 1–20 in a 4 × 5 array (in Excel) as shown below. Write the same array again somewhere else in the same spreadsheet. Multiply the corresponding numbers in the two matrices so that the products appear in a third array.
2-4. Solve the following system of equations by array formulas in Excel:
2-5. Write a formula that picks out the five smallest numbers in this array.
