Basic Formulae in Microsoft Excel

FORMULAE

Spread sheet formulae usually contain numbers, arithmetical operators and cell references. They can be typed in directly or Excel can help build them for you. You can use all the standard mathematical operators in your formula; e.g. () + - / *

ENTERING A FORMULA

1. Activate the cell in to which you want to enter the formula.
2. Enter "= (equals)"
3. Type in the formula by hand or:

- Click in to each of the cells, adding the relevant operators (+/* - etc) where appropriate.

Click on the tick to accept the formula or press enter.

In the example to the right; column B holds information on the Cost Price of the products listed in column A. Column C requires a formula to calculate a 10% Mark Up on the Cost Price for each product. Column D requires the total of the Cost Price and the Mark Up columns giving the Retail Price.

Once the Mark Up price has been calculated, working out the Retail Price is quite easy.

Type = in cell D3 (this is the cell where you want the answer to appear), click in to cell B3, select the plus option and click in to cell C3.

The last thing that you should always do is check the formula, if you are happy with it, click the tick or press enter.

There are always several ways to complete the task in Excel. In the previous example, the formula that you have just completed to work out the Retail Price can be repeated for each product. It is quite permissible for you to repeat the process and type each formula out manually on each row. However, because both parts of the formula are on the same row in this example you can use the AutoFill feature.

The small black cross will appear when you move your mouse pointer over the bottom right hand corner of any selected cell or cells.

Once the small black cross appears simply click and drag down the appropriate number of rows and the formula will be copied down. Note: You can also double click the small black cross and the formula will fill down.

The next step in our little example is to total each column. There are two ways that we can do this. One, use the formula =B3+B4+B5 etc. or two, use a Function called AutoSum. This function has been designed to add lists or ranges as they are called in Excel.

Both examples will provide the correct result however; you will be restricted by using multiple + symbols.

The AutoSum function is the most common function used Excel and therefore it appears in more than one place on the Ribbon. The AutoSum feature is at the right side of the home ribbon.

The first thing you must do is click in the cell where you want the answer to appear. Then click the AutoSum button. The function will guess what you want to add up, you must check that it is correct before clicking the tick or pressing enter.

In the example to the right the function has guessed correctly so it is safe to click the tick or press enter.

Once again, all the elements in this formula are in the same column so you can use the AutoFill feature to drag the formula in to column D. Make sure that you see the small black cross in the bottom right hand corner before you start to drag.

The results are displayed under each column as shown to the right.

MATHEMATICAL PRECEDENCE

If you require a combination of operators within the same formula be sure that you follow the rules of Mathematical Precedence. Multiplication and division are carried out before addition and subtraction.

If you want an addition to occur before a multiplication you must put the addition in brackets.

The order of precedence is Brackets, Multiplication and Division followed by addition and subtraction. BDMAS (Brackets, Division, Multiplication, Addition, and Subtraction) may help you remember the correct order.

E.g. Formula 24+36/12 = 27

Formula (24+36)/12 = 5

In my experience, quite a few people new to Excel will use a calculator to double check formulae results in Excel. I think this is because they do not really understand the order that Excel calculates. About 10% of people find mathematical precedence hard to understand.

Steve Says

I often ask my class to answer the following question.

What is 2+4/2?

Most reply with 3 as the answer having done the sum in two stages in their head.

I then get them to type the same formula in to an Excel Spread sheet.

The answer is 4. The reason is that the 4 is divided by 2 first, therefore the sum will end up being 2 + 2 which equals 4. This is mathematical precedence.

2 + 4/2

2 + 2 = 4

Most people are taught this at school at about 10 or 11 and forget it by the time they leave