Calculations allow you to combine Excel-style functions in a powerful way to manipulate, transform, format, and return values that are not otherwise possible using questions alone.
We'll cover
In a follow-up guide (coming soon), we'll explore more advanced ways to use calculations to do things like determine someone's quiz results based on many different factors, validate input, and format product summaries, among others.
This guide should be read from top to bottom, in order.
While you are welcome to go directly to the parts that interest you, do note that later sections will assume familiarity with content in previous sections. If you are not comfortable with the concepts presented in a later section, review earlier sections to understand the fundamental concepts that come before it.
To get the most out of this guide, you should already be familiar with the content covered in our answer piping guide.
Add a calculation question to your form by choosing either of the ways below.
/calculation
.Inside the calculation's configuration, a section titled "Calculation" allows you to input your calculation.
Below the calculation input you'll find the calculation docs, allowing you to search for and look into each function that is available for use within a calculation.
Depending on your use case, you may need to use several different functions and possibly even use them multiple times on the same value to obtain the result you want.
While we use Excel-style functions, we do not support every Excel function. You are limited to the functions that are listed in the docs only.
A statement is the most fundamental part of a calculation. It, as the name implies, makes a statement about something.
That is:
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.
Functions are applied "inside out" and so we apply lower
first to "jOhN sMiTh".
LOWER("jOhN sMiTh")
The resultant value we get from calling lower
on that value is
"john smith"
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")
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
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
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:
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
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:
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 if
s.
If we run through the calculation from top to bottom, we would find that
We grab the answer for a question by its key and store it in a variable num
.
We run through a chain of nested if
s
num
is less than or equal to 10, we return the letter 'A'.num
is greater than or equal to 11 and less than or equal to 15, we return the letter 'B'.num
is greater than or equal to 16 and less than or equal to 20, we return the letter 'C'.num
is greater than or equal to 21 and less than or equal to 25, we return the letter 'D'.The calculation runs through the chain from top to bottom, returning a value as early as it possibly can:
num
was 14, it would return the letter 'B'.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.
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.
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.
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 if
s, 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.
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.
For example, suppose we had the following calculation:
loves = {{ key.array }};
isAppleLover = INCLUDES(loves, "Apples";
if(isAppleLover, true, dislikes);
There are two issues here:
includes
is not properly closed.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
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):
Line | Explanation |
---|---|
Line 1 | Set the budget variable by getting the coerced number value of key1 . |
Line 2 | Set the size variable by getting the coerced number value of key2 . |
Line 3 | Set the wantsHelp variable by checking if key3 is "Yes". |
Line 5 | Set up the assignment for the qualified variable, which is defined over the next lines. |
Line 6 | Check if budget is greater than 100,000. |
Line 7 | Check if size is between 10 and 100, inclusive. |
Line 8 | Check the boolean value of wantsHelp , implicitly. |
Line 10 | If qualified is true, return a message indicating that. Otherwise, return an error stating otherwise. |
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.
Quantity | Price Per Unit |
---|---|
1 - 10 | 15.00 |
11 - 20 | 12.50 |
21 - 49 | 10.00 |
50+ | 7.50 |
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.
Line | Explanation |
---|---|
Line 1 | Grab the quantity across all products in our product question. |
Line 3 | If qty <= 10, return 0. |
Line 4 | Otherwise, if qty <= 20, return the product of 2.50 and qty . |
Line 5 | Otherwise, if qty <= 49, return the product of 5.00 and qty . |
Line 6 | Otherwise, if qty >= 50, return the product of 7.50 and qty . |
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.
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
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
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.
Now that you've got some knowledge about calculations under your belt, you have many of the tools necessary to explore on your own, constructing calculations that satisfy your unique use case(s) and realizing the tremendous expression that comes along with them.
As always, if you need any additional help with your calculations, reach out to us via chat or email us at support@paperform.co.