Please wait...

Formula (calculated) columns

Video versionA video overview of formula (calculated) columns in wpDataTables

Video is currently being re-filmed and is coming soon. Stay tuned!

wpDataTable Formula (calculated) columns

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

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

Formula Columns Demo

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

Add formula column

Any table can have a formula column. To add one, you can click the “Add formula column” button in the columns settings section of the table configuration page:

You will see a new column that appears in your column set. It has a fixed name ‘formula_1’, ‘formula_2’, etc. – depending on the amount of formulas that you added, slightly different color of header, and a delete “X” button in the top right corner:

You can define a name for formula columns, and set other column properties as for “usual” columns derived from the initial dataset. The most important action now is to add the formula to be used in calculation itself. It can be entered in the “formula for calculation” field, it’s the easiest to click the “Formula constructor” button to open the helper dialog:

Formula constructor

The formula constructor popup has several main elements:

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

Additionally there is an explanation text on the top.

You can nest any static numbers, numeric columns and as many sub-calculations in brackets as you need, e.g. something like: (0.75*col1)*((col2*2.35)-col3*sin(col4-3.12)) is valid.

To calculate the “Tax” column in the above example we used this setup:

There is just a couple number of limitations on using formula columns in wpDataTables:

  1. One formula column cannot be used in another. But this limitation can be avoided by using nested calculations.
  2. In tables with server-side processing formula columns cannot be used for grouping, filtering, and sorting. Basically, formulas are calculated only for the rows that are visible at the moment, so it’s not possible to “know” the values for currently invisible rows. Please note that it also applies to the tables created by Table Constructor 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.
  3. Adding a sum in sum row for formula columns is not yet supported.

Never miss new features!

Join 2000+ newsletter subscribers

Never miss notifications about new cool features, promotions, giveaways or freebies - subscribe to our newsletter! We send about one mail per month, and do our best to keep our announcements interesting.

We never spam or disclose your address to anyone.