3. Data cleaning and validation for CAE results

CAE result files rarely arrive analysis-ready. A solver may emit duplicate nodes at material interfaces, leave blank fields where a probe failed to write, mix millimetre and metre conventions across subassemblies, and bury a handful of physically impossible stress peaks inside an otherwise clean field. If these defects reach a pivot table or a dashboard, they propagate silently: a duplicated node set inflates a mass total, a missing value collapses an average, and a unit mismatch turns a 250 MPa result into 250,000 Pa. This chapter treats cleaning and validation as a deliberate, auditable stage between raw solver output and any reporting layer.

Establishing a validation contract before touching data

The most reliable way to avoid ad-hoc cleaning is to define, up front, what a valid record looks like. For a typical structural study exported as a node table, that contract has four parts: a unique key (node ID), a complete coordinate triple, a consistent length unit, and result values inside physically plausible bounds. Write this contract down as a small specification table and keep it beside the workbook. Every cleaning step then has a pass/fail criterion rather than a subjective judgement.

Removing duplicate nodes and elements

Duplicates in CAE exports usually come from two sources: merged parts that retained coincident nodes at the interface, and repeated export passes that appended the same block twice. The distinction matters. Coincident nodes at a bonded interface are geometrically identical but may carry different IDs and belong to different element groups; deleting one blindly can orphan elements. Repeated export blocks, by contrast, are true duplicates and safe to collapse.

A practical procedure in Excel: add a helper column that concatenates rounded coordinates, =ROUND(B2,4)&"|"&ROUND(C2,4)&"|"&ROUND(D2,4), then use COUNTIF over that column to count occurrences. Rows with a count greater than one are coincident. Before deleting, check whether the duplicate IDs appear in the element connectivity table; if they do, the pair is a genuine interface and should be merged (keep one ID, remap the element references) rather than removed. For pure export duplicates, Data → Remove Duplicates on the node ID column is sufficient, but always record the row count before and after in a cleaning log so the change is reversible and reviewable.

Handling missing values without distorting results

Missing values in simulation data are not random. A blank stress cell often means the element was not solved, the probe fell outside the part, or the export truncated a column. Imputing a mean here is dangerous because it fabricates a result that the solver never produced. The defensible approach is triage: classify each blank by cause, then choose a treatment that preserves traceability.

• Structural blanks (missing coordinate or ID): quarantine the row. These records cannot be located in space and should never enter aggregates.
• Result blanks on solved nodes: mark as NA and exclude from averages using AVERAGEIF or AGGREGATE(1,6,range), which ignores errors and hidden rows. Report the count of excluded records alongside any summary statistic.
• Systematic blanks (an entire part column empty): treat as an export fault and re-export rather than patch in Excel.

For time-history channels, interpolation between adjacent time steps is acceptable when the gap is short relative to the sampling interval, but the interpolated points must be flagged in a separate column so downstream charts can distinguish measured from reconstructed data.

Unit consistency checks

Unit errors are the most expensive defects because they survive visual inspection. A model meshed in millimetres but loaded in newtons produces stresses in MPa only if density and modulus are also in the mm–N–MPa system; mixing a metre-based modulus with millimetre coordinates shifts results by three orders of magnitude. Enforce consistency with an explicit unit column and a conversion step, never by silently rescaling numbers.

Declared source units:  length = mm, force = N, stress = MPa
Target reporting units: length = m,  force = N, stress = Pa

Length conversion:  =B2/1000
Stress conversion:  =E2*1000000

Sanity anchor: a 250 MPa peak must read 2.50E+08 Pa after conversion.
If it reads 2.50E+05, the source was already in Pa — stop and recheck.

The sanity anchor is the key control. Pick one known reference value — a hand calculation, a previous validated run, or a nominal load case — and verify it survives conversion. If the anchor is off by a power of ten, the unit declaration is wrong, not the data.

Outlier detection in simulation fields

Outliers in CAE data fall into two categories that demand opposite responses. Numerical artefacts — a single node with stress ten times its neighbours — usually indicate a distorted element, a singular corner, or a probe error, and should be flagged and excluded from contour statistics. Physical extremes — a genuine stress concentration at a fillet — are the result you are looking for and must never be removed. Distinguishing them requires spatial context, not just a statistical threshold.

A robust workflow combines a global statistic with a local check. Compute the interquartile range and flag values beyond Q3 + 1.5·IQR as candidates. Then, for each candidate, compare it with the median of its immediate neighbours (same element group, within one characteristic length). An artefact typically has no supporting neighbours; a real concentration is surrounded by a gradient of elevated values. Only artefacts are excluded, and each exclusion is logged with node ID, value, and reason.

Closing the loop with a validation gate

Cleaning is only trustworthy if it can be checked. Before any cleaned table feeds a report, run three gate checks: the row count matches the expected node count minus logged exclusions; the unit anchor value is within tolerance of its reference; and every excluded record appears in the exclusion log with a reason code. If any check fails, the dataset returns to the previous stage rather than proceeding. This gate is cheap to automate with a few COUNT and IF formulas and prevents the most common reporting failure — a clean-looking dashboard built on silently corrupted data.

The output of this stage is a validated dataset plus a short cleaning record: source file, row counts before and after, unit conversions applied, anchor verification result, and a list of excluded records with reasons. That record is the handoff artifact to the reporting layer, and it is what makes an automated report defensible when someone asks how a number was produced.