---
title: "Calculated columns vs aggregates explained | Oplema"
description: "Learn the difference between a calculated column and an aggregate like SUM, how nulls behave in arithmetic and how to write both in SQL and Excel."
url: "https://oplema.com/learn/data-preparation/formulas"
updated: "2026-11-04"
---

Lesson 11 of 15 · Lab

# Calculated columns vs aggregates explained

A calculated column applies a formula to each row and keeps the row count, like a formula filled down a column in Excel. An aggregate such as SUM or AVG reduces many rows to one value. Calculate at row level first, then aggregate.

Quantity 2 at price 60 and quantity 4 at price 20 give line totals of 120 and 80, still two rows. Their sum, 200, is an aggregate.

In this lab, [fictional Vandelay Coffee Supply](https://oplema.com/learn/data-preparation/what-is-data-prep)'s 13 invoices have their customers, and North leads by €110 at list value. Starhopper, a North customer, gets a 10% rebate, and nobody else has one.

Three of the 13 invoices, before any rebate
| customer | region | amount\_eur | rebate\_pct |
| --- | --- | --- | --- |
| Starhopper | North | 1,240 | 10 |
| Wonka | South | 860 | 0 |
| Dunder Mifflin | North | 640 | 0 |

Starhopper's invoices come to €4,320 at list. After the rebate, who leads?

South. Ten percent of Starhopper's list value is €432, far more than North's €110 lead.

## Net a rebate out of list prices

1. **Formula** is selected in this live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep. `net_eur`, the net value in euros, is just a copy of `amount_eur` for now.
2. Select **Summarize**, the last block. It reads North 6,010, South 5,900: the gross picture.
3. Select **Formula**. Select the **Expression** field, select all its text with Ctrl+A or Cmd+A, and type `amount_eur * (1 - rebate_pct / 100)`. Then select the **Formula** block. Red text under Expression means a name is mistyped.
4. Read the preview. Starhopper's first invoice drops from 1,240 to 1,116, and Wonka's doesn't move. Scroll to Wonka's NULL price: `net_eur` stays NULL.
5. Select **Summarize**. One region has changed, and it's enough.

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

The regions you should see

North €5,578, South €5,900. Total €11,478, and South leads by €322.

## A calculated column can change the conclusion

This is the number the course has been working toward: net booked invoice value, €11,478, with South ahead. From here on, call it net value.

It also explains both reports. Sydney Swell in Finance has the right figure for North, but her South is €4,590 because her lookup lost Initech's €3,000 and counted Dunder Mifflin's €1,690 in both regions. Her total, €10,168, is close and names the wrong region. Don Tremendous in Sales is furthest off and names the right one by accident, since his old territory map still puts Dunder Mifflin in South.

A calculated column is a decision, so its definition has to travel with it. See the [Formula reference](https://oplema.com/docs/strata/prep/formula).

## Common formula mistakes

- **Expecting a null to act like zero.** In SQL and Strata, arithmetic on an unknown gives an unknown, so Wonka's unpriced invoice stays NULL. Excel differs, treating an empty cell as 0, so the same formula shows a confident 0.
- **Calculating after you aggregate.** A rebate applied to a regional total can't see which customer earned it.
- **Dividing whole numbers.** In PostgreSQL and SQL Server, `5 / 100` is 0, so write `100.0`.
- **Typing a rate into the formula.** Keep it in a column or a table, where someone can see and change it.

## The same formula in SQL and Excel

```
SELECT  invoice_id,
       amount_eur  *  ( 1  -  rebate_pct  /  100 . 0 )  AS  net_eur
FROM  invoices
```

In Excel, type `=C2*(1-D2/100)` and fill it down. Then [group the result](https://oplema.com/learn/data-preparation/summarize) with `SUM` in SQL, or with a PivotTable in Excel.
