Formulas (formerly Expressions)

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:

• How to create a formula
• Data sources for a function
• What are functions
• Math functions and their syntaxes
• Text functions and their syntaxes
• Logic functions and their syntaxes
• Nested functions
• Date and Time functions 

Now, let’s see how to create a formula.

Creating 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.

Sources

A source is a value that can be used as an input to a function. In Formulas, you can choose from the following sources:

• Answer to: It pulls the respondent’s answer to a question 
• Score of: It fetches the score assigned to an option based on the respondent’s choice
• Placeholders: It gets the value stored in contact properties or custom variables

Type “$” or click Add source to add a data source.

Functions

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.

Math functions

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.

Text functions

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

Logic functions

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

Nested functions

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: 

• Ensure the input that you feed into a function is a valid argument for the same. 
• Use “()” to enclose an expression when passed as an input to a function
• You can choose to use +, -, *, / to perform arithmetic operations

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.

Tip

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.

Note

Following are the supported question types for building formulas and their respective data types:

• • Number Input -> Number
  • Rating -> Number
  • Opinion Scale -> Number
  • Text Input -> String
  • Email Input -> String
  • Phone Number -> String
  • Yes/No -> Boolean
  • Silder -> Number
  • URL Input -> String
  • NPS Score -> Number
  • CES Score -> Number
  • CSAT Score -> Numbertypes

Following are the supported score variables and their respective data

• Multiple Choice Question -> Number
• Picture Choice Question -> Number
• Dropdown -> Number

Following are the supported Custom Variables’ data types

• • String Type -> String
  • Number Type-> Number
  • Date (not supported)

Following are the supported Contact Variables and their respective data types

• • Single-line text -> String
  • Multi-line text -> String
  • Email -> String
  • URL -> String
  • Number -> Number

Date and Time functions

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.