Microsoft Office Tutorials and References

In Depth Information

**Writing your own formulae**

When you select a group of cells, you can see the sum of their values, the

average value and the number of cells selected at the very bottom of the

screen. It only ﬂ ashes up while the cells are selected, but it can be a quick

way to check a running total.

Writing your own formulae

Excel is capable of much more sophisticated formulae than you have discovered

in this project, and it can handle any calculations you can dream up for it.

I have two warnings, though. The ﬁ rst is that although you can type a lot of your

working out into a cell, you probably shouldn’t. You could type your exchange

rate directly into a price formula, for example, and this might seem like a time

saver if you know you’re only using it once, but it makes it hard to see what’s

going on when you look at the spreadsheet. It’s better to make your workings as

transparent as possible, as I did with the exchange rate, by putting your numbers

into cells where you can see them, rather than having to dig around in formulae

to ﬁ nd out what’s going on.

The second warning is that, when you’re creating complicated formulae,

sometimes you need to tell Excel which bits of it belong together to avoid errors. What’s

15 minus 5 times 2? It depends on whether you do the subtraction or the

multiplication ﬁ rst. In Excel, you use brackets to show which bits belong together.

For example:

=15 - ( 5 * 2 )

=(15 - 5) * 2

The ﬁ rst calculation gives the result 5 (5 times 2 is 10, and taken away from 15 that

leaves 5), and the second gives the result 20 (15 minus 5 is 10, times 2 is 20).

You’ll get an error if you don’t have the same number of opening and closing

brackets.

If your formula isn’t doing what you expect or is giving an error, it’s usually not too

hard to work out what’s gone wrong. Here’s a quick checklist that can help you

ﬁ nd the problem more quickly: