How Missing Values Change Sums, Averages and Counts

Two people summarise the same Excel sheet — one with formulas, one with pandas — and get different averages. Neither made a mistake: they treated the blank cells differently. Missing values are invisible in a total but change almost every other summary: an average over five filled cells is not an average over eight, a count of rows is not a count of values, and a group with no values at all can show a sum of zero that looks like a real result. This page sets out what each common Excel function and its pandas counterpart does with blanks, zeros and text placeholders such as "N/A", checks the Excel side by recalculating real formulas, and ends with a habit that keeps both tools in agreement. It belongs to Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl

The examples build a small workbook of monthly sales by rep with a genuine blank, a zero and an "N/A" text placeholder, write Excel formulas next to it, and recalculate them with LibreOffice (soffice) so the Excel results can be compared with pandas. Any spreadsheet that recalculates gives the same answers for these functions.

Step 1 — A sheet with blanks, zeros and placeholders

Real exports mix three different things in the same column: real zeros, empty cells, and text standing in for "unknown":

import pandas as pd
from openpyxl import Workbook

rows = [("North", "Ana", 1200), ("North", "Ben", None), ("North", "Chloé", 0),
        ("South", "Dev", 950), ("South", "Eva", "N/A"), ("South", "Farid", 1100),
        ("West", "Grace", None), ("West", "Hugo", None)]

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Rep", "Sales"])
for r in rows:
    ws.append(list(r))
labels = ["SUM", "AVERAGE", "COUNT", "COUNTA", "COUNTBLANK", "ROWS"]
formulas = ["=SUM(C2:C9)", "=AVERAGE(C2:C9)", "=COUNT(C2:C9)", "=COUNTA(C2:C9)", "=COUNTBLANK(C2:C9)", "=ROWS(C2:C9)"]
for i, (lab, f) in enumerate(zip(labels, formulas), start=2):
    ws.cell(i, 5, lab)
    ws.cell(i, 6, f)
ws["E9"] = "AVERAGEIF West"
ws["F9"] = '=IFERROR(AVERAGEIF(A2:A9,"West",C2:C9),"#DIV/0!")'
wb.save("sales.xlsx")
print("written: 8 reps, 3 blank, 1 zero, 1 'N/A'")

Ben, Grace and Hugo have no sales figure at all; Chloé genuinely sold nothing; Eva's figure is the text "N/A". Formulas in column F summarise the column the way most spreadsheets do. In a database, or in pandas after reading, these distinctions survive only if you keep them — a blank becomes NaN, a zero stays 0, and "N/A" stays a string unless you convert it.

Step 2 — What Excel's functions do

Recalculate the formulas and read back the results:

import subprocess
from openpyxl import load_workbook

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "sales.xlsx"],
               check=True, capture_output=True)
calc = load_workbook("calc/sales.xlsx", data_only=True)["Sales"]
excel = {calc.cell(r, 5).value: calc.cell(r, 6).value for r in range(2, 10) if calc.cell(r, 5).value}
for k, v in excel.items():
    print(f"{k:16} {v}")

SUM adds the three numbers (1,200 + 0 + 950 + 1,100 = 3,250) and ignores both blanks and the text. AVERAGE divides by the count of numbers — four, including Chloé's zero — so 812.50; blanks and "N/A" do not count, but the zero does. COUNT counts numeric cells (4), COUNTA counts non-empty cells including text (5), and COUNTBLANK the empty ones (3). ROWS counts all eight. For the West region, where every value is blank, AVERAGEIF has nothing to average and returns #DIV/0! — Excel refuses to invent a figure. These rules are worth knowing exactly, because pandas follows some of them and not others.

Step 3 — The pandas equivalents, and a surprise at read time

Read the same sheet with pandas and compute the matching figures:

df = pd.read_excel("sales.xlsx", usecols="A:C")
print(df["Sales"].tolist(), df["Sales"].dtype)

sales = df["Sales"]
pandas_side = {
    "SUM": sales.sum(),
    "AVERAGE": sales.mean(),
    "COUNT": sales.count(),
    "COUNTA": sales.count(),
    "COUNTBLANK": int(sales.isna().sum()),
    "ROWS": len(df),
}
for k, v in pandas_side.items():
    print(f"{k:12} pandas {v!s:>8}   Excel {excel[k]}")

sum() skips missing values, mean() divides by the number of non-missing values and count() counts non-missing values, so they match Excel's SUM, AVERAGE and COUNT; len(df) — or size in a groupby — counts rows like ROWS. But COUNTA and COUNTBLANK disagree: pandas says 4 and 4, Excel 5 and 3. The column came back as float64, and Eva's "N/A" is NaN. That is pandas' default behaviour: read_excel treats a list of common placeholder strings — "N/A", "NA", "n/a", "NULL", "nan", "#N/A" and others — as missing while reading. Usually that is convenient; here it erased the difference between "the cell says N/A" and "the cell is empty", which Excel keeps.

To count the way Excel does, read the column with the default placeholders switched off, keeping only truly empty cells as missing:

raw = pd.read_excel("sales.xlsx", usecols="A:C", keep_default_na=False, na_values=[""])
print(raw["Sales"].tolist())
pandas_side["COUNTA"] = int(raw["Sales"].count())
pandas_side["COUNTBLANK"] = int(raw["Sales"].isna().sum())
print("COUNTA", pandas_side["COUNTA"], "| COUNTBLANK", pandas_side["COUNTBLANK"])

With keep_default_na=False pandas no longer treats "N/A" specially, and na_values=[""] keeps empty cells as NaN — without it they would come back as empty strings. Now "N/A" is a value, so count() gives Excel's 5 and the blank count 3. For arithmetic on such a column you then convert explicitly with pd.to_numeric(raw["Sales"], errors="coerce"). Which reading is right depends on the question: for totals and averages the default is what you want; for auditing data entry ("how many cells did people fill in?") the raw reading is. More on placeholder handling in Replace Placeholder Text with NaN in pandas.

Step 4 — The all-blank group: 0 or nothing?

Group sums are where pandas and Excel genuinely disagree. Summarise by region:

by_region = df.groupby("Region")["Sales"].agg(["sum", "mean", "count", "size"])
by_region["sum (min_count=1)"] = df.groupby("Region")["Sales"].sum(min_count=1)
print(by_region.to_string())
print("Excel AVERAGEIF for West:", excel["AVERAGEIF West"])

The West region has two reps and no figures. pandas reports its sum as 0 — the sum of nothing is defined as zero — while its mean is NaN. A report showing "West: 0" is wrong in a dangerous way: it says the region sold nothing, when in fact nobody knows. sum(min_count=1) fixes this: it requires at least one real value and otherwise returns NaN, which shows as a blank in Excel. count (0) next to size (2) is the honest picture: two reps, no figures. Excel's SUMIF behaves like pandas' default here and returns 0 for the West, so the same trap exists in formulas — adding a COUNTIF column beside every SUMIF is the spreadsheet equivalent of min_count.

Step 5 — Blanks versus zeros in averages

Whether a blank means "zero" or "unknown" changes the average more than anything else. Compare the two readings of the same column:

as_unknown = sales.mean()
as_zero = sales.fillna(0).mean()
print(f"blanks as unknown: {as_unknown:,.2f}  (over {sales.count()} values)")
print(f"blanks as zero:    {as_zero:,.2f}  (over {len(sales)} values)")

Treating the four missing figures as unknown gives an average of 812.50; treating them as zero halves it to 406.25. Both can be correct — if a blank means "no sales were made", it is a zero and should be entered as one; if it means "not reported yet", it must stay blank. The worst outcome is mixing the two meanings in one column, which is why Chloé's real zero and Ben's blank should never be "cleaned" into the same thing. Decide what a blank means for each column, write it down — a data dictionary sheet is a good place, see Add a Data Dictionary Sheet to an Excel Report — and fill or keep blanks accordingly before summarising.

Step 6 — Prove pandas and Excel agree

When a report is produced by pandas but checked by someone with a spreadsheet, a reconciliation at the end catches any difference in treatment:

mismatch = {k: (pandas_side[k], excel[k]) for k in pandas_side
            if round(float(pandas_side[k]), 6) != round(float(excel[k]), 6)}
print("differences between pandas and Excel:", mismatch or "none")

With the same definitions on both sides — numeric figures from the default read, COUNTA and COUNTBLANK from the raw read — every figure matches. Without the second read, this check is exactly what would have flagged the COUNTA difference. Running a check like this against the formulas in the delivered workbook — recalculated with LibreOffice as above, or with a formula engine as in Recalculate Excel Formulas Without Excel in Python — turns "why don't my numbers match yours?" into a test that fails before anyone asks.

Common pitfalls

• Forgetting pandas' default placeholders. read_excel turns "N/A" and similar text into NaN; use keep_default_na=False when that matters.
• All-blank groups summing to 0. Use sum(min_count=1) and show the count.
• Filling blanks with 0 to "fix" averages. Only if a blank really means zero.
• Confusing count and size. One counts values, the other rows.
• No reconciliation. Compare pandas results with the workbook's formulas.

Conclusion

Excel's SUM, AVERAGE and COUNT ignore blanks and text but include zeros; COUNTA also counts text. pandas reads common placeholders such as "N/A" as NaN by default, after which sum, mean and count skip missing values, while size and len count rows. The traps are an all-blank group summing to 0 — use min_count=1 and report counts — and treating blanks as zeros, which can halve an average. Decide what a blank means per column, and reconcile pandas figures with spreadsheet formulas before publishing.

Frequently asked questions

Does Excel's AVERAGE ignore blank cells? Yes. AVERAGE divides by the number of numeric cells, so blanks and text are left out, but zeros are included and lower the average.

Why does pandas sum return 0 for a group with no values? The sum of no values is defined as 0. Pass min_count=1 to sum so that a group needs at least one real value, otherwise it returns NaN.

What is the difference between count and size in pandas? count returns the number of non-missing values; size returns the number of rows, including those with missing values.

Should I replace blanks with zero before averaging? Only if a blank really means zero. If it means unknown, keep it blank, because filling with zero lowers the average and hides the gap.

Related

• Up one level: Handling Missing Data in Excel Reports — finding and treating gaps.
• Tell Blank Cells, Empty Strings and Zeros Apart in Excel Data — what a blank really is.
• COUNTIF and COUNTIFS Equivalent in pandas — conditional counts.
• AVERAGEIFS, MAXIFS and MINIFS Equivalents in pandas — conditional averages.