Formulas

Owned by Keith Mangold
Last updated: Jul 25, 2016
3 min read

Within Aeros Vision's Financial Reporting function, in either Lines or Column, there are fields available to configure different Formulas. Formulas are grouped into one of two categories:

  • G/L Account Selection - Used when specifying criteria for locating numbers (dollars/units/stats) from the ERP database pertaining to specific G/L Accounts. Using the Formula Builder, you can select a wide range of filter criteria.
      

  • Mathematical - Used in an Excel like fashion to calculate numbers based upon other lines/columns.

G/L Account Selection

The easiest way to build your criteria for G/L Account selection is to click on the ... ellipses button within the formula cell to the right. Then, using the Account Selector / Formula Builder, select the desired G/L Account segments and values. Refer to the Account Selector documentation for more information.

 

 

Within the Account Selector, the following fields are available that come from the setup of the G/L Account Master.

  •  

    • AccountDescription - The Accrual Description specified.

    • AccountName - The Short Name specified.

    • AccountStatus - A = Active, I = Inactive

    • AccountType - Asset, Liability, Equity, Expense, or Income.

    • CompanyCode - The first segment of the G/L Account.

    • DivisionCode - The second segment of the G/L Account.

    • GLAccountNumber - The full 5 segment G/L Account # in Co-Div-Seg3-Seg4-Seg5 format.

    • GLSeqNo - The unique sequence # of the G/L Account. Advanced users that know the Sequence # only.

    • Segment3 - The value of the 3rd Segment of the G/L Account.

    • Segment3Description - If using Segment Descriptions, the specified description of the segment. 

    • Segment3Name - If using Segment Descriptions, the specified name of the segment. 

    • Segment4 - The value of the 4th Segment of the G/L Account.

    • Segment4Description - If using Segment Descriptions, the specified description of the segment. 

    • Segment4Name - If using Segment Descriptions, the specified name of the segment. 

    • Segment5 - The value of the 5th Segment of the G/L Account.

    • Segment5Description - If using Segment Descriptions, the specified description of the segment. 

    • Segment5Name - If using Segment Descriptions, the specified name of the segment. 

    • SubTypeDescription - If a SubType is specified on the G/L Account, the specified Sub Type's description.

    • SubTypeName - If a SubType is specified on the G/L Account, the specified Sub Type's description.

    • SubTypeType - If a SubType is specified on the G/L Account, the specified type of the Sub Type.

Account Selector Tips:

  •  

    • Do not use the "Begins with" or "End with" operators; the system currently does not support them.

      • Instead use the "Is Like" or "Is Not Like" operators.
          

    • When using "Is Like" or "Is Not Like" you must use a % (percent sign) as a the wildcard.

      • For example, to say "Account Description Is Like "TELEPHONE":
        Field: AccountDescription   Operator: Is Like    Value:  TELEPHONE%

        This will find all G/L Accounts where the Description starts with "TELEPHONE"
        "Telephone" "Telephone & Cell" "etc..."

        You could also do "%TELEPHONE%" which would find anything with "TELEPHONE" in the name:
        "Telephone" "Telephone & Cell" "Mangers Telephones" "Farm Telephones" 

Without using the Account Selector / Formula Builder, you can also enter in a standard WHERE clause type criteria. Use the samples created by the Account Selector first to see the structure of the Filter string.

Mathematical

For lines or columns where the Line Type is "Formula" or the Column Type is "Formula" you can specify mathematical type formulas to calculate values based upon other lines and cells.

The available operators are + - / * for Add, Subtract, Divide, and Multiply.

For example:

  •  

    • =L1 + L2
      This sample will get value of the line with Line # L1 and add it to the line with Line # L2.


    • =A1+A2+A3
      This will sum together all the lines A1, A2, and A3.


    • =T10-T05
      This will subtract T05 from T10.  

 

Note: It is important to begin each formula with the "=" (equal) sign. This tells the system that it should evaluate the formula. 

 

IF Statements

The IF function is one of the most popular functions in Excel and is also available within Vision Financials. It allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says:

IF(Something is True, then do something, otherwise do something else)

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

For example:

  •  

    • =IF(L10=0,L50,L10)
      This stats that if L10 is equal to 0 (true), then use the value of L50.
      Otherwise, if L10 is NOT equal to 0 (false), then use the value of L10.