Below are some of the Excel functions that are used to map and normalize data onto the data template for TotalRewards Builder.
Common Excel Functions
These are the most commonly used excel functions for data management.
1. DOLLAR - converts a number to text using currency format, with decimals rounded to the number of places specify. Mostly used for Benefits Coverage.
Formula: =DOLLAR(number, [decimals])
The DOLLAR function syntax has the following arguments:
- number (Required) A number, a reference to a cell containing a number, or a formula that evaluates to a number.
-
decimals (Optional) The number of digits to the right of the decimal point. If this is negative, the number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2.
2. TRIM - removes all spaces from text except single spaces between words. Use TRIM on test that you have received from another application that may have irregular spacing to remove extra spacing and clean data. Mostly used for Name, Email Address and Address.
Formula: =TRIM(text)
Meaning: =TRIM(the cell from where extra spaces will be removed)
The TRIM function syntax:
- text The text from which you want spaces removed.
3. PROPER - capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. Mostly used for Name and Address.
Formula: =PROPER(text)
Meaning: =PROPER(the cell that should be converted to proper case)
The PROPER function syntax has the following arguments:
- text (Required) Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.
4. IFERROR/IFNA - trap and handle errors in a formula, returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
Formula: =IFERROR(value, value_if_error) or =IFNA(value, value_if_na)
The IFERROR or IFNA function syntax has the following arguments:
- value (Required) The argument that is checked for an error.
- value_if_error or value_if_na (Required) The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!.