Calculations Guide

A statement is the most fundamental part of a calculation. It, as the name implies, makes a statement about something.

That is:

• Assign some value to a variable.
• Call this function.
• Return this value.

All of the following lines are individual statements:

1;
greeting = "hello";
result = IF(employees < 10, ERROR("You must have 10+ employees."), "OK");

Some are more complicated than others and some are as simple as possible (e.g. 1;), but they're all statements.

Don't worry if things look a little complicated right now. We'll cover items like variables and functions next!

Functions are basically shortcuts for one or more operations to be applied to a value.

For example, suppose you have the full name of a person and want to make sure that the first letter of their first and last name are capitalized.

You could use proper to achieve this simply and easily:

PROPER("john smith");

If you wanted to ensure that only the first letter of the first and last name were capitalized, you could use composition of functions, which is really just a way of saying "use a function within a function" to manipulate the value multiple times.

More concretely, you could force the name to be all lowercase since that's what you want most of the characters to be and then use proper to change the first letter in the first and last name.

PROPER(LOWER("jOhN sMiTh"));

Let's make sure it's clear what's going on here.

1. Functions are applied "inside out" and so we apply lower first to "jOhN sMiTh".

   LOWER("jOhN sMiTh")
   

2. The resultant value we get from calling lower on that value is

   "john smith"
   

3. That resultant value from the innermost function is then supplied as the value to use for the outermost function, proper.

   So, once proper gets its turn, it effectively sees

   PROPER("john smith")
   

4. Since there are no more functions to call, our final value is the result of the previous three steps:

   "John Smith"
   

The combination, or composition, of functions allows us to enhance how expressive we may be, allowing more complex transformation of data thus enabling greater control over your data from respondents.

Functions take arguments, or parameters, as the values that they need to operate on. They follow a specific pattern, or signature, that may differ by function.

The function's signature indicates

• the arguments the function may have set
• the order in which those arguments are required

For example, in the above example of

PROPER("john smith")

the argument was the string "john smith".

proper only takes a single argument: the text for which you want to capitalize the first letter of each word.

On the other hand, the mathematical function round takes two arguments: a number and its precision.

x = ROUND(1.05);
y = ROUND(1.05, 1);

The precision is optional, so the function technically takes 1 or 2 arguments, depending on what you want.

For the above example, we have

• x = 1
• y = 1.1

Because we didn't supply a precision for x, it used the function's default precision of 0 decimal places to round to, which rounded down to 1.

The calculation docs, located inside a calculation question's configuration, detail helpful information about how to use each function, including:

• its arguments
• the position of each argument
• each argument's type
• whether a given argument is required
• a brief description of each argument's purpose
• an example of the function's usage

While we support numerous Excel-style functions, we do not support all functions you may be familiar with from Excel or similar software. The docs serve as the source of truth. If a function isn't listed, it isn't supported.

While we might be able to write a calculation using only a single statement, that would be severely limiting for more complex use cases and putting everything in a single line would make it less readable and understandable.

For this reason, we can assign values to a variable. A variable is just a handy way of referring to some value, where that value could be as simple as a single integer or as complicated as the result of multiple functions called on a value.

email = {{ email_key }};

Above, we're setting email equal to the answer of a question where we ask for an email address on the form. While this is a simple case of using a variable, it lets us avoid typing out the piping over and over, which can be error-prone if we have to do it repeatedly in a calculation.

Now, we can just use the variable to refer to that value:

"It looks like your email is: " || email;

which might result in a returned string such as

It looks like your email is: danny@paperform.co

Variables can be reassigned or combined and assigned to another variable to allow additional possibilities.

first_name = "Diony";
first_name = "Dean";
last_name = "McPherson";
name = first_name || " " || last_name;

CONCATENATE("Hi, ", name);

The above calculation would return the following string:

Hi, Dean McPherson

While the first_name was initially "Diony" we reassigned that value to "Dean" before combining it with last_name to assign a value to name.

Spacing

What would happen if we instead had used slightly different return values in the previous examples?

"It looks like your email is:" || email;
CONCATENATE("Hi,", name);

Calculations are literal, so we'd be missing some spacing:

It looks like your email is:danny@paperform.co
Hi,Dean McPherson

If you want spacing, the value must already contain it or you must explicitly include spacing when concatenating (combining) input.

While variables help us out tremendously in constructing calculations that can reuse values and help us avoid tedium, we're still left with a straightforward flow. If we want to set a variable to one value or another, conditionally, we're going to need some specific help.

That's where conditional statements come in. We can use if or switch to go down different paths based on some input. As usual, this is better illustrated with an example.

Suppose you want the respondent to choose a single program in a list of programs you offer, presented in a multiple-choice question. However, you also want to assign a value to the program they choose since you need that for some automation you're running after the form is submitted.

To accomplish this, you'd want to use a calculation that

• pulls in the program selected
• assigns values to each program
• returns the corresponding value for the program the respondent selected

This is a great case for a switch, where the initial argument is checked for matches across several paths, or branches, returning the value for that path if a match is found.

SWITCH({{ multiple_choice_key }},
"Program A", 5,
"Program B", 10,
"Program C", 15,
0
);

The returned value is determined by calculation following these steps internally:

1. Grab the answer from the question referenced by the key.
2. Go down the list of possible matches, from top to bottom.
3. If a match is found, return the corresponding value and skip the remaining possible matches.
4. If no match is found, return the default (set to 0 above).

For example, if the respondent chose "Program B" then the calculation would return 10.

Alternatively, you could use an if for the above. Any switch can be turned into an if since each branch of the switch is effectively if this, then that, which is exactly how an if works.

However, that's not true of the opposite.

A switch can only branch off based on a single initial condition. An if, on the other hand, can combine multiple conditions or use completely different conditions for each branch.

Let's see a more complex case where only an if shines.

Suppose you want to check if a number is within a given range, returning a letter based on it being within a specific range or a -1 if none of the specified ranges match.

num = {{ key }};

IF(num <= 10, "A",
IF(num >= 11 and num <= 15, "B",
IF(num >= 16 and num <= 20, "C",
IF(num >= 21 and num <= 25, "D", -1))));

The signature for an if is

IF(condition, value1, value2)

where value1 is returned if condition is true; otherwise, value2 is returned.

There's nothing that says we can't return yet another if inside an if. That's we're doing above, creating nested ifs.

If we run through the calculation from top to bottom, we would find that

1. We grab the answer for a question by its key and store it in a variable num.

2. We run through a chain of nested ifs

   • If num is less than or equal to 10, we return the letter 'A'.
   • Otherwise, if num is greater than or equal to 11 and less than or equal to 15, we return the letter 'B'.
   • Otherwise, if num is greater than or equal to 16 and less than or equal to 20, we return the letter 'C'.
   • Otherwise, if num is greater than or equal to 21 and less than or equal to 25, we return the letter 'D'.
   • Otherwise, we return -1.

The calculation runs through the chain from top to bottom, returning a value as early as it possibly can:

• If num was 14, it would return the letter 'B'.
• If num was instead 55, it would return -1.

map, filter, reduce

These also work as conditional constructs to dynamically transform, remove, or reduce values within an array but are beyond the scope of this introductory guide. They will be covered in our advanced calculations guide.

Understanding when values get returned is crucial to creating calculations that behave and operate in the way that you intend and expect.

When does a value get returned

The latest statement in a calculation that is not assigned to a variable is returned.

Notice that that's a singular statement. You may not return multiple times. If you have multiple statements that would qualify as a return statement in isolation, only the very last one will be returned from the calculation.

For example:

x = 1;
y = 2;

y;
x;

In the above, the calculation returns 1 because x is the last return statement. While x is defined before y in the first line, that isn't a return statement.

If we recall, only the latest statement in a calculation that is not assigned to a variable is returned. Here, we attempt to return y but then attempt to return x below that. Since x is the latest statement, its value is what gets returned.

Wait, isn't x a variable? Why can I return it?

A variable assignment requires a single = in order to assign some value to a variable. Since the last line is simply the variable itself—x—all we're doing is saying, "Whatever value x holds, return it." No assignment is being done at all.

When dealing with values in a calculation or with values that are returned from a calculation, you should be aware of the type of that data.

A type describes the characteristic of some data:

• string — A string is just text, like you're reading now.
• number — A number is like a string, but behaves somewhat differently.
• boolean — A boolean can only be the values true or false.
• array — An array is a list of items, where each item might be a string, number, or boolean.

Separate types may exhibit different behavior for some functions and not knowing how they interact can result in an unexpected outcome.

Number-to-string coercion

If you try to treat a number as a string, we'll attempt to coerce, or force, the value to a string:

x = 1.43;

"The value of x is " || x || ".";

This works just fine, where x is basically turned into a string in order to be concatenated with other strings. The end result is a single string.

However, if we try to use the function left, which grabs the specified number of characters from a string, beginning from the left-hand side, we'll find issues:

x = 1.43;

LEFT(x, 1);

which returns

#VALUE!

That error is indicating that there's an issue with the value. It tries to convert the number to a string, but it isn't going how we want.

Numbers aren't always numbers

Another common error is assuming some value you have from a question is a number when it's really a string (even if it looks like a number).

If you try to perform a mathematical operation with the value and it's not truly of type number, you'll get a NaN (Not a Number) error.

These errors can be quite confusing to determine the source of, so we recommend explicitly attempting to convert anything that you're not sure is a number into a number:

NUMBERVALUE({{ key }})

Wrapping our value in the numbervalue function ensures that we at least get a number to work with.

If the value can't be converted to a number (e.g. the value is a word), it'll return 0. This provides the assurance that any mathematical operations we do with this wrapped value will always end up being a real number and avoids the error from before.

There will come a time when you encounter an error. It happens to everyone, us included. We'll cover several of the errors and some tips for working through any others.

• You can't end with a variable assignment.

  This is, by far, the most common error. It just means that you haven't actually returned anything. Recall from earlier in Return values that a return statement is one that is not assigned to a variable.

  So, if you have something like

  greeting = "hello, world";
  

  you only need to either remove the assignment, returning the string directly, or return the variable itself:

  greeting = "hello, world";
  greeting;
  

• The formula looks incomplete.

  The formula is missing necessary syntax, such as a parenthesis. This is common when using nested ifs, where there are an unequal number of opening and closing parentheses.

• "{variable}" doesn't look like it's supposed to be there, or you haven't finished typing...

  A variable is being referenced that hasn't been defined in the current calculation. Check your spelling and ensure the variable has been defined before where you're attempting to use it.

• Not an array

  An argument that must be an array is not an array. Ensure that the value is actually an array or explicitly convert it to one using array.

• #VALUE!

  Like we saw in Types, there's a mismatched type somewhere. That is, a number was provided where a string should go or something similar.

  Check that your values are of the proper type or that you're explicitly converting them to the proper type before using them.

• NaN

  Also like we saw in Types, a number is being used somewhere that isn't actually a number.

  Ensure all your numbers really are numbers or explicitly convert them to numbers before using them in any mathematical operations.

Finding the root cause

For the above errors or any others you might encounter, there are some steps you can take to determine what might be causing the problem specifically.

1. Save the form and refresh the editor. This can clear up any possible issues with the parser.
2. Review the calculation for any obvious errors (mismatched parentheses, misspellings, missing arguments).
3. Create a duplicate of your calculation and test segments of it by commenting out or removing parts that are unnecessary to test if a particular segment is working.
4. Repeat Step 3 as necessary until you find the issue.

For example, suppose we had the following calculation:

loves = {{ key.array }};
isAppleLover = INCLUDES(loves, "Apples";

if(isAppleLover, true, dislikes);

There are two issues here:

1. Our includes is not properly closed.
2. We're using dislikes but we've never defined it.

How would you find that out on your own?

Well, if you were trying that calculation on your own form, you'd see that the live preview returns an error that gives us a nice hint:

The formula looks incomplete.

You could either:

• Reduce the calculation so the remaining part only has what you absolutely need for it to work.

  loves = {{ key.array }};
  

  No issues so far.

  loves = {{ key.array }};
  isAppleLover = INCLUDES(loves, "Apples";
  

  Ah, there's our error. Once we added that last line, something went wrong. It's not always in the line you just added due to the way things interact, but it typically is.

  Here, it's because we forgot to include the closing parenthesis on our includes.

  loves = {{ key.array }};
  isAppleLover = INCLUDES(loves, "Apples");
  

  Great, now we just an error about ending with a variable assignment. That's OK because this isn't our complete calculation. Our return statement was the line we removed just to test what wasn't working.

• Review the calculation and see what's missing

  This works similarly to the above, but you'd visually inspect the calculation to find that missing parenthesis instead.

With that fixed, we now get the other hint:

"dislikes" doesn't look like it's supposed to be there, or you haven't finished typing...

Easy enough, we know from our list of errors covered above that this is because we haven't defined that variable. We could remove it and use a different value or define it. It's your call. Once that's fixed, the calculation is good to go!

Debugging your calculation is all about

• understanding your calculation's flow
• recognizing where you're making assumptions about values and types
• ensuring no spelling or syntax (e.g. parentheses) errors exist
• reviewing any errors and the hints provided by them

For the most part, you can format your calculation as you see fit. You can cram everything together as tightly as possible or spread statements out loosely.

See Gotchas for an example where this isn't the case.

However, for your own sake and perhaps the sake of anyone who might be assisting you, proper spacing and line breaks can make a difficult, nearly incomprehensible calculation easier to follow and reason about.

Which of the following calculations would you rather work with when reasoning through an already complicated process?

event={{key}};quantity=numbervalue({{key}});isAttending={{ key }}=="Yes, I'm attending.";
base=10;addons=if(event=="A",9.99,if(event=="B",14.99,if(event=="C",19.99,if(event=="D",49.99,0))));
subtotal=(base+addons)*quantity*1.25/.99*POW(2.573,4);subtotal;

or

event = {{ key }};
quantity = numbervalue({{ key }});
isAttending = {{ key }} == "Yes, I'm attending.";

base = 10;

addons = 
if(event == "A", 9.99,
if(event == "B", 14.99,
if(event == "C", 19.99,
if(event == "D", 49.99, 0))));

subtotal = (base + addons) * quantity * 1.25 / 0.99 * POW(2.573, 4);
subtotal;

Certainly, it's up to personal preference, but we much prefer the second calculation.

An incredibly common use case is to display an error if certain conditions are not met, such as when a lead doesn't qualify or a service is not available at a particular time.

In these instances, you don't want data you can't use to be submitted. Instead, you can display an error that can guide the respondent in correcting their invalid data or instruct them that they cannot continue.

A visible error on a form will prevent the respondent from progressing to another page and/or submitting the form.

To achieve this, we might write a calculation similar to

1  budget = NUMBERVALUE({{ key1 }});
2  size = NUMBERVALUE({{ key2 }});
3  wantsHelp = {{ key3 }} == "Yes";
4  
5  qualified =
6  budget > 10000 and
7  (size >= 10 and size <= 100) and
8  wantsHelp;
9  
10 IF(qualified, "You're qualified.", ERROR("Sorry, it looks like you're not qualified."));

Line numbers added for clarity. These are not present in actual calculations.

Let's cover what we did above, line by line (skipping blank lines):

To make sure this sort of calculation has any effect, you need to toggle "Hide this question" off in the calculation's configuration. Invisible errors don't do anything since they can't be seen and thus cannot be acted upon.

Suppose you want to sell shirts, where each shirt costs 15 USD per shirt. However, you want to provide a discount based on the quantity of shirts ordered across the entire product question, providing an incentive for customers to place larger orders.

Sample pricing scheme

To adhere to this pricing scheme, we might use a calculation similar to

1  qty = {{ key.quantity }};
2  
3  IF(qty <= 10, 0,
4  IF(qty <= 20, 2.50 * qty,
5  IF(qty <= 49, 5.00 * qty,
6  IF(qty >= 50, 7.50 * qty))));

Let's cover each line in order.

How does this actually work?

The return value is the entire chain of calls to if, returning as early as it possibly can based on the logic we've set up. That is, if the quantity is 5, it'll return 0 because 5 <= 10, satisfying the part in Line 3.

However, if the quantity were 43, it would return the part from Line 5 because 43 is not <= 10 and is not <= 20 but is <= 49.

To allow this calculation to have an effect on our form's total, we would want to create a custom pricing rule, subtracting our calculation's answer from the form's total.

How does this modify the form's total in the way that we want?

Since our products have a cost of 15 USD, charging that amount per shirt is already handled automatically by the product question itself. If we want to discount that amount based on the quantity, we need our calculation to modify the price on our behalf, applied via a custom pricing rule.

Let's take a quick example to illustrate how this works.

Suppose we order 23 shirts.

1. Without our calculation, the total would be 23 * 15 = 345.
2. Based on our previous calculation, our calculation would then return 23 * 5 = 115.
3. When using a custom pricing rule to subtract our calculation's value from the form's total, we would then have 345 - 115 = 230.
4. To get the new price per unit, we would notice that 230 / 23 = 10 USD per shirt, which is exactly how much each shirt should cost for quantities between 21 and 49, inclusive.

Why do we return and subtract 0 for quantities between 1 and 10, inclusive?

According to our pricing scheme, quantities in that range receive no discount and are charged at the regular price per unit of 15 USD. As such, our calculation returns 0 so that we don't provide any discount.

Because calculations can affect the return value of other calculations, scoring, or the form's total, referencing any of those in a calculation has the potential to result in an infinite loop.

For example, a calculation A may reference a calculation B which also references A. They would continually reference each other, updating the return value of each endlessly and may cause the tab or browser to crash. As such, this is not permitted and attempting to reference these items will fail.

The live preview is based on data from the most recent submission. This means that the preview may produce unexpected results based on that data (e.g. a question you're piping in wasn't answered or had a different value than you expected).

To help confirm the results of your calculation, you can

• Toggle "Hide this question" off in the calculation's configuration and confirm the result real-time as you fill out the form.
• Submit the form and check submission data to see the answer the calculation returned.

Calculations are run on the form, so you can see and act on them, but are also then run on the server when a submission is attempted. In some instances, the values between the form and server are slightly different. The answer from the server is considered the authoritative answer and will always take precedence.

Notable examples are

• Date and time: The server uses UTC at all times.
• Products: The server returns a more verbose answer, including more details about the selected product.

Almost all functions work as expected whether you use something like proper or PROPER. If the function name matches a non-function operator (e.g. and versus AND()), you need to use the proper casing.

That is, something like

and(x, y)

will not work correctly since the calculation will attempt to use the logical operator and instead, which expects an argument on each side instead of being called as a function.

In instances where a non-function operator does not match the name of a function, casing is irrelevant and you may use whichever you prefer.

Typically, you can format your calculation as you please. However, if you're matching against specific answers or text, you'll want to make sure that you match it exactly.

That is, all of the following are different:

1 "Yes, I agree."
2 "Yes, I agree"
3 "Yes,  I agree."

Let's break down the differences between each one:

• 1 — This is a complete, well-formulated sentence.
• 2 — This one is missing a period at the end.
• 3 — This one has two spaces before the 'I'.

They all look pretty similar, but are all considered completely distinct if you're matching against them in a calculation, so make sure you pay attention to the exact text, including spacing, of any answers or other text that you're matching against.