---
title: "Add a calculated column in Strata | Oplema"
description: "Learn to add a calculated column with Formula in Strata: take VAT off a week of bike hires, check the net total against gross × 0.8 and handle free hires."
url: "https://oplema.com/learn/export-to-report/add-a-calculated-column"
updated: "2026-11-04"
---

Lesson 7 of 10 · Lab

# Add a calculated column to a table

Formula adds a column calculated from each row's values and keeps the number of rows. Write the rule once and it applies to every row, including the ones you never look at.

Till prices include VAT. Marit reports her figures without it, so VAT has to come off every amount before anything is added up.

Where [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow) trades, a fifth of every till price is VAT, so net is the amount × 0.8. This live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, starts with the 93 finished hires your filter kept in the last lesson.

Two of the week's hires were free staff rides with no amount. What should their net be?

Empty, like their amount. Formula keeps NULL as NULL, which is right: no money came in, and a sum skips it.

## Take VAT off every hire

1. **Formula** is selected. It writes `net_eur`, the net amount in euros, as a plain copy of `amount_eur` for now.
2. Select **Summarize**. `gross` reads 4093.5 and `net` the same, 4093.50. `priced` counts 91 amounts.
3. Select **Formula**. Select the **Expression** field, select all its text with Ctrl+A or Cmd+A and type `amount_eur * 0.8`. Then select the **Formula** block. Its **Output type**, DECIMAL with 2 decimal places, keeps the money in exact cents when it's added up. If red text appears under Expression, a column name is mistyped.
4. Read the preview. The first row, KB-10213, is a free staff hire, so its `net_eur` stays NULL. The next, KB-10214, goes from 38.5 to 30.80.
5. Select **Summarize**. `net` reads 3274.80, and `priced` is still 91.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What you should see

`net` €3,274.80, which is the €4,093.50 gross × 0.8. `hires` stays 93, because a calculated column never adds or removes rows, and the two free hires, KB-10213 and KB-10254, stay NULL.

## One rule, every row

Checking against gross × 0.8 is worth doing every time, because a formula that runs isn't necessarily right. Type `amount_eur * 1.2` and every row changes without a warning.

Calculate on rows first and total afterward. Here both orders agree, because every hire has the same rate. They stop agreeing once a rule differs from row to row, such as a discount for some customers. Next, total the week by bike code.

See the [Formula reference](https://oplema.com/docs/strata/prep/formula). For later: [Calculated columns](https://oplema.com/learn/data-preparation/formulas) compares row-level formulas with aggregates.

## Common formula mistakes

- **Dividing by 1.2.** That suits VAT charged at a fifth on top of the net price. Here a fifth of the till price itself is VAT, which is × 0.8, the same as ÷ 1.25.
- **Filling NULL with 0 to make the column look complete.** The total stays the same, but `priced` would count 93 and hide the two free rides.
- **Overwriting the original column.** Keep `amount_eur` and add `net_eur`, so anyone can check one against the other.

## Calculated columns in SQL and Excel

```
SELECT  * ,  CAST (amount_eur  *  0 . 8  AS  DECIMAL ( 18 ,  2 ))  AS  net_eur
FROM  week ;

SELECT  sum (amount_eur  *  0 . 8 )  AS  net,
       sum (amount_eur)  *  0 . 8  AS  check_net
FROM  week ;
```

A calculated column in SQL is an expression in SELECT. In Excel, `=F2*0.8` filled down a column does the same, but an empty `F2` gives 0 rather than empty, one more reason to check the count as well as the sum.
