Excel Silently Sums ID Numbers in Pivot Tables, Producing Plausible but Meaningless Data

·1 views

Data analyst Michael Nocito warns that Excel automatically applies a Sum function to any numeric column dragged into a pivot table's Values area, even when the column contains identifiers like IDs, zip codes, or invoice numbers that should never be summed. In a real-world dataset of 82,956 Steam games, this default behavior caused AppID values to be summed rather than counted, producing a group share of 0.45% instead of the correct 0.71%. The resulting figures appeared credible on screen — complete with decimals and no visible error — making them easy to overlook during a routine review. Nocito's recommended fix is a simple one-line test: ask whether adding two values from the column together would produce a meaningful result, and if not, switch the field setting from Sum to Count. He also advises using an independently computed check cell to catch such errors, since misleading figures from this trap are designed by circumstance to look correct, not broken.

Read the full story at DEV Community

This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)

RAG Explained: How AI Can Answer From Your Documents Without Retraining

Retrieval-Augmented Generation (RAG) is an AI architecture that connects large language models to private or up-to-date document collections without requiring the model to be retrained. Standard LLMs rely on knowledge frozen at training time, making them prone to outdated or hallucinated answers when queried about business-specific data. Fine-tuning was the early alternative, but it proved slow, costly, and unable to keep pace with frequently changing documents. RAG solves this by retrieving relevant document excerpts at query time and feeding them directly into the model's prompt, so the model reads current information rather than recalling stale parameters. First proposed in a 2020 Facebook AI Research paper by Lewis et al., the approach has since become one of the most widely adopted patterns in applied AI.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Why Knowing When Not to Trust AI Code Matters More Than Using It

AI coding tools have become powerful enough to handle entire development workflows, but their ability to generate plausible-looking code creates a hidden risk for developers. Models can invent non-existent API methods, misread library versions, or fix surface symptoms while leaving deeper bugs intact — and such failures often go unnoticed because the app appears to run correctly. Experts warn that high-stakes changes involving authentication, payments, databases, or personal data demand especially rigorous human review, not just a passing test suite. AI-generated tests can also be misleading, as they may simply mirror the model's own flawed assumptions rather than validating actual product requirements. The core challenge in modern AI-assisted development is not access to tools, but preserving human judgment about when and how much to trust their output.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Four Practical Lessons for Writing Alerts That Actually Work

A software engineer writing for DEV Community shares four non-obvious lessons learned from building and maintaining alerting systems in production environments. A key insight arose when a colleague's alert tracked message counts as a throughput proxy, unaware that each message could contain up to ten URLs, causing thresholds and SLOs to be silently denominated in the wrong unit. The author argues that monitoring queue age — rather than simple throughput floors — better distinguishes genuine stalls from idle periods, eliminating false pages during quiet workload windows. Tracking how many times a per-job deadline is exceeded, not just whether it fires, helps differentiate a system-wide failure from a small number of slow outlier jobs. Finally, setting alert thresholds where exceedances are genuinely rare ensures that a page signals something is truly stuck rather than merely slow.

0 comments Read more at DEV Community

ProgrammingDEV Community ·

Kubernetes DNS and NetworkPolicy Bug Can Silently Disable Prometheus Heartbeat Alerts

A subtle interaction between Kubernetes' default ndots:5 DNS resolver behavior and namespace-scoped NetworkPolicies can cause Pushgateway heartbeat metrics to stop updating without triggering any alerts. When a pod's fully qualified service name contains fewer than five dots, the resolver walks through search domain suffixes before attempting the absolute name, potentially matching a wildcard DNS record and routing traffic to the wrong endpoint. In a common homelab or internal cluster setup, this misdirects monitoring pushes to an ingress controller instead of Pushgateway, leaving timestamps stale while dashboards show no errors. The failure is especially dangerous because it targets the monitoring infrastructure itself, meaning no alert fires and no visible symptom surfaces. The issue only manifests when three conditions align: the default ndots:5 DNS policy, a wildcard internal DNS record, and a push-based alerting pattern like Prometheus with Pushgateway.

0 comments Read more at DEV Community