Formula (calculated) columns

Table of Contents

Video tutorial

Formula (calculated) columns in wpDataTables

From version 5.7, the calculation functions can also be used in Formula Columns.

If your dataset used for wpDataTable creation is not comprehensive – if for example, it shows only the price without VAT tax, or you need a column showing a result of calculation based on other columns’ cell values, you can use the formula (calculated) columns.

See this table: “Tax” and “Difference” columns do not exist in the dataset. They are calculated “on the fly” using the formula columns feature.

Region	Type	Net cost	Catalog cost	Tax	Difference
Zuid Holland	Terraced house	49,188	54,107	9,739.26	14,658.26
Rivers	Apartment	11,139	12,253	2,205.54	3,319.54
Västra Götalands län	Tyneside flat	35,226	38,749	6,974.82	10,497.82
Connacht	Flat	24,206	26,627	4,792.86	7,213.86
Wielkopolskie	Apartment	89,710	98,681	17,762.58	26,733.58
Wielkopolskie	Apartment	87,461	96,207	17,317.26	26,063.26
Jönköpings län	Flat	13,704	15,074	2,713.32	4,083.32
Alajuela	Apartment	76,984	84,682	15,242.76	22,940.76
Utrecht	Terraced house	41,625	45,788	8,241.84	12,404.84
Western Australia	Garden flat	68,300	75,130	13,523.40	20,353.40
Dr	Garden flat	81,117	89,229	16,061.22	24,173.22
Leinster	Terraced house	40,652	44,717	8,049.06	12,114.06
Noord Brabant	Terraced house	85,792	94,371	16,986.78	25,565.78
SI	Flat	6,539	7,193	1,294.74	1,948.74
Wie	Tyneside flat	23,464	25,810	4,645.80	6,991.80
MH	Apartment	36,653	40,318	7,257.24	10,922.24
Roxburghshire	Tyneside flat	75,914	83,505	15,030.90	22,621.90
Kay	Flat	15,222	16,744	3,013.92	4,535.92
BC	Garden flat	99,087	108,996	19,619.28	29,528.28
AB	Tyneside flat	93,474	102,821	18,507.78	27,854.78
Saarland	Apartment	19,910	21,901	3,942.18	5,933.18
São Paulo	Apartment	9,173	10,090	1,816.20	2,733.20
N.	Apartment	15,202	16,722	3,009.96	4,529.96
Östergötlands län	Apartment	30,186	33,205	5,976.90	8,995.90
Uttar Pradesh	Tyneside flat	23,237	25,561	4,600.98	6,924.98
Oklahoma	Garden flat	23,034	25,337	4,560.66	6,863.66
Languedoc-Roussillon	Apartment	12,321	13,553	2,439.54	3,671.54
AB	Tyneside flat	57,508	63,259	11,386.62	17,137.62
C	Flat	3,071	3,378	608.04	915.04
Hamburg	Apartment	47,860	52,646	9,476.28	14,262.28
UP	Garden flat	4,241	4,665	839.70	1,263.70
WP	Tyneside flat	99,853	109,838	19,770.84	29,755.84
Andalucía	Tyneside flat	83,485	91,834	16,530.12	24,879.12
Centre	Flat	87,639	96,403	17,352.54	26,116.54
VA	Garden flat	41,906	46,097	8,297.46	12,488.46
NI	Terraced house	42,484	46,732	8,411.76	12,659.76
Madrid	Garden flat	42,273	46,500	8,370.00	12,597.00
NSW	Terraced house	38,732	42,605	7,668.90	11,541.90
San José	Flat	33,341	36,675	6,601.50	9,935.50
NSW	Tyneside flat	53,757	59,133	10,643.94	16,019.94
Guanacaste	Garden flat	39,567	43,524	7,834.32	11,791.32
Zl	Apartment	22,408	24,649	4,436.82	6,677.82
Hampshire	Flat	30,921	34,013	6,122.34	9,214.34
BE	Flat	25,294	27,823	5,008.14	7,537.14
Paraná	Terraced house	19,868	21,855	3,933.90	5,920.90
Niger	Terraced house	53,943	59,337	10,680.66	16,074.66
JH	Tyneside flat	11,476	12,624	2,272.32	3,420.32
Maharastra	Flat	57,554	63,309	11,395.62	17,150.62
South Australia	Tyneside flat	52,292	57,521	10,353.78	15,582.78
CA	Terraced house	77,019	84,721	15,249.78	22,951.78
Hamburg	Terraced house	78,409	86,250	15,525.00	23,366.00
Vienna	Terraced house	84,402	92,842	16,711.56	25,151.56
Ohio	Flat	15,223	16,745	3,014.10	4,536.10
E	Garden flat	84,734	93,207	16,777.26	25,250.26
Vlaams-Brabant	Garden flat	24,510	26,961	4,852.98	7,303.98
SJ	Terraced house	25,111	27,622	4,971.96	7,482.96
OY	Garden flat	27,279	30,007	5,401.26	8,129.26
C	Garden flat	49,670	54,637	9,834.66	14,801.66
North Island	Flat	78,208	86,029	15,485.22	23,306.22
Wie	Garden flat	8,764	9,640	1,735.20	2,611.20
Noord Brabant	Flat	53,045	58,350	10,503.00	15,808.00
Zl	Tyneside flat	24,410	26,851	4,833.18	7,274.18
Ulster	Terraced house	44,111	48,522	8,733.96	13,144.96
AB	Tyneside flat	28,326	31,159	5,608.62	8,441.62
Berwickshire	Garden flat	55,491	61,040	10,987.20	16,536.20
IM	Tyneside flat	99,104	109,014	19,622.52	29,532.52
MH	Garden flat	25,850	28,435	5,118.30	7,703.30
Kahramanmaraş	Garden flat	71,781	78,959	14,212.62	21,390.62
BU	Garden flat	91,154	100,269	18,048.42	27,163.42
BA	Garden flat	53,861	59,247	10,664.46	16,050.46
Hamburg	Flat	13,646	15,011	2,701.98	4,066.98
ST	Terraced house	73,394	80,733	14,531.94	21,870.94
DS	Terraced house	80,418	88,460	15,922.80	23,964.80
PA	Flat	53,226	58,549	10,538.82	15,861.82
Kujawsko-pomorskie	Garden flat	70,998	78,098	14,057.64	21,157.64
BC	Flat	47,774	52,551	9,459.18	14,236.18
Vermont	Flat	59,454	65,399	11,771.82	17,716.82
BE	Terraced house	21,850	24,035	4,326.30	6,511.30
LU	Apartment	74,885	82,374	14,827.32	22,316.32
South Island	Terraced house	89,110	98,021	17,643.78	26,554.78
OP	Apartment	69,448	76,393	13,750.74	20,695.74
BA	Terraced house	67,929	74,722	13,449.96	20,242.96
Ontario	Garden flat	48,449	53,294	9,592.92	14,437.92
New South Wales	Tyneside flat	90,339	99,373	17,887.14	26,921.14
O'Higgins	Garden flat	57,528	63,281	11,390.58	17,143.58
AN	Terraced house	38,229	42,052	7,569.36	11,392.36
Guanacaste	Garden flat	66,160	72,776	13,099.68	19,715.68
IN	Garden flat	80,812	88,893	16,000.74	24,081.74
Jigawa	Terraced house	47,679	52,447	9,440.46	14,208.46
MB	Flat	32,147	35,362	6,365.16	9,580.16
Comunitat Valenciana	Terraced house	45,468	50,015	9,002.70	13,549.70
New South Wales	Garden flat	36,044	39,648	7,136.64	10,740.64
C	Terraced house	24,426	26,869	4,836.42	7,279.42
MI	Flat	81,871	90,058	16,210.44	24,397.44
South Island	Flat	22,516	24,768	4,458.24	6,710.24
PO	Flat	56,513	62,164	11,189.52	16,840.52
North Rhine-Westphalia	Apartment	16,617	18,279	3,290.22	4,952.22
South Australia	Tyneside flat	33,039	36,343	6,541.74	9,845.74
Cartago	Garden flat	8,731	9,604	1,728.72	2,601.72
CA	Tyneside flat	92,651	101,916	18,344.88	27,609.88
Region	Type	Net cost	Catalog cost

Please note that formulas in wpDataTables are NOT THE SAME as formulas in Excel!

Creating a formula column

Any table can have a formula column. To add one, you can click the “Add a formula column” button:

Formula constructor

After you click the “Add formula a column” button, you will see a popup formula constructor. It has a fixed name ‘formula_1’, ‘formula_2’, etc. – depending on the amount of formulas that you added.

The formula constructor popup has several main elements:

Formula. The created formula itself.
Columns that can be used in the formula. Please note that only numeric (float and integer columns) can be used in formula columns.
Supported operators. Formulas in wpDataTables support all arithmetic operators, brackets to define the calculation order, and trigonometric operators.
Preview button and preview section. Once you prepare the formula, you can click this button to see the result for the first 5 rows of your table to verify the calculation is correct.

Additionally, there is an explanation text on the top.

You can use columns (values for each cell will be inserted), or number values. Only numeric columns are allowed (non-numeric will be parsed as 0). Basic math operations and brackets are supported. Example: col1*((col2+2)-col3*sin(col4-3)).

To calculate the “Tax” column in the above example, we can simply use the catalog cost column value, multiply it by some number, and click “Save“.

Now let’s change the displayed header of the formula column. To do that, click the button for column settings.

Enter the new value in the “Displayed header” input in the first tab column setting and click apply.

If you want to change the formula for a calculation you can go to the “Data” tab and click on the “Open Formula Editor” button.
After you click on THE “Open Formula Editor” button, the formula constructor popup will appear. You can use it to adjust the formula that is used for calculation.

Formula columns can be easily deleted by clicking the “Delete” button in the column header area in the live table preview.

There are several limitations when using formula columns in wpDataTables:

One formula column cannot be used in another. This limitation can be avoided by using nested calculations.
In tables with server-side processing formulas, columns cannot be used for grouping, filtering, and sorting. Basically, formulas are calculated only for the rows that are immediately visible; Therefore, it’s not possible to “know” the values for currently invisible rows. Please note that this also applies to the “Manual” tables, as they also use server-side processing by default. Also, of course, it is not possible to edit the cells generated by formulas in the editable tables.
A formula column can not be used as the default sorting column (sort table by this column on load).

Click here to see this table in our sandbox site or here to play around with back-end configuration.

Formula (calculated) columns

Video tutorial

Formula (calculated) columns in wpDataTables

Creating a formula column

Formula constructor

Read more