Search
Formulas (formerly Expressions) help you to make calculations based on the answers provided by the respondents, scoring details, contact variables, and your previously configured formulas.
You can perform arithmetic and logic operations using Formulas and show the results to the respondents. Formulas let you control the survey flow when used with display logic, skip logic, or question piping. You can also see the calculated value only in your reports by keeping it as a custom variable or using it as a filter value to get value-driven reports.
This article covers the following:
Now, let’s see how to create a formula.
1. Navigate to the Global Variables section of a survey and click Formula.
2. Now, click Create a Formula to start building one.
3. Enter a name to the formula for your reference.
4. Now, you’re all set to build a formula..
Every formula needs a data source to process the information. Let’s have a look at the data sources available for your calculation in SurveySparrow.
A source is a value that can be used as an input to a function. In Formulas, you can choose from the following sources:
Type “$” or click Add source to add a data source.
A function performs an arithmetic or logic operation on data and returns an output. Based on your requirement you choose to use the following functions to create a formula.
Type ‘f’ or click Add function to start using a function.
Note: At least one function must be added to create a formula.
1. ABS
The ABS function returns the absolute value of a given number
Syntax:
ABS(value)
*Ensure that the value is a number(positive, negative, or zero).
Example:
ABS(-1) = 1
2. SUM
The SUM function returns the addition of the list of values.
Syntax:
SUM(value1, value2, value3..)
*Ensure that the values are numbers and separated by commas.
Example:
SUM(2,3,4,-1) = 8
3. MINUS
The MINUS function subtracts the second value from the first value.
Syntax:
MINUS(value1, value2)
*Ensure that the values are numbers and separated by commas.
Example:
MINUS(5,2) = 3
4. MULTIPLY
This function performs the multiplication operation between two or more numbers.
Syntax:
MULTIPLY(value1, value2)
*Ensure that each value is a number(positive, negative, or zero)
Example:
MULTIPLY(2, 3) = 6
5. DIVIDE
The DIVIDE function performs the division operation between two numbers. The result will be the quotient after calculation.
Syntax:
DIVIDE(value1, value2)
value1 should be the numerator
value2 should be the denominator
*Ensure that each value is a number(positive or negative)
Example
DIVIDE(4, 2) = 2
6. AVG
This function calculates the average(arithmetic mean) of a set of numbers.
Syntax:
AVG(value1, value2,..)
*Ensure that each value is a number(positive, negative, or zero) and separated by commas
Example:
AVG(1,2,3) = 2
7. MOD
The MOD function returns the remainder after a number is divided by a divisor.
Syntax:
MOD(value1, value2)
value1: The dividend.
value2: The divisor.
*Ensure that each value is a number(positive or negative)
Example:
MOD(10, 3) = 1
8. MAX
This function returns the largest value in a set of numbers.
Syntax:
MAX(value1, value2,..)
*Ensure that each value is a number(positive, negative, or zero) and separated by commas
Example:
MAX(2, 8, 3, 1) = 8.
9. MIN
This function returns the smallest value in a set of numbers.
Syntax:
MIN(value1, value2,..)
*Ensure that each value is a number(positive, negative, or zero) and separated by commas
Example:
MIN(2, 8, 3, 1) = 1
10. ROUND
The ROUND function rounds a number to a specified number of decimal places.
Syntax:
ROUND(value1, value2)
value1: The number you want to round.
value2: The number of decimal places to round to.
If value2 is greater than 0, the number is rounded to the specified number of decimal places.
If value2 is 0, the number is rounded to the nearest integer.
If value2 is less than 0, the number is rounded to the left of the decimal point.
*Ensure that each value is a number(positive, negative, or zero)
Example:
ROUND(3.14159, 2) = 3.14
ROUND(76.45, 0) = 76
ROUND(123.45, -1) = 120
11. ROUNDUP
The ROUNDUP function rounds a number up to a specified number of decimal places.
Syntax:
ROUNDUP(value1, value2)
value1: The number you want to round up.
value2: The number of decimal places to round up to.
If value2 is greater than 0, the number is rounded up to the specified number of decimal places.
If value2 is 0, the number is rounded up to the nearest integer.
If value2 is less than 0, the number is rounded up to the left of the decimal point.
*Ensure that each value is a number(positive, negative, or zero)
Example:
ROUNDUP(3.14159, 2) = 3.15
ROUNDUP(332.25, 0) = 333
ROUNDUP(123.45, -1) = 130
12. ROUNDDOWN
The ROUNDDOWN function rounds a number down to a specified number of decimal places.
Syntax:
ROUNDDOWN(value1, value2)
value1: The number you want to round down.
value2: The number of decimal places to round down to.
If value2 is greater than 0, the number is rounded down to the specified number of decimal places.
If value2 is 0, the number is rounded down to the nearest integer.
If value2 is less than 0, the number is rounded down to the left of the decimal point.
*Ensure that each value is a number(positive, negative, or zero)
Example:
ROUNDDOWN(3.14159, 2) = 3.14
ROUNDDOWN(332.25, 0)) = 332
ROUNDDOWN(123.45, -1) = 120
13. SQRT
The SQRT function calculates the square root of a number.
Syntax:
SQRT(value1)
*Ensure that the value is positive to avoid any miscalculations
Example:
SQRT(25) = 5
14. POWER
This function raises a number to a specified power.
Syntax:
POWER(value1, value2)
value1: The base number.
value2: The exponent to which the base number is raised.
Example:
POWER(2, 3) = 8.
1. CONCAT
The CONCAT function joins two or more text strings together and returns it as a single text.
Syntax:
CONCAT(text1, text2, ...)
text1, text2,..: The text strings you want to join.
Example:
CONCAT("Mr", ". ", "Matt", "Henry") = Mr. MattHenry
2. LEN
This function calculates and returns the number of characters in a text string.
Syntax:
LEN(text)
*Ensure the text is entered within quotes and separated by commas.
*The function counts the whitespaces as well
Example:
LEN("Refine Experience") = 17
3. LEFT
This function extracts a specified number of characters from the beginning of a text string.
Syntax:
LEFT(text, num)
text: The text string from which to extract characters.
num: The number of characters to extract from the left side of the text string.
If num is 0, the function will return empty text.
*Ensure num is a positive number.
*Ensure the text is entered within quotes
Example:
LEFT("Refine", 3) = Ref
4. RIGHT
The RIGHT function extracts a specified number of characters from the end of a text string.
Syntax:
RIGHT(text, num)
text: The text string from which to extract characters.
num: The number of characters to extract from the right side of the text string.
If num is 0, the function will return empty text.
*Ensure num is a positive number.
*Ensure the text is entered within quotes.
Example:
RIGHT("Refine", 2) = ne
5. LOWER
This function converts all characters in a text string to lowercase.
Syntax:
LOWER(text)
text: The text string you want to convert to lowercase.
Example:
LOWER("Refine Experience"): refine experience
6. UPPER
This function converts all characters in a text string to uppercase.
Syntax:
UPPER(text)
text: The text string you want to convert to uppercase.
Example:
UPPER("Refine Experience") = REFINE EXPERIENCE
1. IF
The IF function performs a logical test and returns one value if the condition is TRUE and another value if the condition is FALSE.
Syntax:
IF(logicTest, value1, value2)
logicTest: The condition to be evaluated.
value1: The value to return if the logicTest is TRUE.
value2: The value to return if the logicTest is FALSE.
*Ensure the logic test is on values of the same data type.
Example:
IF(3>10, "Above 10", "Below or equal to 10") = “Below or equal to 10”
2. SWITCH
The SWITCH function evaluates an expression against a list of values and returns a corresponding result.
Syntax:
SWITCH(expression, value1, result1, value2, result2, ..., default_result)
expression: The value to be compared against the list of values.
value1, value2, ..: The values to compare the expression against.
result1, result2, ..: The values to return if the expression matches the corresponding value.
default_result: The value to return if the expression does not match any of the values.
Example:
SWITCH(“Repeat customer”, "New customer", 1, "Old customer", 2, "Repeat customer", 3, 4) =3
3. AND
The AND function checks whether all the arguments evaluate to TRUE and returns TRUE if they do, otherwise, it returns FALSE.
Syntax:
AND(logic1, logic2, ..)
logic1, logic2, ..: The conditions to be evaluated.
*Ensure the logic test is on values of the same data type.
Example:
AND(3>10, 4<5) = FALSE
AND(3<10, 4<5) = TRUE
4. OR
The OR function checks whether at least one of the arguments evaluates to TRUE and returns TRUE if any of them do, otherwise, it returns FALSE.
Syntax:
OR(logic1, logic2, ..)
logic1, logic2, ..: The conditions to be evaluated.
*Ensure the logic test is on values of the same data type.
Example:
OR(3>10, 4<5) = TRUE
OR(3>10, 4>5) = FALSE
5. XOR
This function checks if an exclusive OR condition is met. It returns TRUE if only one of the arguments evaluates to TRUE, and FALSE otherwise.
Syntax:
XOR(logic1, logic2, ..)
logic1, logic2, ..: The conditions to be evaluated.
*Ensure the logic test is on values of the same data type.
Example:
XOR(3>10, 4<5) = TRUE
XOR(3<10, 4<5) = FALSE
6. EXACT
This function checks if two text strings are exactly the same, including case sensitivity.
Syntax:
EXACT(text1, text2)
text1: The first text string to compare.
text2: The second text string to compare.
Example:
EXACT("Refine", "refine") = FALSE
EXACT("Refine", "Refine") = TRUE
Formulas may demand the usage of a function’s output as an input to another function. That’s exactly what nested functions do. For example, let’s say you define scores and calculate a value for each personality trait using custom variables.
You can now define a function to calculate and display the personality traits.
xsadza
IF(($skill1 > MAX($skill2, $skill3)), “Leadership is your top personality trait”, “Leadership is your secondary personality trait”)
In this case, the data type of MAX($skill2, $skill3) should match the data type of $skill1 for valid comparison.
Note:
Click Test Formula to get a preview of your formula’s functioning.
Enter sample values for the formula and click Test.
Once you verify the formula, click Save.
That’s it! Your formula is ready to be used. You can use it to display as a text message, set logic, or present it on the Thank-you page.
You can also choose to use the created formula in another formula. While adding a source to the new formula, navigate to the placeholders, and select a formula present under Expressions.
Following are the supported question types for building formulas and their respective data types:
Following are the supported score variables and their respective data
Following are the supported Custom Variables’ data types
Following are the supported Contact Variables and their respective data types
Date and Time functions help you work with date-related and time-related values in formulas. These functions can return the current date or time, extract specific parts like day, month, or year, calculate the difference between two dates, or even add time to a given date.
They are useful for tracking deadlines, logging timestamps, scheduling events, and performing time-based calculations.
1. NOW
This function returns the current date and time.
Syntax:
NOW()
This function gives a STRING as the result.
Example:
NOW() => "2025/04/09 07:33"
2. TODAY
This function returns the current date
Syntax:
TODAY()
This function gives a STRING as the result.
Example:
TODAY() => "2025/04/09"
3. DATE
Returns a date by combining the given year, month, and day values..
Syntax:
DATE()
This function gives a STRING as the result.
Example:
DATE(2021, 1, 1) => "2021/01/01"
4. DAY
Returns the day of the month from a given date.
Syntax: DAY()
This function gives a NUMBER as the result.
Example:
DAY("2021/01/05") => 5
5. MONTH
Returns the month of the year from a given date.
Syntax:
MONTH()
This function gives a NUMBER as the result.
Example
MONTH("2021/01/05") => 1
6. YEAR
Returns the year from a given date.
Syntax:
YEAR()
This function gives a NUMBER as the result.
Example
YEAR("2021/01/05") => 2021
7. HOUR
Returns the hour from a given date and time.
Syntax:
HOUR()
This function gives a NUMBER as the result.
Example
HOUR("2021/01/05 12:30") => 12
8. MINUTE
Returns the minute from a given date and time.
Syntax:
MINUTE()
This function gives a NUMBER as the result.
Example
MINUTE("2021/01/05 12:30") => 30
9. DATEADD
Calculates a future or past date by adding or subtracting a specific time period to a given date.
Syntax:
DATEADD()
This function gives a STRING as the result.
Example
DATEADD("2021/01/05", 5, "day") => "January 10th 2021, 12:00 am"
10. DATEDIFF
Calculates the number of days between two dates.
Syntax:
DATEDIFF()
This function gives a NUMBER as the result.
Example
DATEDIFF("2021/01/06", "2021/01/01", "days") => 5
11. TONOW
Returns the number of days from the given date to today
Syntax:
TONOW([date])
This function gives a NUMBER as the result.
Example
TONOW("2025/04/11") => 2 days
12. FROMNOW
Shows the number of days from today to a given future date.
Syntax:
FROMNOW([date])
This function gives a NUMBER as the result.
Example
FROMNOW("2025/04/06") => 3 days
NOTE:
The following are the supported formats when entering a date as a string:
MM/DD/YYYY
MM/DD/YYYY HH:mm
YYYY/MM/DD
YYYY/MM/DD HH:mm
While using a formula within a formula, ensure you know the output data type to avoid any invalid calculations. We highly recommend testing your formula before using it.
Feel free to reach out to our community if you have any questions.
Powered By SparrowDesk