How to Write Formulas in Excel Spreadsheets

Excel is Not a Calculator

One of the first things to understand about Excel is it doesn’t work the same way as a calculator. For example, if you type 4 + 3 x 2 into your calculator you’ll get the answer 14. If you type this formula into a cell in Excel, you’ll get a total different result:

=4+3*2

Excel gives the answer 10.

If you have a mathematical background, you’ll know why Excel gives a different answer to such a simple calculation — and why, technically, Excel is right. If you don’t have a math background this is very confusing indeed.

Excel is applying a mathematical rule called Order of Precedence. This rule specifies the order in which mathematical calculations should be performed. One part of that rule says that multiplication and division should be performed before addition and subtraction. Because of this Excel multiplies 3 by 2 and then it adds 4 to the result giving the answer 10.

If you double-click on a cell containing a formula, Excel shows you the values used in that formula so you can ensure they are correct. (Click for larger image).

If your situation requires the answer to be 14 — for example if you calculate the selling price of an item that you pay $4 for as being the purchase price plus $3 and then that value doubled, you need to write the calculation in Excel as:

=(4+3)*2

Placing the calculation 4 + 3 inside a set of brackets forces Excel to perform that calculation before it performs the multiplication. This is another part of the Order of Precedence rule, and it’s applied to all Excel formulas. You need to be aware of it and account for it in your calculations or things can go spectacularly wrong.

Detecting Simple Errors

If you are creating a worksheet and using a formula such as the one above, you will be alerted to problems if you check the calculation on your calculator. If you were to type the formula 4 + 3 x 2 on a calculator and compare the results you get in Excel, it will be immediately obvious that something is wrong.

If you don’t double check your calculations you won’t see that there is a problem with your formulas, particularly when the numbers are much larger and the formulas more complex than 4 + 3 x 2.

Everyone who designs a spreadsheet — even those with good math skills — should check their formulas in some way. You can, for example, check calculations against similar calculations made with that data using a different program such as your accounting software or you can check a calculation using a calculator to see that the results are the same. If they’re not, stop! Don’t go any further until you sort out the issue.

Use Automated Tools in Excel

When you’re creating formulas in Excel, such as formulas that add a range of numbers, use a tool like the AutoSum button. It adds a sum function to a cell, and it shows you the cells it is using in the calculation so you have a visual check of the data.

When you have created one formula this way, or if you have to build a custom formula, copy it to fill the other cells in the row or column. To do this click on the cell containing the formula and drag on the fill handle in its bottom right corner. In most cases, dragging to copy a formula is more accurate than writing formulas for each cell in a range. If you write individual formulas, chances are one or more will contain errors because you’re writing so many of them. Copying a formula reduces the chances of error.

Of course, you should always check that the formulas are calculating correctly and that they actually refer to the values that you think they do. One way to do this is to double-click on a formula in an Excel cell. The formula will appear in the cell and the cells used in that calculation will be outlined. This gives you a visual way to verify which cells the formula is calculating. In addition, make that same calculation on a calculator to make sure that the result is what you expect.

If you have trouble writing a single formula for a task, do it in steps and calculate each piece of the formula one column at a time. (Click for larger image).

Writing Excel Formulas: Keep it Simple

If you need to write a formula for a complex calculation, break it into small steps. There is no rule that says the result has to be calculated in one step so, if you have to, create columns for each intermediate step in the complex calculation.

This will give you a clearer idea of the calculations you need, and it will show you which calculations you might need to place in brackets so they’re performed in the right order. Later, you can write a single formula to make the calculation in one step. At that point, you can compare the answers from your step-by-step solution to the results from the single formula to make sure it is working correctly.

Good Looks Don’t Count in Spreadsheets

It is tempting to assume that a neat-looking worksheet with well-organized numbers and calculations is, by its very nature, correct and can be trusted. A look at the statistics will tell you that this is anything but the case.

Research indicates that some 90 percent of spreadsheets produced each year in the U.S. alone contain significant errors with a cumulative cost estimated at more than $10 billion a year. This is a huge problem, and you certainly don’t want your business to lose money because of incorrect calculations.

Understand the risks you are taking when writing formulas in Excel. Take a simple step-by-step approach and check your work carefully. If you do this, you will have a good chance of creating formulas that work — or at least you will know immediately when you have a problem that requires extra help to resolve.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com