Add Formula Column

Formula columns are calculated columns created from your data that enable you to derive business-specific metrics or calculations on the go. You can add them at the visual level or directly to your source database (database column).

Common use cases for formula columns include calculating profit margins, tracking performance metrics, creating planning and forecasting calculations, generating status indicators, and building custom KPIs from existing data.

1. To insert formula columns, select PowerTable > Insert Column > Formula Column.

2. Enter the column name.
3. Enter the required formula in the formula box.

• When you place the cursor in the editor, a context assistant appears automatically with Functions and References. Or press Ctrl + Space to open it.
• As you type, the suggestions are automatically narrowed down.
• Use the References tab to find and insert available columns from the current table into the formula.
• Use the Functions tab to browse and insert supported functions, such as SUM, MIN, MAX, and AVERAGE, into the formula.

4. You can specify the currency type and the number of decimal points to display.

5. If you want to add the formula column directly to the database, check the Create a database column checkbox. Then, choose the appropriate data type and attributes such as length, precision, or scale for the column in the target database.

6. Select Save.

The formula column is added to the table.

Related content#

For more information about the available formulas and functions, you can refer to Formula Syntax.

FAQ#

What does the Expected EOL Date formula column calculate?

The Expected EOL Date formula column adds the asset's expected lifetime to its purchase date to calculate the end-of-life date.

It uses the following formula:

DATEADD([Purchase Date], (365 * [Expected Lifetime In Years]))

For example, if the expected lifetime is 3 years, the formula adds 1,095 days to the Purchase Date.

Why can't I paste the formula?

Pasting a formula isn't supported. PowerTable resolves column references as you type, and pasted text doesn't preserve those references.

Type the formula manually, and use Ctrl+Space to insert each column reference instead of typing the bracketed column name as plain text.

Does the formula column get written to the database?

The formula column is written to the database only if you select Create a database column in the Add Formula Column dialog.

If you don't create a database column, the formula column still persists in the PowerTable sheet. When you reopen the sheet, the formula column remains available, and its values are recalculated based on any changes to the underlying data.