Formulas and Calculations
Excel's math and logic formulas map to Python arithmetic operators and VisiData aggregators. This page covers every common accounting formula with its VisiData equivalent.
Learning Focus
The key insight: column-level aggregations (SUM, AVERAGE, COUNT) use VisiData aggregators (+ key), not cell expressions. Row-level calculations (A+B, A*B) use the = expression key. These are two different tools.
Arithmetic Formulas
| Excel / Google Sheets | VisiData Python | Description |
|---|---|---|
=A2+B2 | = col_a + col_b | Add two columns |
=A2-B2 | = col_a - col_b | Subtract |
=A2*B2 | = col_a * col_b | Multiply |
=A2/B2 | = col_a / col_b | Divide (use guard for zero) |
=A2^2 | = value ** 2 | Power / exponent |
=ROUND(A2, 2) | = round(value, 2) | Round to 2 decimal places |
=ABS(A2) | = abs(value) | Absolute value |
=INT(A2) | = int(value) | Integer part (floor) |
=MOD(A2, 12) | = value % 12 | Modulo / remainder |
=SQRT(A2) | = value ** 0.5 | Square root |
=POWER(A2, 3) | = value ** 3 | Raise to power |
=TRUNC(A2, 2) | = int(value * 100) / 100 | Truncate decimals |
=CEILING(A2, 5) | = (value // 5 + 1) * 5 | Round up to nearest 5 |
=FLOOR(A2, 5) | = (value // 5) * 5 | Round down to nearest 5 |
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Create margin column: (price - cost) / price
=
# Enter: (price - cost) / price if price != 0 else 0
# Name: margin_pct
# Cast amount to float first
# Move to 'amount' column → %
# Round amount to 2 decimals
=
# Enter: round(amount, 2)
# Name: amount_rounded
Aggregator Functions (Column-Level)
These replace Excel's SUM, AVERAGE, COUNT functions on entire columns. Set aggregators with +:
| Excel / Google Sheets | VisiData Aggregator | Description |
|---|---|---|
=SUM(A:A) | + sum | Sum entire column |
=AVERAGE(A:A) | + mean | Arithmetic mean |
=COUNT(A:A) | + count | Count non-null values |
=COUNTA(A:A) | Status bar row count | Count all non-empty |
=MAX(A:A) | + max | Maximum value |
=MIN(A:A) | + min | Minimum value |
=MEDIAN(A:A) | + median | Median value |
=STDEV(A:A) | + stdev | Standard deviation |
=PERCENTILE(A:A, 25) | + q4 | Quartile grouping |
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Set sum aggregator on 'amount'
# Move to 'amount' column → press % (cast to float)
+ sum
# Now open frequency table by customer
# Move to 'customer' column → Shift+F
# → shows: customer, count, percent, amount (sum per customer)
info
z+ shows the aggregate result immediately in the status bar without opening a new sheet — useful for a quick total check.
# Quick total of amount column
+ sum
z+
# Status bar shows: sum = 45,230.50
Conditional Functions
IF Logic
| Excel | VisiData Python | Description |
|---|---|---|
=IF(A2>100, "High", "Low") | = 'high' if amount > 100 else 'low' | Simple if-else |
=IF(A2="", "N/A", A2) | = value or 'N/A' | Fill empty with default |
=IFERROR(A2/B2, 0) | = (a / b) if b != 0 else 0 | Error-safe division |
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# Create order size category
=
# Enter: 'large' if amount > 500 else 'medium' if amount > 100 else 'small'
# Name: order_size
IFS (Multiple Conditions)
Excel's IFS maps to Python's chained ternary:
# Excel: =IFS(A2>100, "High", A2>50, "Med", TRUE, "Low")
# VisiData:
= 'high' if amount > 100 else 'medium' if amount > 50 else 'low'
SWITCH (Dictionary Lookup)
# Excel: =SWITCH(A2, "A", 1, "B", 2, 0)
# VisiData:
= {'A': 1, 'B': 2}.get(status, 0)
# Applied to orders:
= {'pending': 0, 'processing': 1, 'shipped': 2, 'delivered': 3}.get(status, -1)
AND / OR Logic
| Excel | VisiData Filter | Description |
|---|---|---|
=AND(A2>100, B2<50) | z| amount > 100 and quantity < 50 | AND in filter |
=OR(A2="X", A2="Y") | z| status == 'X' or status == 'Y' | OR in filter |
=NOT(A2>100) | z| not (amount > 100) | NOT in filter |
SUMIF / COUNTIF Equivalents
| Excel / Google Sheets | VisiData Workflow | Description |
|---|---|---|
=SUMIF(A:A, ">100") | z| amount > 100 → z+ sum | Conditional sum |
=SUMIFS(C:C, A:A, "X", B:B, "Y") | z| a == 'X' and b == 'Y' → z+ sum on c | Multi-condition sum |
=COUNTIF(A:A, "active") | | active → count in status bar | Conditional count |
=COUNTIFS(A:A, "X", B:B, "Y") | z| a == 'X' and b == 'Y' → status bar count | Multi-condition count |
=AVERAGEIF(A:A, ">100", B:B) | z| a > 100 → z+ mean on b | Conditional average |
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# SUMIF equivalent: sum of large orders
# Move to 'amount' → cast: %
z|
# Enter: amount > 500
# Check status bar: shows selected row count
# Quick sum of selected:
z+
# Status bar shows sum of selected rows' amount
SUMPRODUCT Equivalent
# Excel: =SUMPRODUCT(price_col, quantity_col)
# VisiData — create derived column first:
=
# Enter: price * quantity
# Name: line_total
# Then sum the derived column:
+ sum
z+
Hands-On Practice
vd ~/github/practice-folder/visidata/04-cleaning/01-dirty_orders.csv
# 1. Cast 'amount' to float: move to 'amount' → %
# 2. Create order_size column:
=
# Enter: 'large' if amount > 500 else 'medium' if amount > 100 else 'small'
# Name: order_size
# 3. Create safe margin (avoid div/0):
=
# Enter: round(amount / quantity, 2) if quantity != 0 else 0
# Name: unit_price
# 4. Set sum aggregator on amount: + sum
# 5. Quick total: z+
# Note total in status bar
# 6. SUMIF: select large orders
z|
# Enter: amount > 500
# Quick sum of selected: z+
# 7. Press gu (deselect all)
# 8. Press Ctrl+S → save as /tmp/orders_calculated.csv