User Calc Fields on Other BFA Worksheets

The BFA workbook allows you to define up to ten calculation columns for use on the Data worksheet. However, you can use those same User Calc Field columns on the Division Totals and Billing Code Totals worksheets in BFA. The User Calc Field columns can hold formulas and create data not elsewhere on the Data worksheet. The BFA workbook can also convert those formulas to use in the Division Totals and Billing Code Totals worksheets.

Note: When you enter a formula for such a column, we recommend that you use Defined Names instead of cell references. You can use the F3 key while in the User Calc Field cell to pop up a Defined Names list. [See also Defined Names Tips below.]

BFA’s Setup worksheet, on the Site Setting level, allows you to indicate how you want the subtotal rows on the Division Totals and Billing Code Totals worksheet to be calculated..

  1. In the BFA workbook (preferably in Project Analysis mode), select Site Settings.

  2. Go to row 67 on the Setup worksheet.
  3. Scroll to your User Calc Field columns.
  4. Select one of the following for each column in use.
    1. F = use the formula for the column (in row 61) converted to the appropriate worksheet (Division Totals or Billing Codes).
    2. N = leave the cell blank. This option is used by Spitfire whenever the resulting value created by a sum or formula is invalid (such as when adding units with different units of measure). The following message will appear in such circumstances.
    3. S = use the sum of the constituent amounts. This option is often used for money.
  5. Remember to save.

For example, the codes on the following  User Calc Field columns would result in the following types of data on the Division Totals worksheet.

During the initial creation of the Division Totals or Billing Code Totals worksheet, the User Calc Field columns are reviewed and converted for use in the associated worksheet. The conversion remains in place during the session of BFA or until you make changes to the Setup data.

Defined Names Tips

Did you know that a user formula can be configured to

  • ONLY operate in either the Cost Code total row OR the Account Category row?
  • use one calculation for Cost Code rows and a different formula for Account Category rows?
  • target specific attributes, like Account Category, Account Class or Account Type?

Normally, a User Calc Field containing a formula is applicable to all row types. However, using Defined Names in the formula increases the flexibility of the User Calc Fields.

For Example:
Defined Name Samples Used in a Formula Objective
AccountCategory Labor, Sub, Material =IF(AccountCategory= “Material”… Looking for “Material” rows only
Acct_Class L, LB, M, O =IF(Acct_Class = “LB”… Looking for “Labor Burden” rows only
Acct_Type RV, EX =IF(Acct_Type = “RV”… Looking for “Revenue” rows only
RowsType T, A =IF(RowsType = “T”… Looking for Cost Code rows only