Excel Functions

Excel-Style Functions

Hire2Retire provides a really powerful set of features to apply complex formulas on data for transformation. Here are the set of functions that are available.

Math Functions (ABS, EXP, INT etc. )

The Excel Math Functions perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, exponents & logarithms. For example - RAND() function returns a random number between 0 and 1.

Fig 1. Math operation with RAND() function example

Trigonometry Functions (SIN, TAN, COS etc.)

Excel uses several built-in trigonometry functions. Those that are most often used including SIN, COS, TAN, RADIANS etc are provided.

Fig 2. Trignometry Functions

Date Time Functions (Today, Now() etc.)

Excel Date and Time functions can be used to extract information from, and perform operations on Dates and Times. Some Date & Time functions provided by us are DATE(), NOW(), SECOND(), TODAY().

Fig 3. Date Time Functions

Statistical Functions (Average, Mean etc.)

Statistical functions apply a mathematical process to a group of values. For example ABS() - returns the absolute value, MAX() - returns the highest value in a group of values.

Fig 4. Statistical Functions

Prev() Function

The PREV function helps you check and compare previous values of AD/AAD attributes. It is useful when you need to track changes in identity data. PREV() - Checks the previous value of a specified AD/AAD attribute and returns it. If the previous value is the same as the current value, it returns the current value.

Fig 5. Prev Function

Text Operations (Clean, Trim, Lowercase etc.)

There are Text functions that can help you to deal with textual data. These functions can help you to change a text, change the case, find a string, count the length of the string, etc. For example LEFT() - Returns the specified number of characters from the start of a text string, LOWER() - Converts all letters in a text string to lowercase etc.

Fig 6. Text Operations

Fig 7. Text Operations Example

Fig 8. Search Text Operation Example

Information Functions (ISTEXT, ISNUMBER etc.)

Information function provides information about the content entered. For example ISBLANK() - Checks whether a reference is to an empty cell, and returns TRUE or FALSE, ISEVEN() - Returns TRUE if the number is even.

Fig 9. Data Validation

Fig 10. Data Validation Example

Financial Operations (FV, NPV, PV, etc.)

These function helps user tackle any financial problems during mapping. It should be noted that while each of these formulas and functions are useful independently, they can also be used in combinations that make them even more powerful.

Fig 11. Financial Operations

Logical Operations (OR, NOT etc)

Wide range of logical functions including AND, FALSE, IF, NOT etc are provided which can be used to carry out more than one comparison in your mapping or test multiple conditions instead of just one.

Fig 12. Logical Operations

Fig 13. Logical Operations Example

Engineering Operations (BIN2DEX, HEX2DEC etc.)

The Engineering functions perform the most commonly used engineering calculations, many of which relate to converting between different bases.

Fig 14. Engineering Operations