Quality Control of Earthworks and Quantity Takeoff: Statistical Auditing of Excel Volumes

Earthwork quantity calculations are prone to errors from incorrect station spacings or area inputs. Auditing these large spreadsheets manually is time-consuming.

Using statistical analysis, we can automatically detect anomalies, such as sudden spikes in volume or area, identifying potential errors.

1. Outlier Check

We identify cross-sectional area changes between consecutive stations using statistical thresholds. Points with high deviation are flagged for review.

2. Python Audit Script

This Python script reads an Excel volume sheet and flags stations with high area variations:

def audit_takeoff(stations, cut_areas):
    threshold = 5.0 # Max area change rate (m2/m)
    anomalies = []
    
    for i in range(1, len(stations)):
        dist = stations[i] - stations[i-1]
        area_diff = abs(cut_areas[i] - cut_areas[i-1])
        change_rate = area_diff / dist
        
        if change_rate > threshold:
            anomalies.append({
                "station": stations[i],
                "change_rate": change_rate,
                "msg": "Sudden cross-section area change"
            })
    return anomalies

Applying these statistical filters helps ensure quantity takeoff accuracy, reducing bidding risk.