Turning a column of numbers into a KPI card by hand

A dashboard's KPI cards look like they come from somewhere complicated, but each one is a single pass over a single column: add up the values, divide by how many there are, and keep track of the biggest and smallest as you go. No forecasting, no modeling — just four running totals updated row by row.

The method

1. Pick the numeric column you care about.
2. Initialize: sum = 0, count = 0, max = -infinity, min = +infinity.
3. For each row with a usable value: add it to sum, add 1 to count, replace max if this value is bigger, replace min if this value is smaller.
4. Skip blank or unparseable cells, but count them separately — a KPI card that silently treats a blank as zero is lying about the average.
5. After the pass: average = sum ÷ count. That's the whole card — sum, average, max, min, plus how many rows and how many were skipped.

A worked example

Seven days of sales, in dollars: 100, 120, 95, 140, 130, 160, 110. No blanks.

Walking the rows: running sum goes 100, 220, 315, 455, 585, 745, 855. After all seven rows, sum = 855 and count = 7.

average = 855 ÷ 7 = 122.14 (rounded to two places). Tracking the running max and min alongside the sum: max = 160 (day 6), min = 95 (day 3).

The finished KPI card reads: sum $855 · average $122.14 · max $160 · min $95, over 7 rows with 0 skipped. Every one of those four numbers came from the same single walk through the column — nothing was recomputed separately.

Where honesty about blanks matters

Say one of the seven days was a blank cell instead of 95 — a store closed, nothing recorded. If the average divides by 7 anyway and treats the blank as 0, the sum understates nothing (it just skips it) but the average is now wrong twice over: once for excluding a value that should be excluded, and once if someone divides by 7 instead of 6. The correct average over the six real values is (855 - 95) ÷ 6 = 760 ÷ 6 = 126.67 — notably higher than 122.14, because the missing day happened to be the lowest one. A dashboard that doesn't separately report "6 of 7 rows had a value" is hiding the exact fact that changes the number.

The same discipline applies to charts: a bar chart capped at the top 10 categories should say "12 more not shown," and a time series should never interpolate a missing date — a gap in the line is more honest than a guessed value connecting two real ones.

The free Data Dashboard Builder runs this exact column walk in your browser: paste a CSV and get KPI cards, a grouped bar chart and a time series if there's a date column, with blanks counted next to every average and capped charts saying exactly how many categories were left out. The file never leaves your device.