Formula
Last updated
Last updated
Mashmatrix Sheet uses formulas to calculate and display the contents of cells in a sheet. Formulas in Mashmatrix Sheet are syntactically compatible with the formulas used in Salesforce field definitions, so there are a number of Salesforce formulas that work directly on Mashmatrix Sheet as well.
Formulas are written using Formula Editor. The Formula Editor can be used to create a new formula column or to edit a formula from the column formula settings.
[A] Variable Tabs - References fields, columns, global variables, functions, etc. to be used in the formula. By selecting a variable and pressing the "Insert" button, the variable is inserted at the cursor position in the formula input.
[B] Formula Input - An area to enter a formula in text.
[C] Validate Button - The text entered into the formula input is checked for syntax and validated as a formula. A validated formula will be marked with a check mark. If there is a validation error, an error message will appear in red at the bottom of the formula input.
[D] Blank Field Handling - If the values in number/currency/percentage type variables referenced in the formula are blank, choose whether to treat them as 0 or empty as is.
Within a formula, you can refer to the field value of the record for which the formula is evaluated as a variable. For the formula specified in a formula column, the fields are from the object linked to the sheet where the column belongs.
To pick and enter a field reference in the formula, follow these steps.
Select the "Field" tab from the variable tabs in the Formula Editor.
Select a field you want to reference from the list in the tab and press the "Insert" button.
Fields referenced in the formula do not necessarily have to be included as columns in the sheet. Fields that are not included in the sheet are also available in the formula.
Field variables cannot be used as column arguments for aggregate functions or VLOOKUP functions. To use them as column arguments for these functions, add the fields as columns in the sheet in advance, then select "All Rows" from the "Column" tab and insert the column variable.
Within a formula, you can refer to columns in sheets as variables.
There are two types of column variables:
Column Value (Current Row) - Refers to the column value of the record where the formula is currently evaluated
In the text of the formula, it is written in the form [@Column Name].
Column Reference (All Rows) - Refers to the entire column contained in the sheet. It can be used from special functions that accept column references as arguments, such as aggregate functions.
In the text of the formula, it is written in the form [#Column Name]. For the column contained in the external sheet is written in the form [sheet ID].[#Column Name].
"Column Value (Current Row)" variables can refer only to the columns in the sheet where the formula will be used. On the other hand, "Column Reference (All Rows)" variables can refer to any columns in the sheets in addition to the sheet in which the formula is to be used.
To add a column value variable (current row) that refers to the value of the record in the current row, do the following:
Select the "Column" tab from the variable tabs in the Formula Editor.
Select a column from the list of columns in the tab.
With "Current Row" selected from the Target Row radio button [B], press the "Insert" button.
To add a column reference variable (all rows) that refers to an entire column, do the following:
Select the "Column" tab from the variable tabs in the Formula Editor.
Select a sheet that contains the target column from the sheet switching menu [A].
Select a column from the list of columns in the tab.
With "All Rows" selected from the Target Row radio button [B], press the "Insert" button.
Global variables are variables that hold information that is independent of the object or record for which the formula is evaluated and always accessible by reference.
To pick and enter a global variable reference in the formula, follow these steps.
Select the "Global" tab from the variable tabs in the Formula Editor.
Select a global variable you want to reference from the list of variables in the tab, and press the "Insert" button.
The following are the global variables supported by the Mashmatrix Sheet formulas. Note that not all global variables in Salesforce are available, as they are a subset of the global variables available in Salesforce formulas.
User ID (Id)
Username (Username)
Email (Email)
First Name (FirstName)
Last Name (LastName)
Name (Name)
Alias (Alias)
Company Name (CompanyName)
Department (Department)
Title (Title)
Manager ID (ManagerId)
Profile ID (ProfileId)
Role ID (UserRoleId)
Currency Code (DefaultCurrencyIsoCode)
Language (LanguageLocaleKey)
Locale (LocaleSidKey)
Timezone (TimeZoneSidKey)
Organization ID (Id)
Organization Name (Name)
A function allows you to convert variables within the formula and utilize as a different value.
To pick and enter a function reference in the formula, follow these steps.
Select the "Function" tab from the variable tabs in the Formula Editor.
Select a function you want to reference from the list in the tab, and press the "Insert" button.
