Workbench Formula Examples

Owned by Brian Ziebart (Deactivated)
Last updated: 03 06, 2019 by Laura Bach [X] (Unlicensed)

Issue/Symptom/Question

Help with Workbench formulas

Applies To

Penta Workbench

Analysis Grid Formulas

Resolution/Fix/Answer

Formula Guide

Sample Percentage Formulas 

There are no null checks in these examples.   All examples assume that there is a number in the columns being used.
  1. Raw, unformatted calculated percentage

    1. [Forecast at Complete Cost] /  [Cost - JTD]

    2. This formula calculates the value of the "Forecast at Complete Cost" column divided by the "Cost - JTD" column and displays it as a raw decimal value.

    3. Values where the denominator are equal to 0 are displayed as "NaN" (not a number).

    4. To make this formula work, replace the fields in orange with the column names specific to the report you are working with.

  2. Calculated percentage with basic formatting]

    1. FormatPercent([Forecast at Complete Cost] /  [Cost - JTD])

    2. This formula calculates the value of the "Forecast at Complete Cost" column divided by the "Cost - JTD" column and displays it as a percentage.

    3. Values where the denominator are equal to 0 are displayed as "NaN" (not a number).

    4. Values where the numerator are equal to 0 are displayed as "Infinity".

    5. To make this formula work, replace the fields in orange with the column names specific to the report you are working with.

  3. Calculated percentage with basic formatting and decimal places specified

    1. FormatPercent([Forecast at Complete Cost] /  [Cost - JTD], 3)

    2. This formula calculates the value of the "Forecast at Complete Cost" column divided by the "Cost - JTD" column and displays it as a percentage with 3 decimal places displayed.

    3. Values where the numerator are equal to 0 are displayed as "Infinity".

    4. Values where the denominator are equal to 0 are displayed as "NaN" (not a number).

    5. To make this formula work, replace the fields in orange with the column names specific to the report you are working with.  Replace the number in pink with the number of decimal places that you want displayed.

  4. Calculated percentage with formatting and removal of NaN and Infinity values

    1. iif([Cost - JTD] = 0, (FormatPercent(0, 3)), (FormatPercent([Forecast at Complete Cost] /  [Cost - JTD], 3)))

    2. This formula calculates the value of the "Forecast at Complete Cost" column divided by the "Cost - JTD" column and displays it as a percentage with 3 decimal places displayed.  If the "Cost - JTD" column is equal to 0, it shows 0.000% instead of NaN.

    3. To make this formula work, replace the fields in orange with the column names specific to the report you are working with.  Replace the numbers in pink with the number of decimal places that you want displayed.

Sample Text Manipulation Formulas

  1. Position of a character within a string

    1. InStr([Project Manager Name], ",")

    2. This formula displays the position of the first comma it can find in the Project Manager Name column.

    3. If the character is not found, it will display a 0.

    4. To make this formula work, replace the field in orange with the column name specific to the report you are working with.   Replace the character in pink with the character(s) you are looking for.

  2. Length of a string

    1. Len([Project Manager Name])

    2. This formula displays the length of string that is displayed in the Project Manager Name field.

    3. To make this formula work, replace the field in orange with the column name specific to the report you are working with.

  3. Display the last x number of characters from a given string

    1. Right([Project Manager Name], 4)

    2. This formula displays the last 4 characters from the Project Manager Name column.

    3. To make this formula work, replace the field in orange with the column name specific to the report you are working with.  Replace the number in pink with the number of characters you want displayed.   "Right" can be replaced with "Left" if you want to display characters from the front of the field instead of the characters from the end of the field.

  4. Display the length of a portion of a string

    1. Len([Project Manager Name]) - InStr([Project Manager Name], ",") - 1

    2. This formula calculates the length of the first name portion of the "Project Manager Name" column which is formatted as {last name}, {first name} {middle init}.

    3. The "-1" portion is needed in this particular formula because there is a space between the comma and the first name.   If you don't have a space, just remove the "-1" part of the formula.

    4. To make this formula work, replace the field in orange with the column name specific to the report you are working with.  Replace the character in pink with whatever the separator character is that you are looking for.

  5. Display a portion of a string

    1. Right([Project Manager Name], (Len([Project Manager Name]) - InStr([Project Manager Name], ",") - 1))

    2. This formula display the first name portion of the "Project Manager Name" column which is formatted as {last name}, {first name} {middle init}.

    3. The "-1" portion is needed in this particular formula because there is a space between the comma and the first name.   If you don't have a space, just remove the "-1" part of the formula.

    4. To make this formula work, replace the field in orange with the column name specific to the report you are working with.  Replace the character in pink with whatever the separator character is that you are looking for.

  6. Display numeric as text in order to create formula
    1. CStr([Costcode]) 
    2. This formula will display the "Costcode" as a text
  7. Combine 2 columns into 1 column
    1. Column+ ","+Column