Excel NA Utils Skill

NA/missing value helper functions for Excel data processing

Overview

Consistent NA handling across Python and VBA based on CLAUDE.md guidelines:

• Treat empty cells, #N/A, "NA", "N/A" as missing values
• Exclude from numeric aggregates (mean, SD, min, max)
• Track excluded value counts for auditing
• Works with both Python and VBA

When to Use

• Python: Analyzing Excel data with polars/pandas
• VBA: Building summary macros with NA filtering
• Statistics: Computing aggregates while excluding NA
• Validation: Identifying and counting missing data
• Auditing: Track excluded value counts

Python API

from na_helpers import is_na, filter_na, na_ratio, numeric_only

# Check if single value is NA
is_na(None)           # True
is_na("")             # True
is_na("NA")           # True
is_na("#N/A")         # True
is_na(1.5)            # False

# Filter NA from list
data = [1, 2, None, "NA", 3, "#N/A"]
clean = filter_na(data)  # [1, 2, 3]

# Calculate NA ratio
ratio = na_ratio(data)  # 0.5 (3 out of 6)

# Extract only numeric values (after NA filtering)
numbers = numeric_only(data)  # [1, 2, 3]

VBA Templates

' Check if value is NA
If Not IsNA(cellValue) And IsNumeric(cellValue) Then
    ' Use value for calculation
    AddNumeric arr, n, cellValue
End If

' IsNA function checks:
' - Empty cells
' - Error values (#N/A, #DIV/0!, etc.)
' - Text markers ("NA", "N/A", "na", "n/a")

' AddNumeric adds to array only if not NA
' Result: clean array of valid numbers only

NA Value Definitions

These are treated as missing values:

Type	Examples	Handling
Empty	`` (blank cell)	Excluded
Error	#N/A, #DIV/0!	Excluded
Text markers	"NA", "N/A", "na"	Excluded (case-insensitive, trimmed)

Output

Functions return:

• is_na(): bool
• filter_na(): list of non-NA values
• na_ratio(): float (0.0-1.0)
• numeric_only(): list of numeric values

With auditing:

• Count of excluded NA values
• Count of remaining valid values
• NA ratio for reporting

Integration

Python + Excel

from na_helpers import filter_na, na_ratio

# Read from Excel, filter NA
data = [cell.value for cell in range]
clean = filter_na(data)
n_excluded = len(data) - len(clean)

# Report N and exclusions
print(f"N: {len(clean)} (excluded: {n_excluded})")

VBA (in vba.md)

' Module2 already implements IsNA + AddNumeric
' Example from BuildMetaSummary macro:

If Not IsNA(cellValue) And IsNumeric(cellValue) Then
    AddNumeric arr, n, cellValue
End If

' Result: proper N calculation with excluded count

Files

• na_helpers.py: Python implementation
• na_helpers.vba: VBA templates (reference)
• SKILL.md: This file

Related Skills

• excel-inspector: Analyze NA ratio per column
• excel-vba-modifier: Use for VBA NA handling
• Reference: vba.md (full VBA implementation)

Examples

Example 1: Calculate mean excluding NA

from na_helpers import filter_na
import statistics

data = [10, 20, None, 30, "NA", 40]
clean = filter_na(data)
mean = statistics.mean(clean)  # 25.0 (10+20+30+40)/4

Example 2: Report statistics

from na_helpers import filter_na, na_ratio

data = [...1000 values...]
clean = filter_na(data)

n_total = len(data)
n_valid = len(clean)
n_na = n_total - n_valid
ratio = na_ratio(data)

print(f"N: {n_valid} (excluded: {n_na}, NA%: {ratio*100:.1f}%)")

Example 3: VBA summary calculation

' In Module2
If Not IsNA(ageVal) And IsNumeric(ageVal) Then
    AddNumeric ageArr, nAge, ageVal
    If CDbl(ageVal) >= 60 Then
        AddNumeric ageOldArr, nAgeOld, ageVal
    Else
        AddNumeric ageYoungArr, nAgeYoung, ageVal
    End If
End If

Compliance

Follows CLAUDE.md NA Handling Guidelines: ✓ Empty, error, text NA treated consistently ✓ NA excluded from aggregates ✓ Count tracked for auditing ✓ Proper numeric conversion order ✓ Results report N correctly