		Site Navigation
	Home
	Products
	Tags
	Levels
	Dates
	Authors

		5 MOST RECENT
	Click to view Braintrove article: Perform a Case-Insensitive String Comparison without Using Translate() Written by Gregory Scot Collins Thursday, 20 August 2009, 7:21 PM Products: InfoPath and Office Hidden in undocumented optional parameters of an extended function, there is a way to perform a case-insensitive string comparison without the need for the translate() function. Perform a Case-Insensitive String Comparison without Using Translate()
	Click to view Braintrove article: Force a Field Value to a Boolean Using Rules Written by Gregory Scot Collins Wednesday, 29 July 2009, 9:42 AM Products: InfoPath and Office Custom data validation is not available for fields bound to a secondary data source making it a challenge to validate to a specific data type. Using rules you can force a field value to a Boolean. This is especially useful when standardizing unconventional Boolean values copyied from a secondary data source. Force a Field Value to a Boolean Using Rules
	Click to view Braintrove article: Quickly Crop to a Shadow Written by Gregory Scot Collins Friday, 29 May 2009, 8:55 AM Product: Photoshop Learn how to crop to a shadow perfectly in a few seconds without the need to fuss with zoom and look for slight color variations. Quickly Crop to a Shadow
	Click to view Braintrove article: Sum Repeating Time Values Written by Gregory Scot Collins Tuesday, 14 April 2009, 2:33 PM Products: InfoPath and Office Sum time values in a repeating structure by converting them to seconds, and then display the results as seconds, minutes or hours. Sum Repeating Time Values
	Click to view Braintrove article: Preview and Open Forms after Upgrading to Internet Explorer 8 Written by Gregory Scot Collins Friday, 10 April 2009, 12:29 PM Products: InfoPath, Internet Explorer, Office, Visual Studio, Visual Studio Tools for Applications, Visual Studio Tools for Office and Windows After upgrading to Internet Explorer 8 you might find that you can no longer preview or open your managed code forms. There is an easy solution to this. Preview and Open Forms after Upgrading to Internet Explorer 8

		5 MOST POPULAR
	Click to view Braintrove article: Pass Query String Parameters to an ASP.NET Xml Control Written by Gregory Scot Collins Friday, 19 May 2006, 5:19 PM Product: ASP.NET Use the Page_Load method of your ASPX page to attach a list of parameters to your asp:Xml control that will be passed through to your XSL transform. Pass Query String Parameters to an ASP.NET Xml Control
	Click to view Braintrove article: Access a Method in a Master Page with Code-Behind Written by Gregory Scot Collins Friday, 16 June 2006, 7:09 AM Product: ASP.NET Write code for pages with code-behind to access a method in an immediate or parent master page from a content page. Access a Method in a Master Page with Code-Behind
	Click to view Braintrove article: Prevent Namespace Prefixes from Being Copied to the Output Written by Gregory Scot Collins Monday, 1 May 2006, 7:18 AM Products: HTML, JavaScript, XML, XPath and XSLT The extra namespace prefixes required to transform XML are not always desirable on the output. This article describes a technique for excluding specific namespace prefixes from the output. Prevent Namespace Prefixes from Being Copied to the Output
	Click to view Braintrove article: Create Groups in a Drop-Down List Box Written by Gregory Scot Collins Saturday, 17 June 2006, 5:57 AM Products: CSS, FireFox, HTML and Internet Explorer Create grouped option-sets in your drop-down list boxes, each with its own non-selectable header. Create Groups in a Drop-Down List Box
	Click to view Braintrove article: Change the Default Action of the Power Button on the Windows Vista Start Menu Written by Gregory Scot Collins Friday, 8 December 2006, 5:30 AM Product: Windows In Windows Vista, you can change the default action for the power button on the Start menu, as well as for other power events, in the advanced power settings. Change the Default Action of the Power Button on the Windows Vista Start Menu

		5 MOST FORGOTTEN
	Click to view Braintrove article: Install Windows 7 Beta onto a Virtual Machine Written by Gregory Scot Collins Wednesday, 25 February 2009, 9:52 AM Products: Virtual Server and Windows Walk through the simple process of installing Microsoft Windows 7 Beta onto a virtual machine with screenshots for every step in the process. Install Windows 7 Beta onto a Virtual Machine
	Click to view Braintrove article: Configure Lab Color Mode Defaults for Braintrove Articles Written by Gregory Scot Collins Wednesday, 30 April 2008, 10:36 PM Product: Photoshop Make the appropriate changes in a couple of dialog boxes in order to complete the tasks of Braintrove articles involving the Photohshop Lab Color mode. Configure Lab Color Mode Defaults for Braintrove Articles
	Click to view Braintrove article: Permanently Change the Default Styles for New Word Documents Written by Gregory Scot Collins Wednesday, 18 February 2009, 4:54 PM Products: Office and Word Using this surprisingly simple technique, you can quickly change the way that all newly created Microsoft Word documents look. Permanently Change the Default Styles for New Word Documents
	Click to view Braintrove article: Enable Themes in Windows Server Written by Gregory Scot Collins Monday, 31 March 2008, 3:25 PM Product: Windows Server Consumer versions of Windows have great themes. These themes are available to Windows Server too, but must be enabled before you can enjoy them. Enable Themes in Windows Server
	Click to view Braintrove article: Use Logical Operators in Place of Conditional Expressions Written by Gregory Scot Collins Wednesday, 28 February 2007, 9:22 AM Product: SQL Server Simplify your SQL query statements and reduce code by replacing conditional IF-ELSE expressions with appropriate logical operators. Use Logical Operators in Place of Conditional Expressions

		Microsoft MVP

This article has been viewed 215 times.

Eliminate NaN and Infinity Errors in Calculations

Page 1 of 2

Written by Gregory Scot Collins

Tuesday, 31 March 2009, 11:47 AM

This article has been tested to work with the following products and versions. No guarantee of compatibility, with or without modification, is offered for products or versions other than those listed.

InfoPath 2007 and 2003 (Microsoft)
Office 2007 and 2003 (Microsoft)

IN THIS ARTICLE:

When using calculated fields or expression boxes that incorporate division, the likelihood of receiving calculation errors is great. Not only can this look bad in the form, it can cause downstream issues for data stored in a database.

You will encounter two kinds of calculation errors. The first is the NaN error which occurs when attempting calculations with non-numerical values, including blanks. The second is the Infinity and -Infinity errors which occur when dividing by zero or a blank.

NOTE: These errors are actually useful in that they identify data problems that need correction. However, if you don't require a correction and don't want the ugly error messages, you can use the following techniques to replace these errors with a zero or a blank.

Create a test form

To start, create a test form using the following steps:

Create a new blank form.
Open the Data Source task pane.
Add a Decimal field named Dividend.
Add a Decimal field named Divisor.
Add a Decimal field named Quotient.
Double-click Quotient.
In the Field Or Group Properties dialog box, click the Insert Formula button to the right of the Value text box.
In the Formula text box type: ../my:Dividend div ../my:Divisor, and then click OK twice.
Right-click the myFields and choose Controls In Layout Table.

Preview the form and you will note that Quotient has the value NaN, which stands for Not A Number. If you were to type 5 as the Dividend, the Quotient value changes to Infinity. Change the Dividend to -5 and the Quotient is now -Infinity. Exit the preview.

Eliminate just the NaN error

Use the following steps to eliminate the NaN error:

In the Data Source task pane, double-click Quotient.
In the Field Or Group Properties dialog box, click the Insert Formula button to the right of the Value text box.
Replace the existing formula with one of the following:

If you want a zero value, type: translate(../my:Dividend div ../my:Divisor, "aN", "0").
If you want a blank value, type: translate(../my:Dividend div ../my:Divisor, "aN", "").

Click OK twice.

Preview the form and you will note that the Quotient no longer has the value NaN, but zero or blank, depending on which formula you used. Exit the preview.

The translate() function replaces the character 'a' with a zero or nothing, and replaces both instances of the character 'N' with nothing, thus converting the error "NaN" into a zero or a blank.

CONTINUED: Eliminate all of the errors

1 | 2 | Next >

		Bookmark this Article
	Stumble It!
	Digg It!
	del.icio.us

		Article Tags
	Browser-Enabled Forms Divide By Zero Dividend Division Divisor Errors Expression Boxes Formulas Infinity InfoPath IPFS Office Postbacks Quotient Text Boxes translate()

		Great Deals
	TigerDirect Exclusive Deals, Limited Time Offers, Act Now And Save!
	Find all current special offers on Adobe products.

Copyright © 2006-2010 Braintrove. All rights reserved. Braintrove, braintrove.com, and the Braintrove logo are trademarks of Gregory Scot Collins in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners. Any rights not expressly granted herein are reserved.