Let’s run a quick test: Designing an ogee crest with P = 5 m, H(_d) = 3 m, vertical upstream face.
| Task | Old “.xls” | New “Better” Version | | :--- | :--- | :--- | | Find C value | Manual interpolation (2 min) | Automatic (0.5 sec) | | Generate X-Y crest coordinates | 10 min (copy formulas) | Instant (dynamic array) | | Check tangent point slope match | Guess/check | Solved automatically | | Produce Q vs. H table | 20 min manual calc | Built-in, 2 sec refresh | | Risk of formula error | High (hidden references) | Low (structured tables) |
Time savings for a single design iteration: ~30 minutes. ogee spillway designxls better
Remember the two equations?
Typing these into an XLS with a 0.1m step size gives you instant coordinates for formwork fabrication. No manual table lookups, no rounding errors. Let’s run a quick test: Designing an ogee
Section A: Input Parameters (User Entry)
Section B: Hydraulic Calculations (Formulas) This section determines the actual head operating the spillway. Typing these into an XLS with a 0
| Row | Description | Formula / Logic |
| :--- | :--- | :--- |
| 10 | Unit Discharge ($q$) | =B3/B4 |
| 11 | Design Head ($H_d$) | See Note 1 below |
| 12 | Approach Velocity ($v_a$) | =B3/(B4*(B5-B6)) (Approximate) |
| 13 | Velocity Head ($h_v$) | =(B12^2)/(2*9.81) |
| 14 | Total Head ($H_0$) | =B11 + B13 |
Section C: Coefficient Correction (The "Deep" Part) Most simple spreadsheets use a constant discharge coefficient ($C$). A "better" spreadsheet corrects $C$ based on $H/H_d$.
Add a button that runs this simple code to copy Sheet 2 (Coordinates) into a CSV format ready for AutoCAD PLINE command:
Sub ExportToCAD()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Geometry")
ws.Range("B2:C100").Copy ' Copy X and Y columns
' Create a text file or simply message box that data is ready
MsgBox "Coordinates copied! Paste into AutoCAD command line.", vbInformation
End Sub
Use a Data Table to show how Crest Length ($L$) changes with varying Head ($H$).