Tips to improve Formula Accuracy | How to become an Expert of MS Excel - Class 6 PDF Download

Introduction

One of the most powerful features of Excel is the ability to create formulas. You can use formulas to calculate new values, analyze data, and much more. But formulas also have a downside: If you make even a small mistake when typing a formula, it can give an incorrect result.

• To make matters worse, your spreadsheet will not always tell you if a formula is wrong. It will usually just go ahead and run the calculations and give you the wrong answer. It's up to you to double-check your formulas whenever you create them.
• We've put together a list of tips you can use to help check formulas for accuracy. These tips won't help you solve every problem you encounter, but they should provide you with the tools to identify many common errors.

Check the references

Most formulas use at least one cell reference. When you double-click a formula, it will highlight all of the referenced cells. You can then double-check each one to make sure they are correct.

Look for mix-ups

A common mistake is to use the correct cell references, but in the wrong order. For example, if you want to subtract C2 from C3, the formula should be =C3-C2, not =C2-C3.

Break it up

If a formula is too complicated to check, try breaking it up into several smaller formulas. This way, you can check each formula for accuracy, and if there are any problems you will know exactly where they are.

Ballpark it

• You can use your own experience, critical-thinking skills, and common sense to estimate what the answer should be. If Excel gives you a much larger or smaller value than expected, there may be a problem with your formula (or with the values in the cells).
• For example, if you are calculating the total price of 8 items that are 98 cents each, the answer should be slightly less than $8. In the example below, the formula calculated the answer as $784.00, which is incorrect. That's because the price in A2 was entered as 98, and it should have been 0.98. As you can see, even the smallest details can make a huge difference.
  

Note: This tip does not always work. In some cases, the wrong answer may be fairly close to the correct answer. However, in many situations it can help you quickly catch a problem in your formula.

Check the arguments

If you're using a function, make sure each required argument is included. A small dialog box should appear as you're typing the function to let you know what arguments are needed.

• This can be especially useful when you're trying to fix a function that's not working correctly. For example, let's look at the function below:
  
• In the example above, the NETWORKDAYS function is returning an error. If we enter the NETWORKDAYS function into a new cell, the reason becomes clear:
  
• The NETWORKDAYS function requires at least two arguments—a start date and an end date. Our original function only has one argument, so we'll edit our function to include both arguments:

Now our function is working correctly!

Walk through the order of operations

Remember the order of operations from math class? If not (or if you want a refresher), you can check out our Complex Formulas lesson. Your spreadsheet will always use this ordering, which means it doesn't just calculate a formula from left to right. In the example below, the multiplication is calculated first, which isn't what we wanted. We could fix this formula by enclosing D2+D3 in parentheses.

Switch to formula view

If you have a lot of formulas and functions in your spreadsheet, you may want to switch to formula view to see all of them at the same time. Just hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the upper-left corner of the keyboard. Press Ctrl+` again to switch back to normal view.

Remember, it takes a lot of practice to master writing formulas. Even the most experienced spreadsheet users encounter formula errors. If your formula doesn't work or produces an incorrect value, don't panic! More often than not, there's probably a simple reason your formula resulted in an error—once you find it, you'll be able to get your formula working correctly.