Work with the Formulas to Optimise Test Data
Last updated
Last updated
Formulas in the Test Data module help boost performance and optimize test scenarios by generating valid data on the go with minimal user effort. These formulas allow for dynamic data creation, reducing the need for manual input and enabling efficient test case execution.
To call a formula, go to the Test Data module in the test case.
Select the parameter field where you want to use the formula. To do so, simply add '=' which will show a dropdown of all formulas. Select or type the formula as shown in the table below and add the required parameters or strings required by the formula.
You can also call parameters in the Test Data module of a test case while adding a formula. To do so, call the equation and add double curly brackets { { inside the Parentheses. A dropdown will appear showing all available parameters, including Local, Global, and Runtime parameters.
FORMULA
DESCRIPTION
EXAMPLE
PARAMETERS
SUM(number1, [number2], ...)
Returns the sum of all given numbers
=SUM(10, 20, 30) returns 60
number1: Numeric arguments to sum.
number2: Numeric arguments to sum.
AVERAGE(number1, [number2]
Calculates the average of the given numbers.
=AVERAGE(10, 20, 30) returns 20
number1: Numeric arguments for which you want the average.
number2: Numeric arguments for which you want the average.
SUBTRACT(number1, [number2], ...)
Subtract the given numbers together.
=SUBTRACT(30,20) returns 10
number1: Numeric arguments to subtract.
number2: Numeric arguments to subtract.
MULTIPLY(number1, [number2], ...)
This function multiplies all the numbers provided as parameters.
=MULTIPLY(1, 2, 3) returns the product as 6
number1: The first number or range to be multiplied.
[number2]: Additional numbers or ranges to be multiplied.
MODULUS(number, divisor)
Returns the remainder after dividing one number by another. It is often used to determine if a number is even or odd, or to perform cyclic operations.
=MOD(10, 3) returns 1, because when 10 is divided by 3, the remainder is 1
number: The number to be divided (the dividend)
divisor: The number by which to divide (the divisor)
FORMULA
DESCRIPTION
EXAMPLE
PARAMETERS
CONCATENATE(“text1”, [text2], ...)
Joins several text strings into one.
=CONCATENATE("Hello", " ", "World")
text1: string to be joined into a single string
text2: string to be joined into a single string.
LEFT(text, [num_chars])
Returns the first character(s) from a text string, starting from the left.
=LEFT("Hello World", 5) returns "Hello". If num_chars is not specified, =LEFT("Hello World") returns "H".
text: The text string from which to extract the characters.
[num_chars]: (Optional) The number of characters to extract from the left side of the text. If omitted, it defaults to 1.
RIGHT(text, [num_chars])
Returns the specified number of characters from the end of a text string.
=RIGHT("Hello World", 5) returns "World".
text: The string from which you want to extract the characters.
[num_chars]: (Optional) The number of characters to extract from the end. Default is 1.
MID(text, start_num, num_chars)
Extracts a substring from a text string, starting at a specified position.
=MID("Hello World", 7, 5) returns "World"
text: The string from which you want to extract the characters.
start_num: The position of the first character to extract (1-based).
num_chars: 'The number of characters to return from text.
LOWER(text)
Converts a text string to lowercase.
=LOWER("Hello World") returns "hello world"
text: The text string which you want to convert text to lowercase.
UPPER(text)
Converts a text string to uppercase.
=UPPER("Hello World") returns "HELLO WORLD".
text: The text string which you want to convert text to uppercase.
PROPER(text)
Capitalises the first letter of each word in a text string
=PROPER("hello world") returns "Hello World"
text: The text string in which you want to capitalise the first letter in each word
SPLIT(text, delimiter, index)
Splits a text string into multiple parts based on a specified delimiter.
=SPLIT("Hello,World,Excel", ",") splits the text "Hello,World,Excel" into three parts: "Hello", "World", and "Excel"
text: The text string to split.
delimiter: The character or string used to divide the text.
index: The position of the part to return (1-based).
For example, 1 returns the first part, 2 returns the second part, and so on.
FORMULA
DESCRIPTION
EXAMPLE
PARAMETERS
NOW()
Returns the current date and time based on your system's date & time format.
=NOW() would return 08/23/2024 12:20 pm based on your system's date time format
TODAY()
Returns the current date as per local system date format.
=TODAY() would return 08/08/2024 if today’s date is 8th August 2024.
DAY()
Returns the day of the month, a number from 1 to 31.
=DAY("01/08/2024") returns 1
MONTH()
Returns the month, a number 1 (January) to 12 (December).
=MONTH("01/08/2024") returns 8.
YEAR()
Returns the year from a date.
=YEAR("01/08/2024") returns 2024.
HOUR()
Extracts the hour from a time value.
=HOUR("12:30:45 PM") returns 12.
MINUTE()
Extracts the minute from a time value.
=MINUTE("12:30:45 PM") returns 30.
SECOND()
Extracts the second from a time value.
=SECOND("12:30:45 PM") returns 45.
FORMULA
DESCRIPTION
EXAMPLE
PARAMETERS
GENDER()
Generates gender/sex information.
=GENDER() would return "Male"
PREFIX(sex)
Returns a prefix (Mr., Mrs., etc.) based on the specified sex.
=PREFIX("Male") returns "Mr."
sex: The gender for which to get the prefix ("Male" or "Female")
FIRST_NAME(country name, sex, min, max)
Generates a first name based on the specified country and sex.
=FIRST_NAME("United States", "Male", 3, 10) would return "John"
country name: The name of the country
sex: The gender for the name ("Male" or "Female")
min: The minimum length of the name
max: The maximum length of the name
LAST_NAME(country name, sex, min, max)
Generates a last name based on the specified country and sex.
=LAST_NAME("United States", "Male", 3, 10) would return "Smith"
country name: The name of the country
sex: The gender for the name ("Male" or "Female")
min: The minimum length of the name
max: The maximum length of the name
FULL_NAME(Country, Sex, MinLength, MaxLength)
Generates a full name based on the specified country and sex.
=FULL_NAME("United States", "Male", 3, 10) would return "Smith".
country name: The name of the country.
sex: The gender for the name ("Male" or "Female")
min: The minimum length of the name.
max: The maximum length of the name.
MIDDLE_NAME(Country, Sex, MinLength, MaxLength)
Generates a middle name based on the specified country and sex.
=MIDDLE_NAME("United States", "Male", 3, 10) would return "Smith".
country name: The name of the country.
sex: The gender for the name ("Male" or "Female")
min: The minimum length of the name.
max: The maximum length of the name.
DATE_OF_BIRTH(dd/MM/yyyy, MinAge, MaxAge)
Generates a date of birth within the specified range.
=DOB("dd/mm/yyyy", 5, 25) would return "15/06/1985".
dd/mm/yyyy: The format for the date of birth.
MinAge: The start date of the range.
MaxAge: The end date of the range.
COUNTRY_NAME
Returns the name of a country randomly.
=COUNTRY() would return "United States".
CREDIT_CARD_CVV(“Country name”)
Generates a credit card CVV code based on the specified country.
=CREDIT_CARD_CVV("US") would return "123"
CREDIT_CARD_NUMBER(“Country Name)
Generates a credit card number based on the specified country.
=CREDIT_CARD_NUMBER("US") would return "123".
Country Name: The country code for which to generate the card number
CURRENCY_CODE(“country name”)
Returns the currency code for the specified country.
=CURRENCY_CODE("United States") returns "USD"
Country Name: The name of the country for which to get the currency code.
CURRENCY_NAME(“country name”)
Returns the currency name for the specified country.
=CURRENCY_NAME("United States") returns "Dollar".
Country Name: 'The name of the country for which to get the currency code.
CURRENCY_SYMBOL(“country name”)
Returns the currency symbol for the specified country.
=CURRENCY_SYMBOL("United States") returns "$".
Country Name: 'The name of the country for which to get the currency code.
PHONE_NUMBER("country name")
Generates a phone number based on the specified country.
=PHONE_NUMBER("United States") would return "+1-555-555-5555".
Country Name: The name of the country for which to get the phone number.
COUNTRY_CODE(“country name”)
Returns the country code for the specified country.
=COUNTRY_CODE("United States") would return"123"
Country Name: The name of the country for which to get the country code.
STATE(“country name”)
Returns a postal code for the specified country.
=STATE("United States") might would "California"
Country Name: 'The name of the country for which to get the state.
CITY("country name")
Returns a name of a city randomly based on the entered country name. Parameter: country name - The name of the country for which you want to retrieve city information
city("france") might return the value as Paris
STREET(“country name”)
Returns a street address for the specified country.
=STREET("United States") would return "123 Main St
Country Name: The name of the country for which to get the street.
ADDRESS(“country name”)
Returns a complete address for the specified country.
=ADDRESS("United States") would return "123 Main St, Springfield, IL, 62701"
Country Name: The name of the country for which to get the full address.
ZIPCODE(“country name”)
Returns a state/province for the specified country.
=ZIPCODE("United States") would return "62701".
Country Name: The name of the country for which to get the zipcode.
TIMEZONE(“country name”)
Returns the time zone for the specified country.
=TIME_ZONE("United States") would return "Central Standard Time".
Country Name: The name of the country for which to get the timezone.
FORMULA
DESCRIPTION
EXAMPLE
PARAMETERS
READ_FROM_EXCEL ([file-path], [sheet-name], [cell-reference])
Fetches data from the specified Excel file during test execution. The fetched value is used as test data for the parameter.
=ReadFromExcel("C:\\Data\\Test.xlsx", "Sheet1", "A1")
File path: The path to the Excel file.an be a local file path or a repository path.
Supports
Sheet Name: The name of the sheet in the Excel file to read from.
Supports
Cell Reference: The reference to the cell (e.g., A1, B2) whose value needs to be fetched. Supports parameterisation.
