Creating Self-Checking Spreadsheets for Design Verification

The Cost of Spreadsheet Errors

Studies consistently find that over 80% of spreadsheets contain at least one error. In engineering, a wrong number can mean an undersized beam, an over-stressed connection, or a failed foundation. Self-checking spreadsheets do not eliminate human judgment, but they do catch the mechanical errors that humans miss — especially under deadline pressure.

Layer 1: Input Validation

The first line of defense is preventing bad data from entering the spreadsheet. Implement these checks on every input cell:

• Range checks: Concrete strength between 20 and 80 MPa. Beam width between 200 and 1000 mm. Steel yield strength exactly 275 or 415 MPa.
• Consistency checks: Effective depth must be less than total depth. Top reinforcement area cannot exceed the section capacity. Span-to-depth ratio should be within typical ranges.
• Completeness checks: Flag any blank input cell that affects downstream calculations.

Layer 2: Intermediate Checks

Add verification at each calculation step, not just the final result. Check that the neutral axis depth is positive and less than the section depth. Verify that the compression block depth a = beta_1 × c falls within expected bounds. Confirm that the steel strain exceeds the yield strain for tension-controlled sections. Each check is a simple IF formula that returns "OK" or a warning message.

Layer 3: Code Compliance Checks

Structural codes specify minimum and maximum reinforcement ratios, maximum spacing limits, minimum cover requirements, and development length criteria. Build a compliance summary table that checks each requirement and displays a clear pass or fail status. Color-code the results — engineers scan for red cells, not for numbers.

Layer 4: Reasonableness Checks

Even when all code checks pass, a result can be technically correct but practically unreasonable. Flag reinforcement ratios above 2% (constructability concern), deflections above L/500 (excessive for most applications), and foundation pressures within 5% of allowable (low margin). These are engineering judgments encoded as soft warnings rather than hard failures.

Implementing a Check Summary Dashboard

Testing Your Checks

Deliberately enter wrong values and verify that your checks catch them. Try zero beam width, negative moment, reinforcement exceeding the section area, and span-to-depth ratios of 50. If any of these pass without a warning, your checks have gaps. Document your test cases in a separate sheet for future reference.

Sample Code

' Excel verification formulas for a column check
' Cell D2 - Axial capacity (kN):
' =0.65 * 0.80 * (0.85*B2*(B3*B4 - B5) + B6*B5)
'   B2=fc  B3=width  B4=depth  B5=As  B6=fy

' Cell D3 - Demand/Capacity ratio:
' =B7/D2     where B7 = factored axial load Pu

' Cell D4 - Status with color flag:
' =IF(D3<=1, "PASS", "FAIL")

' ----- Self-check formulas -----
' Cell F2 - Input range validation:
' =AND(B2>=20, B2<=80, B3>=200, B4>=200, B5>0)

' Cell F3 - Steel ratio check:
' =IF(AND(B5/(B3*B4)>=0.01, B5/(B3*B4)<=0.08),
'   "OK", "WARNING: rho outside 1%-8%")

' Cell F4 - Cross-verification:
' =IF(ABS(D2-INDIRECT("Check!D2"))/D2 < 0.001,
'   "VERIFIED", "MISMATCH - review calculation"