Is your excel formulae correct?

Formulae can save you a lot of time. Excel employs some fantastic formulae that can really help you analyse data. But what can go wrong?

Well, for 2012, there will be 53 weeks if you use the WEEKNUM formula. It is a calendar quirk that may throw any calculations you may have this year. Look out for it. Here are some of the more common formulae problems I see in Excel spreadsheets:

1.  Not coding for zero values

Dividing one field by another is simple enough:

=A1/A2

But what if one of your values could possibly be zero? Excel does not like dividing by zero and you will end up with an error. So put in a condition. Perhaps like this:

=IF(A2 = 0,0,(A1/A2))

2.  Circular references

You have two fields A1:=B1+5 and B1:=A1-5.

A1 requires B1 to be calculated, but B1 requires A1 to be calculated. Don't do it. Excel doesn't like it.

3.  Hard-coding values into your formulae

Write your formulae as clear as possible. Do not put values into your formulae. Have your formulae reference values in other fields

Wrong:        = A1*120

Right:          = A1*B1       (nb populate the field B1 with the value 120)

4.  Not including balance checks

When you have totalled your columns of data, it really is worth putting some balance checking into your sheet. This gives you some indication that your formulae has worked, and there are no missing values, or blank spaces where you haven't dragged your formulae properly.

Excel is a great tool for working things out. I'm sure you have come across more common errors. Leave your comments in the place below.