For experts: every formula, the spreadsheet cells, and where the calculator differs
The formulas are those of the MyCheckDam workbook (Peter Dillon’s check-dam water balance template); each step names the workbook cells it reproduces.
What you need
- Daily gauge-board readings of the water in the check dam, in centimetres.
- Daily rainfall at the site, in millimetres.
- A one-time level survey (for example by dumpy level): the water-surface area, and if you have it the stored volume, at a series of levels from the bed up past the spillway crest.
- The structure: the reduced level (RL) of the gauge’s 0 cm mark, the spillway crest level, the crest length, and the catchment area.
Levels are reduced levels in metres. As in the workbook, a local datum is fine: the Badgaon example takes the spillway crest (cease-to-flow level, CTF) as 100 m.
1 · Area and volume at each day’s level
The survey table is interpolated linearly between its levels (sheet 2, columns R–T). Blank volumes are filled from the areas: the cone formula for the lowest segment, where the area starts at zero, and the trapezoidal rule above it.
2 · The daily water balance
For each day, with D the water level (m), E the water-surface area (m²), F the stored volume (m³), e the evaporation rate (m/d) and P the rainfall (mm); the subscript −1 is the day before:
Inflow is not measured: it is whatever closes the balance. The running total of inflow minus evaporation, recharge and spill must equal the water in the structure every day; the calculator checks this.
r, the infiltration rate on days when the level is above the spillway, rising or steady, is by default the mean dry-weather infiltration rate of the season: the average of K over the days where K > 0.0001 m/d (cell M10). You can instead use the mean rate of each period, or a fixed rate.
The first day compares against an empty structure, as the workbook does. If there was already water in it, set the level on the day before the record in Step 1 (Advanced).
3 · Water left at the end
Water still stored on the last day is assumed to drain away later by infiltration and evaporation, in proportion to their rates (cells AH131–AJ135):
4 · Season results
The sensitivity table re-runs the balance with each of evaporation, the infiltration rate on wet days and the weir coefficient changed by ±20 %.
5 · Dry-weather infiltration from the gauge alone
Step 5, Infiltration rate, uses the method of the workbook’s “Check dam calculator for MyWell” sheet. It needs only the gauge readings and rainfall, so it works for any check dam monitored in MyWell, even without a survey.
- A reading is a dry recession reading when, compared with the reading before it, the water is not above the spillway, it did not rain more than 0.1 mm, and the level did not rise by more than 2 cm.
- Each unbroken run of at least three such readings gets a least-squares line of gauge reading (cm) against time (days).
- The slopes are averaged with weights n − 1 (n = readings in the run), and the evaporation rate is subtracted.
The two methods estimate the same thing from different days, so expect them to differ. For Badgaon in 2014 the recession runs give 19 mm/d (weighted) and 43 mm/d (unweighted), while the water balance gives 31 mm/d. The workbook’s note attributes the gap to faster infiltration when the dam holds more water.
Assumptions
- No water is pumped or taken directly from the check dam.
- Evaporation is constant through the season.
- All inflow arrives as runoff from the catchment; seepage in from the banks counts as inflow.
- Spill follows the weir equation with the coefficients you give; flow through the structure itself is not modelled.
- One reading a day. The water balance needs every day in the record; a missing gauge reading between two days is interpolated and marked.
Differences from the spreadsheet
With the Badgaon example the calculator reproduces every daily column and total of the workbook to
floating-point precision (checked by the tests in test/). Where it does not, it is deliberate:
| What | Workbook | Calculator |
|---|---|---|
| Water left on the last day (AH131) | Typed as 3,014 m³, so the balance ends at −0.34 m³ | The last day’s volume, 3,013.66 m³; the balance closes at 0. Recharge is 0.29 m³ lower. |
| Evaporation / recharge (H158) | R150 / Q151 = 0.163: evaporation without the end share over recharge with it | Both with the end share: 0.167 |
| Pooled standard error of the recession slope (row 119) | Uses (n−1)·SE although labelled SE²; the result, 0.32, has units of √(cm/d) | Uses (n−1)·SE² as labelled: 0.19 cm/d |
| Sensitivity table (rows 160–168) | Pasted values from earlier runs; the C₁ row is labelled 1.28 but its values are for 1.3 | Recalculated whenever an input changes |
| Levels above the survey table | #REF! error | The top segment is extended, with a warning |
| Recession readings at 0 cm | Would be fitted as real levels | Skipped: the water is at or below the gauge zero, so its level is unknown. No effect on Badgaon. |
| Missing days | You type the adjusted level in column D | Enter a level override, or leave the day blank to interpolate between its neighbours |
Also noted, with no effect on results: the first row of column M compares a number with the text “0.001”, which Excel always treats as true; the first day’s dry-weather rate is 0 either way. The survey volume at 98 m was typed as 225 m³ where the cone formula gives 224.7 m³; entered volumes are used as entered.
Sources
Water balance template by Peter Dillon. Badgaon check dam gauge-board and rainfall readings (July–November 2014) and dumpy-level survey by Yogita, MARVI project. The water-balance infiltration rate for Badgaon is discussed in Dashora et al. (2019).