Essentials Of Formulas And Functions In Excel 2007

by Andrew Whiteman


As most of us know, to create an Excel formula, you start by typing an equal sign: this distinguishes it from other types of data. A formula is made up of a series of expressions. There are three main components in these expressions: mathematical operations, such as addition and subtraction; functions; and cell references.

To create a formula which gives us the total of two of the numbers in a spreadsheet (say 24 and 12), we could simply type "=24 + 12". When we press the Enter key, we would get the correct result. However, the formula would have no link to the two cells containing the numbers. If the content of the cells were to change, the formula would still return the old result.

Obviously, the best solution is to ensure that our formula contains a reference to the two cells in question. The simplest method of doing this is to click on the first cell having typed the equal sign. Excel then automatically creates a reference to the cell. Next type a plus sign and click the second cell. Excel puts in a reference to the second cell and we then enter the formula either by clicking on the Enter button (on the left of the formula bar) or pressing Enter on the keyboard.

Now that the formula contains a reference to these two cells, any time their contents change, the result generted by formula will be updated.

If this were the only way of performing calculations in Excel, formulas would become very long and extremely complex. To avoid this situation, we can use Excel’s built-in functions. Functions carry out complex mathematical calculations without the user having to worry about how the result is reached.

For example, let’s say that we wanted to total the contents of a hundred different cells. If we relied purely on the plus sign, we would end up creating a formula with a hundred different arguments; in other words, a reference to each an every cell. Instead, we can simply use the SUM function.

To insert a function into a formula, type an equal sign followed by the name of the function. Next, type open parentheses and enter the arguments of the function. The arguments of a function are the parameters required by that function in order to return a result. When using the SUM function, the arguments are the references of the cells contain the numbers we want to total. We can generate these references by clicking or dragging across the cells in question.

When you have entered a reference to all cells containing numbers that we want to total, you can confirm entry of the formula by pressing the Enter key. You don’t even need to close the brackets. Excel will close them for you.

The writer of this article is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel Classes in London and all over the UK.