Tables

Tables are useful when you have lots of data that you need to refer to in your formulas. You can paste them straight from your Excel/Google Docs etc spreadsheet.

Note: Tables should be at most 3-400 rows. If you need really large tables contact us.

How Tables Work

Let's say you have this custom pricing, based on the quantity of items ordered:

Quantity	Price
1-5	$1,000.00
6-20	$900
21-100	$750.99
101-500	$650.99

You would need a Number question (or Slider, Incrementer) called Quantity, and a Formula called Price

When editing the Formula you will see the "Tables" tab above the formula content. In there you can create a table, we can call it Prices (the title is just for your benefit, you will refer to the table as T1 inside formulas)

In the table we will add these columns:

Quantity	Price
5	1000.00
20	900
100	750.99
500	650.99

You'll notice 3 things:
In the Quantity columns we only kept the upper limit, since that is the point where the price changes.
In the Price field we removed the "$" sign because we need the clean number
Also in the Price field we removed the comma "," separator because only decimal dots are allowed
(You can do these operations in your spreadsheet - Excel, etc with Find and Replace)

Now that you have the table set up, you can go to the Formula field and type this in the formula content:

FINDIFS(T1!B, T1!A, ">= @Quantity")

What this means is find the value from column B of table T1 where column A of table T1 is greather than or equal to the value of @Quantity.

At this point you can update your pricing just by going in and editing the Prices table.

As you can see, you use a table column with the expression T1!A
T1 is the table, as you add more tables it will be T2, T3, etc
A,B,C is the column. You can also write T1!1, T1!2, T1!3, etc to achieve the same result.

Advanced Tip: You can use a question value in the Table notation:
Let's say you have this table (truncated) for average baby weights:

Male Weight (kg)	Female Weight (kg)	Age (months)
4.5	4.2	1
5.6	5.1	2

And a Gender radio buttons question with the options: Male (value = 1) and Female (value = 2).
In your formulas you can use: T1!@Gender which will resolve to T1!1 if the user picks Male and T1!2 if the user picks Female

Advanced Tip 2: If for some reason your table had the columns in this order:

Age (months)	Male Weight (kg)	Female Weight (kg)
1	4.5	4.2
2	5.6	5.1

And the Gender radio buttons question had the same options: Male (value = 1) and Female (value = 2)
You can write the table columns like this:

T1!{@Gender+1}

This way {@Gender+1} will evaluate first to 2 or 3 (depending on the @Gender value), then your table column expression will be T1!2 or T1!3

Learn more about the functions you can use in tables:

If you can't find a function you're looking for let us know