Show All

Calculate the average of a column of numbers

You can use expression boxes to calculate the average of a column of numbers and perform other calculations. When you create formulas in an expression box, you refer to the fields and groups to which controls on the form are bound, rather than the controls themselves. The formulas are created using XPath, but you can follow these steps without knowing XPath.

1. In design mode, place the insertion point where you want to insert the expression box.
2. If the Controls task pane is not visible, click More Controls on the Insert menu.
3. In the Controls task pane, click Expression Box.
4. Enter an expression such as the following in the XPath box in the Insert Expression Box dialog box:
   
   (sum(my:group1/my:group2/my:field3)) div (count(my:group1/my:group2/my:field3))
   

   Note  In this expression, my:group1/my:group2/my:field3 is only an example. When you type your average expression, you must replace the example with an XPath expression that identifies your specific field.

   How?

   1. In the Insert Expression Box dialog box, click Select XPath .
   2. In the Select a Field or Group dialog box, select the field that the column is bound to, and then click OK.

      Note  The field must be a repeating field or part of a repeating group.

   3. In the Insert Expression Box dialog box, wrap the text in the XPath box with the following expression: (sum(text ))div(count(text ))

      How?

      1. Cut the text in the XPath box and copy it to the Clipboard by selecting it and pressing CTRL+X.
      2. In the XPath box, type (sum(
      3. Press CTRL+V to paste the text you cut into the XPath box.
      4. After the text, type ))div(count(
      5. Press CTRL+V to paste the text again into the XPath box.
      6. After the text, type ))
5. To test the formula, click Preview Form on the Standard toolbar.

Note  If an empty field is included in an average formula, the expression box will show the total as NaN or 1. #QNAN (Not a Number). To avoid this, you can create a default value for the fields used in the equation.