List of useful formulae for the Analytical Models for Decision Making (AMDM) module

7th February 2013

1. Drawing box and arrow diagrams

Boxes can be created by adding lines to the border of cells or groups of cells.

Arrows between boxes can be inserted using InsertShapes

2. Adding formulae to cells

Cell locations are defined by columns and rows. Click on a cell (for example, cell L8 below), then enter the equals sign (=). Excel will then expect a user-defined equation to follow. Use a star (*) to represent multiply, and a slash (/) to represent divide. Hit enter when the equation is finished and Excel will display the solution in the cell. The equation is also visible in the formula bar at the top when the cell is selected.

3. Copying cell formula to other cells (and use of the $ symbol)

Once you have entered a formula, you can copy and paste it into other cells, either by:

i) dragging the bottom right square of the active cell down/across to other cells;

ii) selecting copy from the menu, moving your cursor to a new cell, then selecting paste; or,

iii) pressing CTRL+C on the keyboard to copy and CTRL+V to paste to a new cell.

This avoids having to repeat entering the same formula over and over again. It works fine as long as you want the cells to which the formula refers to move with the cells.

In some cases however, you want to fix at least one of the cells in the formula so that all of the formulae reference this one absolute cell (absolute cell referencing). To fix a particular cell reference within the formula you need to enter the dollar sign before the row or column you wish to fix e.g. L$30 or $L30 (or sometimes you want to fix both e.g. $L$30). For example, in the equation below, cell L30 at the end of the equation has been changed to L$30. This will ensure that when copying the formula in cell L9 down to other cells below, all cells with copied formulae will always refer to the cell in row 30 (and not the cells beneath it).

4. Using IF statements

This involves entering a statement you wish to test. For example, =IF(B7=good would test whether the entry in cell B7 is equal to the word good. The next part of the equation tells Excel what to enter in the active cell if this statement is TRUE, and what to enter if this statement is FALSE. Each component of the equation is separated by a comma. In the example below, a 2 is entered is cell B7=good and a 0 if it does not equal good.

Additional nested IF statements can be added within an existing IF statement. For example, in the example below, IF cell B7 does not equal good but does equal fair, THEN enter a 1, ELSE enter a 0).

5. Sorting/ranking rows or columns

Sometimes it is convenient to be able to sort or rank items in a table (for example, sorting the table below in terms of lives saved per 100,000). To do this, you need to select all of the cells in the table, then choose DataSort. You can then customise the sort in the pop-up panel.

6. The OFFSET function

This is a way to locate values within a table. For example, the formula in cell B26 is asking Excel to find the value which is 2 rows beneath cell E13 and 0 columns to the right of cell E13. Hence the value 450.

7. The MATCH function

This is a way to locate rows or columns within a table. For example, the formula in cell C31 is asking Excel to look in the range B23:D23 (in other words cells B23, C23 and D23) and identify which of the cells contains an entry matching the letter A (i.e. the entry in cell B30). Because A can be found in the first cell within the range, it returns the number 1.

8. Some other common functions

=SUM(A1:A8) adds together all values in the range A1 to A8

=MAX(A1:A8)returns the maximum value in the range A1 to A8

=MIN(A1:A8)returns the minimum value in the range A1 to A8

=AVERAGE(A1:A8)returns the average value in the range A1 to A8

=COUNT(A1:A8)counts the number of values in the range A1 to A8

9. Algebra outside of Excel

In some rare cases (see activity on page 113) you will be required to do some algebra. This will require you to know how to multiply out expressions with brackets:

Multiplying out single bracket expressions:

-p(q-4)

1. Multiply the value outside of the brackets by the first value in the brackets

2. Multiply the value outside of the brackets by the second value in the brackets

3. Add the two together, making sure double negatives get converted to a single positive (e.g. p x - 4 = +4p)

-pq + 4p

Multiplying out double bracket expressions:

(1 p)*(q - 4)

1. Multiply everything in the 2nd set of brackets by the first value in the first set of brackets

2. Multiply everything in the 2nd set of brackets by the second value in the fist set of brackets

3. Sum both together

1(q-4) + -p(q-4)

q 4 pq + 4p

Reducing an equation:

On page 113 you begin with a very long equation to represent the utility of building nothing:

= p*(q*u01 + (1 q)*u02) + (1 p)*(q*u03 + (1 q)*u04)

First you replace the utility variables (u01, u02, u03, u04) with actual utility values.

= p*(q*-1 + (1 q)*-1) + (1 p)*(q*-3 + (1 q)*-4)

Then you can multiply out the brackets using the methods described above. Once this is done, you can often simplify further by basic addition and subtraction (making sure you do not mix variables e.g. you can add 3p to 3p to give 6p, but you cannot add 3p to 3qp).

= q 4 pq + 3p

As long as you retain the correct +/- symbol immediately before each item, this expression can also then be re-ordered. For example in the book it is presented in a different order as below (the order doesnt really matter as both will give the same result):

= 3p pq + q 4

Solving an equation:

On page 113 you are asked to set the utility for building nothing (build 0) to be equal to the utility for building part of the next phase (build 1). This is obviously the point at which you would have no preference between the two options because the utilities would be the same. What is interesting however, is where this point lies when different probabilities are assumed.

In the above examples:

p = the probability that the neighbour will build; and,

q = the probability that the report will be accepted.

Once both utility calculations are reduced (as above) we are left with:

Utility of build 0 = 3p pq + q 4

Utility of build 1 = p 3pq + q 2

We now set them equal to one another:

3p pq + q 4 = p 3pq + q 2

This time there are no brackets to reduce, but you can reduce in other ways. For example, by moving expressions from one side of the equation to the other (when doing this, it is important to reverse their logical operator e.g. if you move -4 from the left side it would become +4 when it appears on the right side. That would then allow you to add together -2 and +4 to give +2, as follows:

3p pq + q = p 3pq + q + 2

Next you could move the +q from the left to the right converting it to q. That would allow you to add together +q and q, and essentially cancel each other out, as follows:

3p pq = p 3pq + 2

Next you could move pq across and add the converted +pq to -3pq, to give -2pq:

3p = p 2pq + 2

Next you could move +p from the right to the left side, and add the converted p to 3p to give 2p:

2p = - 2pq + 2

Now what you really need is to have an expression for just p or q. This currently isnt possible because pq are combined in the item -2pq. First divide everything on both sides of the equation by 2 to simplify things:

p = -pq + 1

Now, move the +1 on the right-side over to the left-side (and convert to -1).

-1 + p = -pq

Then divide everything on both sides of the equation by -p

(-1 + p)/-p = q

Now you can plot this line on a chart by calculating q for different probabilities of p.

You can see that some of the results for q are above 100% so can be ignored. Do this by restricting the axis to a maximum value of 1.0. The orange line is the graphical representation of the equation and shows the point/s at which the utility of build 0 is equal to the utility of build 1. In this example, any combinations of probabilities to the right of the line, will mean build 0 is preferred, and any to the left, will mean, build 1 is preferred. For example, if the probability of p (neighbour builds) is 100%, then building nothing (right of the line) is always the preferred option, irrespective of what happens to the probability of q.

Another line could now be plotted to show the frontier between build 1 and build 2.