Skip to main content

Calculated Columns

Updated

Overview

Calculated columns let admins create new values from data in an imported residual report before mapping that data to Merchant Central. Using Excel-like formulas, you can transform, reformat, or combine report fields during the import process.

  • Transform imported data: Use formulas to adjust split percentages, revenue amounts, and other report values before import.
  • Reformat fields: Clean up or restructure values—for example, convert an ID to the format Merchant Central expects or combine several address fields into one.
  • Use Excel-like functions: Supported functions include IF, SEARCH, CODE, LEN, LEFT, RIGHT, MID, CONCATENATE, and TRIM. You can nest formulas and use logical conditions. The IF function also supports the AND (&) and OR (|) operators.
  • Map calculated columns: After you create a calculated column, it appears alongside the original report columns and can be mapped to a Merchant Central field in the same way.

Use calculated columns when processor data must be adjusted or reformatted before import. This lets you make recurring changes in Merchant Central instead of manually editing the CSV or Excel file each month.

Add a calculated column

  1. On the residual report mapping page, find the Calculations row, then click Add. 
    The Calculations row on the residual report mapping page
  2. In the dialog, enter a name for the calculated column. Then select the columns from the imported Excel file that you want to use in the formula. In this example, the calculated column is named Volume, and the selected Excel columns are Commission Code and Quantity Billed. 
    Dialog for naming a calculated column and selecting source columns

  3. Enter the formula in the Equation field, then click Add. In this example, the formula evaluates the Commission Code value. If the commission code is 2, the new Volume column uses the value from Quantity Billed. Otherwise, it returns 0. 
    A formula entered in the Equation field

    💡Click on any selected column to insert its code into the Equation field automatically.

  4. The calculated column appears in the Calculations row. Click Save Changes to finish setting it up. 
    A saved calculated column in the Calculations row

💡To delete a calculated column, click the X in the upper-right corner of its formula widget.

Map a calculated column

After you save a calculated column, it appears in the list of mappable columns alongside the original report columns. Drag the calculated column to the appropriate Merchant Central field.

A calculated column mapped to a Merchant Central field 

Create formulas

Formulas work similarly to formulas in Microsoft Excel and can contain nested functions.

Follow these rules when creating a formula:

  • Do not begin the formula with an equals sign (=).
  • Enclose Excel column names in curly braces, for example, {column_name}.
  • Enter column names exactly as they appear in the imported Excel file. Column names are case-sensitive.
  • Enter function names in uppercase.

Function reference

Function Description Syntax
IF Returns one value when a condition is true and another when it is false. Use the AND (&) or OR (|) operator to evaluate multiple conditions. IF(condition, true_value, false_value)
SEARCH Returns the starting position of a substring within a text string. Returns 0 if the substring is not found. SEARCH(substring, string)
CODE Returns the ASCII value of a character. CODE(character)
LEN Returns the number of characters in a text string. LEN(string)
LEFT Returns the specified number of characters from the beginning of a text string. LEFT(string, number)
RIGHT Returns the specified number of characters from the end of a text string. RIGHT(string, number)
MID Returns a specified number of characters from a text string, beginning at the specified position. MID(string, position, length)
CONCATENATE Joins two or more text strings into one string. CONCATENATE(string1, string2, ..., stringN)
TRIM Removes leading and trailing spaces and replaces repeated spaces within a string with a single space. TRIM(string)

Formula examples

Function Column and value Formula Result
IF Commission Code: 2
Quantity Billed: 10000		 IF({Commission Code}=2,{Quantity Billed},0) 10000
IF with AND Column1: 200
Column2: 300		 IF({Column1}=100 & {Column2}=300,1,0) 0
IF with OR Column1: 200
Column2: 300		 IF({Column1}=100 | {Column2}=300,1,0) 1
SEARCH Date: "1/1/2019" SEARCH("2019",{Date}) 5
CODE Type: "T" CODE({Type}) 84
LEN DBA Name: "Tom's Diner" LEN({DBA Name}) 11
LEFT DBA Name: "Tom's Diner" LEFT({DBA Name},5) Tom's
RIGHT DBA Name: "Tom's Diner" RIGHT({DBA Name},5) Diner
MID Phone Number: "Tel: 505-123-4567x100" MID({Phone Number},6,12) 505-123-4567
CONCATENATE Address: "656 Godfrey Road"
City: "New York"
State: "NY"
ZIP: "10003"		 CONCATENATE({Address},", ",{City},", ",{State}," ",{Zip}) 656 Godfrey Road, New York, NY 10003
TRIM Address: "  656    Godfrey    Road  " TRIM({Address}) 656 Godfrey Road