Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
programming4us
Windows 7

Microsoft Excel 2010 : Using Formulas - Troubleshooting Formulas

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
4/5/2013 4:57:12 PM

It can be frustrating to enter a formula and have it either return an incorrect value or an error. An important part of solving the problem is to understand what the error is trying to tell you. After you have an idea of the problem you’re looking for, you can use several tools to look deeper into the error.

Error Messages

After entering a formula, you may run into one of the following errors:

  • #DIV/0!— Occurs when a number is divided by zero.

  • #N/A— Occurs when a value isn’t available in the formula; for example, if you’re doing a lookup function and the lookup value isn’t found.

  • #NAME?— Occurs when a formula includes unrecognizable text.

  • #NULL!— Occurs when an intersection is specified but the areas do not intersect or if a range operator is incorrect. For example, if you entered =SUM(A1:A10 B1:B10), you would get the error because the error separating the ranges is missing. The correct formula would be =SUM(A1:A10,B1:B10).

  • #NUM!— Occurs when there is an invalid numeric value in a formula or function.

  • #REF!— Occurs when a cell reference isn’t valid; for example, if the cell has been deleted. This can be difficult to trace because the #REF can occur within the formula itself—for example, =#REF*A2—and there’s no direct way to trace back to the original reference.

  • #VALUE!— Occurs when trying to do math with nonnumeric data; for example, D1+D2 will return the error if D1 is the column header.

  • ######— This isn’t really an error. It can occur if the column width is not wide enough for the formula result or if you’re trying to subtract a later date from an earlier date.

The initial help provided by Excel and Excel Starter may help trace the problem. When an error cell is selected, an exclamation icon appears from which you can select Trace Error. If Trace Error is selected, blue and red lines will appear on the sheet, as shown in Figure 1. A red arrow will connect to the first cell causing a problem. From that cell, blue lines will appear, pointing out the cells used in that cell’s formula. In Figure 1, the culprits are the 0 quantities being used in the Price Per calculation.

Figure 1. Use Trace Error to find the cells causing problems in the calculation.


Trace Precedents and Dependents

When you select a cell containing a formula and press F2, Excel highlights any cells on the sheet that are used by the formula, but it doesn’t highlight the cells on other sheets. To do that and more, use the trace precedent and trace dependent auditing arrows.

Use the formula auditing arrows found on the Formulas tab in the Formula Auditing group if you have a cell you want to trace, whether it’s to locate other cells used in that cell’s formula or what cells reference the selected cell. Just select the cell in question and click a trace button.

There are two types of auditing arrows:

  • Trace Precedents— If the selected cell contains a formula, arrows will point to the cells used by the formula.

  • Trace Dependents— If the selected cell is used in a formula in another cell, arrows will point to the cell containing that formula.

When you click one of the trace buttons, arrows appear, linking the cell to other cells that are directly connected to it, as shown in Figure 2. Click the trace button again and you will get the next level of cell connections. You can continue to click the button and Excel will continue adding tracing arrows to the sheet.

Figure 2. Use the Trace Precedents and Trace Dependents buttons to locate links to the selected cell, including links to other sheets.


If the connecting cell is on another sheet, Excel will display a dashed arrow pointing to a sheet icon, as shown in Figure 5.14. If you double-click the line, a Go To dialog will appear, listing every cell containing a link to the selected cell. You can then double-click one of the listed items and be brought directly to the linked cell.

To clear all the arrows, select Formulas, Formula Auditing, Remove Arrows. There is no way to go back just one level or remove one type of arrow.

Watch Window

The Watch Window found in the Formula Auditing group of the Formulas tab allows you to watch a cell update as you make changes that will affect it. This can be useful when you have formulas that span multiple sheets and you need to watch a cell on one sheet while you make changes to another.

Double-click a cell in the Watch Window to jump to that cell.


Watching a Formula Update on Another Sheet

To watch a formula update on another sheet, follow these steps:

1.
Select Formulas, Formula Auditing, Watch Window. The Watch Window dialog appears.

2.
Click Add Watch to bring up the Add Watch dialog.

3.
Select the cell you want to watch update and click Add.

4.
Repeat steps 2 and 3 for any additional cells that you want to monitor.

5.
Leave the Watch Window open and make changes to cells that will affect the watched cells.

6.
Whether directly linked or not, if the watched cells are in any way affected by the changes you make, the Watch Window will update to reflect the new value of the cell.

Evaluating Formulas

If you want to watch how Excel calculates each part of a formula, you can use Formulas, Formula Auditing, Evaluate Formula.

The Evaluate Formula window has three buttons you can use to investigate a formula. The buttons will work only on the underlined portion of the formula:

  • Evaluate— Replaces the underlined portion of the formula with the value.

  • Step In— Displays the actual contents of a cell if the underlined portion is a cell address. This may be a value or a formula. If it’s a formula, you have the option to continue stepping in until it is resolved.

  • Step Out— Returns to the previous level of evaluation.

You can continue to evaluate a formula until it is completely resolved.

Using Evaluate to Watch a Formula Calculate

To see how Excel is calculating a formula, follow these steps:

1.
Select the cell with the formula to evaluate.

2.
Select Formulas, Formula Auditing, Evaluate Formula.

3.
The formula appears in the evaluation window with some part of it underlined.

4.
Click the Step In button if it is activated. If it isn’t, skip to step 8.

5.
A new section appears in the window, showing the result of the underlined portion.

6.
Repeat steps 4 and 5 if the Step In button is still active.

7.
When the Step In button is no longer active, click the Step Out button to return to the previous level. Each click of the button returns you to the previous level.

8.
Click the Evaluate button and Excel will evaluate the underlined portion, replacing it in the formula with the returned or calculated value.

9.
Continue to click Evaluate or Step In to watch the formula calculate.

10.
Excel is done with the evaluation when only the calculated value appears in the Evaluation field. You can either click Restart to go through the steps again, or click Close to return to Excel.

Evaluating with F9

With the Evaluate Window, you have to go in the order that Excel calculates the formula. If you want to jump directly to a portion of the formula, you can highlight that portion and press F9 to evaluate to its value.

You should keep two things in mind when using F9 to evaluate a formula:

  • When highlighting the portion to evaluate, you must be careful to select the entire portion, including any relevant parentheses.

  • You must press Esc to leave the cell. If you don’t, Excel will replace your formula with the value you just evaluated to.

Other -----------------
- Microsoft Excel 2010 : Using Formulas - Table References in Formulas, Using Array Formulas
- Microsoft Word 2010 : Creating Desktop Publishing Documents - Adding a Watermark
- Microsoft Word 2010 : Creating Desktop Publishing Documents - Adding Desktop Publishing Effects
- Customizing Windows 7 : Customize the Start Menu
- Customizing Windows 7 : Change the Windows 7 Color Scheme
- Customizing Windows 7 : Set the Screen Saver
- Customizing Windows 7 : Set Up a Desktop Slide Show
- Customizing Windows 7 : Change the Desktop Background
- Customizing Windows 7 : Open the Personalization Window
- Deploying Applications (part 3) - Injecting in a Disk Image, Repackaging Legacy Applications
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Trailers Game
- The Banner Saga 2 [PS4/XOne/PC] PC Launch Trailer
- Welkin Road [PC] Early Access Trailer
- 7th Dragon III Code: VFD [3DS] Character Creation Trailer
- Human: Fall Flat [PS4/XOne/PC] Coming Soon Trailer
- Battlefleet Gothic: Armada [PC] Eldar Trailer
- Neon Chrome [PS4/XOne/PC] PC Release Date Trailer
- Rocketbirds 2: Evolution [Vita/PS4] Launch Trailer
- Battleborn [PS4/XOne/PC] 12 Min Gameplay Trailer
- 7 Days to Die [PS4/XOne/PC] Console Trailer
- Total War: Warhammer [PC] The Empire vs Chaos Warriors Gameplay Trailer
- Umbrella Corps [PS4/PC] Mercenary Customization Trailer
- Niten [PC] Debut Trailer
- Stellaris [PC] Aiming for the Stars - Dev. Diary Trailer #1
- LawBreakers [PC] Dev Diary #4: Concept Art Evolutions
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer