Using ChatGPT to Generate Complex Engineering Formulas in Excel

Why Engineers Should Use AI for Formulas

Writing a nested INDEX-MATCH with error handling, unit conversion, and conditional logic takes time and testing. ChatGPT can generate this formula in seconds from a plain-language description. The engineer's job shifts from writing syntax to verifying results — a better use of engineering expertise.

How to Prompt Effectively

The quality of the formula depends on the quality of your prompt. Be specific about inputs, outputs, and edge cases. A vague prompt like "calculate beam capacity" produces generic results. A precise prompt produces accurate, usable formulas.

Common Formula Types Engineers Need

Multi-Condition Lookups

Ask ChatGPT to write formulas that look up values based on two or three criteria simultaneously — for example, finding the allowable bearing pressure based on both soil type and footing depth. These multi-criteria lookups are tedious to write manually but straightforward for AI.

Interpolation Between Table Values

Engineering codes often require linear interpolation between tabulated values. Ask for a formula that interpolates development length between two concrete strengths or between two bar sizes. Specify whether you want linear or logarithmic interpolation.

Iterative Calculations

Some engineering calculations require iteration — finding the neutral axis depth that satisfies equilibrium, for example. While Excel's iterative calculation feature can handle this, ChatGPT can suggest efficient approaches using Goal Seek alternatives or convergence formulas.

Verifying AI-Generated Formulas

Never use an AI-generated formula without verification. Test it against hand calculations for at least three cases: a typical case, an extreme case, and an edge case. Check that the formula handles division by zero, negative inputs, and out-of-range values correctly. The AI writes syntax well but does not understand engineering judgment — that remains your responsibility.

Building a Formula Library

Save your verified formulas in a reference spreadsheet organized by topic: structural design, geotechnical, hydraulics, cost estimation. Include the original prompt, the formula, a description, and a test case. This library becomes a reusable asset that grows with every project.

Sample Code

' Prompt-generated VBA: steel beam deflection checker
' ChatGPT prompt used:
' "Write a VBA macro to check beam deflection against
'  L/360 limit. Inputs: span(m), I(mm4), E(MPa), w(kN/m)"

Sub DeflectionCheck()
  Dim L As Double, I As Double
  Dim E As Double, w As Double

  L = Range("B2").Value * 1000  ' m to mm
  I = Range("B3").Value         ' mm4
  E = Range("B4").Value         ' MPa
  w = Range("B5").Value         ' kN/m

  ' 5wL^4 / (384EI)  units: N/mm
  Dim delta As Double
  delta = (5 * w * L ^ 4) / (384 * E * I)

  Dim limit As Double
  limit = L / 360

  Range("D2").Value = Round(delta, 2)
  Range("D3").Value = Round(limit, 2)
  Range("D4").Value = IIf(delta <= limit, "OK", "EXCEEDS L/360")
End Sub