Thiessen and Isohyetal Area-Weighted Rainfall in Excel

Estimate rainfall over a basin, not runoff

The four illustrative gauges record the same storm period. Their rainfall depths are 95, 88, 82 and 30 mm; their assigned areas are 12, 18, 20 and 75 km2. The area-weighted estimate is 52.912 mm. The arithmetic mean is 73.75 mm: the difference is 20.838 mm, not a measured error against known true rainfall.

Rainfall volume is depth times area. It is not runoff: interception, infiltration, storage and other processes are outside this calculation. This example estimates a volume from an assumed spatial rainfall distribution.

Prepare Thiessen areas before using the worksheet

Use GIS to construct the gauge proximity polygons, clip them to the watershed boundary and verify that the resulting areas cover the basin without gaps or overlaps. This sheet calculates weights; it does not construct polygons. All depths must use the same time interval and unit.

Enter the independent basin area in E2. The example uses 125 km2. E3 checks numeric, nonnegative areas and depths, a positive basin and total area, and agreement with the basin area to relative tolerance 1E-9. Matching totals cannot prove that the map has no overlapping or missing pieces. Use an area-preserving measurement method and consistent precision. A missing gauge is not zero rainfall.

Calculate weighted depth and rainfall volume

B2:B5 contains polygon areas and C2:C5 rainfall depths. E4 is the unrounded weighted depth; E5 is volume in cubic metres. One km2 times one mm equals 1,000 m3. Format displayed results to two decimals without rounding intermediate calculations.

E6 compares the arithmetic gauge mean with E4. A zero total area returns a diagnostic, whereas numeric zero rainfall at every gauge correctly returns zero.

=IF(E3="OK",SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5),E3)

A separate isohyetal-band example

H2:H4 contains net band areas of 20, 55 and 50 km2. I2:J4 contains the bounding contour depths: 20–40, 40–60 and 60–80 mm. K2:K4 calculates their midpoint depths of 30, 50 and 70 mm. These are illustrative bands for a separate map, not contours inferred automatically from the four gauges.

Use net area between contours within the basin, not cumulative enclosed contour areas. The midpoint is the assumed representative band depth. Bands at an outer boundary or an extreme without two contours require a justified representative depth; do not invent a missing contour.

E8 validates numbers, nonnegative inputs, ordered contour pairs and total area. E9 gives 54.8 mm and E10 gives 6,850,000 m3. Map quality and the assumed within-band depths determine the estimate.

=IF(E8="OK",SUMPRODUCT(H2:H4,K2:K4)/SUM(H2:H4),E8)

Try the example and verify the changes

Change C5 from 30 to 80: E4 becomes 82.912 mm and E5 becomes 10,364,000 m3. Restore C5. Change one polygon area without updating the independent basin area: the coverage warning prevents an apparently valid basin result.

Set all four rainfall depths to zero: E4 and E5 become zero. Clear a rainfall cell instead: the input check rejects it. In the separate band example, put an upper contour below its lower contour and check that E8 rejects it.

To add stations or bands, extend the ranges, numeric counts and pair checks consistently. The supplied formulas validate exactly four gauges and three bands.

Further reading

• Microsoft Support — SUMPRODUCT function
• National Weather Service — Mean areal precipitation and the hydrologic cycle
• US Army Corps of Engineers — HEC-HMS gage weights