Excel Function And Formula Essentials

As you probably know, to create a formula in MS Excel 2007, you begin by typing an equal sign: this distinguishes it from other types of data. A formula consists of a series of expressions. There are three key components in these expressions: arithmetic operations, such as add and subtract; 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, what we need to do is to ensure that our formula contains a reference to the two cells in question. The easiest way of doing this is to click on the first cell having typed =. Excel then automatically creates a reference to that cell. Next type the + sign and click on 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 our formula contains a reference to these two cells, any time the cell contents change, the result returned by formula will be updated.

If this was the only way of performing calculations in Excel, formulas would very quickly become extremely long and very complex. To avoid this, we can use Excels built-in functions. Functions perform complex mathematical calculations without us having to worry about how the result is arrived at.

For example, imagine that we wanted to total the contents of a hundred different cells. If we relied purely on the + sign, we would end up creating a formula with a hundred different arguments; in other words, a reference to each of the cells containing the numbers. Instead, we can use the SUM function.

To use a function in a formula, type = followed by the name of the function. Next type open brackets and enter the arguments of the function. The arguments of the function are the values required by that function in order to return a result. In the case of the SUM function, we simply need to specify which cells contain the numbers we want to total. We can do this by clicking or dragging across the cells that contain those numbers.

You can repeat this process to add several different ranges as the argument of the SUM function; simply drag across the first area and Excel will pick up the reference; drag across the second range to pick up the second reference; and so on.

When you have finished entering references to all the cells containing numbers that we want to total, you can enter the formula. You dont even need to put in the closing brackets. Excel will do this for you.

If you now wish to have the average, minimum, and maximum values contained within those same cells you can simply copy the formula from the formula bar.

To do this, select the formula right clicking on the selection and choose Copy from the context menu. Next, click on the Cancel button (or press the Escape key on your keyboard) to exit editing mode. To create a formula containing the average function double click on the appropriate cell, right click on the formula bar and choose Paste from the context menu. The only change you will need to make is to change the Sum function AVERAGE, MAX or MIN, as appropriate.

Case is ignored when you type in the name of a function. As soon as you press Enter, the name of the function will be changed to upper case automatically.

Get important tips for emotional freedom technique – your individual knowledge base.