Add, subtract, multiply, and divide values in a control

You can help your users fill out forms based on your form template by using formulas to calculate the value for a control, based on data that the user enters into other controls. The calculations can include adding, subtracting, multiplying, and dividing values. For example, if you are designing a form template that electrical contractors will use to apply for permits, you can add a formula to a control that automatically calculates the total price that the contractor has to pay for the application by multiplying the number of permits in the application by the price of each permit.

In this article

What is a formula?

Using two or more operators in a formula

Insert a mathematical formula into a control

What is a formula?

A formula is an XPath expression made up of values, fields or groups, functions, and operators used to calculate and display other values. Formulas can be used to do the following tasks:

• Calculate mathematical values from values that you specify when designing the form template or values that users enter into controls when they fill out forms based on your form template..

• Display specific dates and times.

• Display the values that users enter into one control in another control.

• Set the default value of a field or control.

• Run a rule based on a value calculated by using a formula.

Each XPath expression that is used in a formula is a combination of values, functions, and operators that evaluates to a single value. A formula can contain several expressions. You can think of a formula as a sentence made up of one or more phrases, with each phrase representing one expression in the formula.

The following illustration shows the relationship between a formula and an expression.

A function is an expression that returns a value based on the results of a calculation. The values used in the functions are called arguments. You can use the standard XPath 1.0 functions that are included in InfoPath, as well as some InfoPath-specific functions. Find links to more information about InfoPath functions in the See also section.

Top of Page

Using two or more operators in a formula

When a formula contains two or more mathematical operators, InfoPath performs the calculation according to the precedence of the operator. The following list shows the order in which operations are performed:

1. Calculations within parentheses

2. Multiplication and division calculations

3. Addition and subtraction calculations

If the formula contains two operators that have the same precedence, the calculations are performed from left to right.

For example, imagine that you are creating a permit application form template that has a text box that displays the total cost of all of the permits submitted in an application. The values required by this formula come from other text boxes in the form. The text box that shows the total cost contains the following formula:

txtPermit1Qty * txtPermitCost1 + txtPermit2Qty * txtPermitCost2 /txtNumberOfPermits

This formula includes addition (+), multiplication (*), and division (/) operators. According to the precedence of operators, the multiplication and division calculations are performed before the addition calculation. Since the multiplication and division operators have the same precedence, the multiplication calculation is done before the division operator because the multiplication operator is located to the left of the division operator. The formula is calculated in this way:

1. The value in txtPermit1Qty is multiplied with the value in txtPermitCost1.

2. The value in txtPermit2Qty is multiplied with the value in txtPermitCost2, and that result is then divided by the value in txtNumberOfPermits.

3. The result of the calculation in step 1 is added to the results of the calculation in step 2.

To control calculation order, enclose the calculation that you want done first in parentheses. The calculations in the parentheses are performed before calculations outside the parentheses. Put calculations with parentheses to the left of calculations without parentheses. Calculations in nested parentheses are performed working from the inner to the outer parentheses.

For example, consider the following formula:

((txtPermit1Qty * txtPermitCost1) + (txtPermit2Qty * txtPermitCost2))/txtNumberOfPermits

In this calculation, the value that results from multiplying the values in txtPermit1Qty and txtPermitCost1 is added to the value that results from multiplying the values in txtPermit2Qty and txtPermitCost2. The sum of that calculation is then divided by the value in txtNumberOfPermits.

Top of Page

Insert a mathematical formula into a control

1. Double-click the control or field for which you want to create the formula.

2. Click the Data tab.

3. Click Insert Formula .

4. To insert a value or mathematical operator into the formula, type the value or the symbol for the mathematical operation in the Formula box.

   List of mathematical operations

   Operation	Symbol
   Add	+
   Subtract	-
   Multiply	*
   Divide	/

   Note: If your formula uses the division (/) operator, make sure that there is a space before and after the division operator. If the division operator does not have a space before and after it, InfoPath may interpret '/' as a separator for XPath location steps rather than as a division operator.

   Tip: Mathematical formulas usually depend on integer or decimal values as arguments. To avoid blank values in your formula, select the Treat blank values as zero check box in the Advanced category in the Form Options dialog box.

5. To check the formula for the correct syntax, in the Insert Formula dialog box, click Verify Formula.

   My formula contains errors

   Click Show Details in the Microsoft Office InfoPath dialog box to see the errors in the formula. The following are some suggestions for resolving these errors:

   • If you are using a function in your formula, verify that you are using the correct arguments for the function. Some functions require fields or groups while other functions require specified values as arguments. Find links to more information about functions in the See also section.

   • Delete and retype your formula to make sure that it is correctly typed.

6. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Top of Page