Hints and Tips

Excel Formula Tips

1. BODMAS. No Bradley, it’s not a new mixer. BODMAS stands for Brackets, Order, Divide, Multiply, Add, Subtract and represents the sequence in which you calculate mathematical expressions. For example:

  • Brackets. 6 x 3 + 2 = 20 but 6 x (3+2) = 30. You calculate the contents of the brackets first…
  • Order (powers and roots). 3 x 22 = 12, not 36. …then powers before divide and multiply…
  • Divide and Multiply. 3 + 2 x 6 = 15, not 30. …which are calculated before add and subtract…
  • Add and Subtract. …and if there are no other operators, just calculate the formula from left to right.

2. Percentages. If Department X’s budget increases from $417,000 to $451,000 and Department Y’s budget from $219,000 to $246,000 which department has been more favourably treated in relative terms? No Brad, it’s not easy to make a quick comparison – however by expressing the increases as percentages we can answer the question. To calculate Department Y’s increase as a percentage we divide the increase ($27,000) by the old budget amount ($219,000) giving 0.123 and format the result as a percentage = 12.3%. The Excel formula might look something like =(B2-B1)/B1. The equivalent calculation for Department X gives 8.1% so we can say that Department Y has received the bigger relative increase.

3. Formulas. We use the Sum function as an efficient method of adding a column or row of numbers – for example =SUM(B3:B18) sums the content of all cells from B3 to B18 inclusive. For simple formulas we use the appropriate cell addresses and mathematical operators. For example =A2*(C11-C12) subtracts the contents of C12 from C11 and multiplies the result by the contents of A2. What we do not need to do is combine the two methods – for example = SUM( A2*(C11-C12)). While this produces the correct answer it is a bit like drinking beer through a straw.

4. Embedded numbers. Don’t put numbers in formulas, no, not ever Brad. Rather than use a formula such as =B5*6, type the variable 6 in a cell (perhaps B2) with an explanatory label in an adjacent cell and use the formula =B5*B2. Typing numbers in formulas means that a) data may be misinterpreted if key variables are hidden from the casual glance, and b) variable values can only be changed by editing formulas, with the possibility of introducing errors and inconsistencies.

5. Absolute cell references. If we take the formula =B5*B2 from the previous example and copy it along a row, the copied formulas will be = C5*C2, =D5*D2 etc. This is because the default setting for Excel is to adjust the cell references in copied formulas relative to their new location. However if we wish all the copied formulas to refer to the variable cell B2 this cell reference must be made absolute. We achieve this by pressing function key F4 as we enter the cell reference B2 in the formula. The original formula then appears as = B5*$B$2. If the formula is copied, the copied formulas will be = C5*$B$2, = D5*$B$2 etc. Any change to the variable value in cell B2 will automatically ‘flow’ through to all the formulas. Understanding absolute cell references is the key to the correct use of spreadsheets.

Contact Us   |    Privacy   |    Access and Equity   |    Links   |    Sitemap