Size gutter
Both versions use the same inputs — switch to compare, or use the side-by-side table below.
This module reproduces the legacy workbook cell-for-cell, including its known defects, so results always match the Excel sheet. Expand any result to validate the exact formula used. Known workbook defects (preserved intentionally):
- D1 ('Gutter size'!B18 / G12) B18 = IF(B17=G12, G19, G31), but G12 is the formula =B17 — the condition is always TRUE, so the 400 mm/hr table's total (G31) is unreachable and the rainfall-intensity input has no effect on any result.
- D2 ('Gutter size'!H16) The 10 m² bucket count is hardcoded to 1 instead of the remainder formula used by every other bucket (and used correctly at H28 in the 400 table). This overstates the total cross-section by 4,650 mm² whenever the true remainder count is not 1 — for the default inputs it should be 0, giving 101,700 instead of the method-correct 97,050.
- D3 ('Gutter size'!F11 / F21) The table headers read "350mm/hr" and "275mm/hr (Malaysia Standard)", but the tabulated values are actually for 450 and 400 mm/hr.
- D4 ('Rwdp size'!C21:C25, A29 (also 'Gutter size'!B29, B38, B46)) The "≈ 200/150/125mmØ" nominal labels, the "2 number of 100mm Diameter RWDP" recommendation, the proposed gutter size (950) and the proposed RWDP diameters (150) are hand-typed text in the workbook — manual engineering judgment, not computed by any formula.
Min average gutter height (incl. 25 mm freeboard)
251 mm
'Gutter size'!B22
Min gutter girth (for 1.0 mm thick Galvalume gutter)
977 mm
'Gutter size'!B27
Hand-typed workbook values (defect D4 — manual judgment, not computed)
- Gutter gradient: 1 : 200 (used by no formula)
- Proposed Min. Gutter size => 450mm(W) x 250mm(H): 950 mm
- Proposed minimum diameter for RWDP (eave gutter): 150 mm
- Proposed minimum diameter for RWDP (internal box gutter): 150 mm
Inputs · highlighted cells of the 'Gutter size' sheet
Results · expand any row to validate its formula
Formula ('Gutter size'!B14)
catchmentWidth × catchmentLength
With current values
35.05 × 10 = 350.5
Formula ('Gutter size'!G15)
FLOOR(roofCatchmentArea, 10)
With current values
FLOOR(350.5, 10) = 350
Formula ('Gutter size'!G16)
CEILING(flooredCatchmentArea, 10)
With current values
CEILING(350, 10) = 350
Formula ('Gutter size'!G19)
(count10 × area10) + (count20 × area20) + (count50 × area50) + (count100 × area100) + (count200 × area200)
With current values
(1 × 4650) + (0 × 8060) + (1 × 16800) + (1 × 29250) + (1 × 51000) = 101700
Reference table rows used — Gutter sizes calculation basing on Rainfall Intensity as 350mm/hr (Malaysia Standard)
| Catchment bucket (m²) | Cross-section (mm²) | Count | Subtotal (mm²) |
|---|---|---|---|
| 10 | 4650 | 1 (hardcoded — D2) | 4650 |
| 20 | 8060 | 0 | 0 |
| 50 | 16800 | 1 | 16800 |
| 100 | 29250 | 1 | 29250 |
| 200 | 51000 | 1 | 51000 |
Formula ('Gutter size'!G31)
(count10 × area10) + (count20 × area20) + (count50 × area50) + (count100 × area100) + (count200 × area200)
With current values
(0 × 3300) + (0 × 5760) + (1 × 12000) + (1 × 20920) + (1 × 36400) = 69320
Reference table rows used — Gutter sizes calculation basing on Rainfall Intensity as 275mm/hr (Malaysia Standard)
| Catchment bucket (m²) | Cross-section (mm²) | Count | Subtotal (mm²) |
|---|---|---|---|
| 10 | 3300 | 0 | 0 |
| 20 | 5760 | 0 | 0 |
| 50 | 12000 | 1 | 12000 |
| 100 | 20920 | 1 | 20920 |
| 200 | 36400 | 1 | 36400 |
Formula ('Gutter size'!B18)
IF(rainfallIntensity = tableIntensityG12, totalCrossSection450, totalCrossSection400)
With current values
IF(450 = 450, 101700, 69320) = 101700
G12 is the formula =B17, so this IF is always TRUE and the 400-table total (G31) is unreachable — the rainfall-intensity input has no effect on this result.
Formula ('Gutter size'!B22)
CEILING((totalCrossSection / gutterWidth) + 25, 1)
With current values
CEILING((101700 / 450) + 25, 1) = 251
Formula ('Gutter size'!B27)
gutterWidth + (2 × minGutterHeight) + 25
With current values
450 + (2 × 251) + 25 = 977
Formula ('Gutter size'!B36)
roofCatchmentArea × 95 × 1
With current values
350.5 × 95 × 1 = 33297.5
95 mm² of downpipe cross-section per m² of roof catchment.
Formula ('Gutter size'!B44)
roofCatchmentArea × 95 × 1
With current values
350.5 × 95 × 1 = 33297.5
95 mm² of downpipe cross-section per m² of roof catchment.
Fixed reference tables · never edited — the workbook's darkened side
Gutter sizes calculation basing on Rainfall Intensity as 350mm/hr (Malaysia Standard)
Roof catchment (m²) | Cross section of gutter (mm²) | Multiple | Sub-total (mm²) |
|---|---|---|---|
10 | 4650 | 1hardcoded (D2) | 4650 |
20 | 8060 | 0 | 0 |
50 | 16800 | 1 | 16800 |
100 | 29250 | 1 | 29250 |
200 | 51000 | 1 | 51000 |
Gutter sizes calculation basing on Rainfall Intensity as 275mm/hr (Malaysia Standard)
Roof catchment (m²) | Cross section of gutter (mm²) | Multiple | Sub-total (mm²) |
|---|---|---|---|
10 | 3300 | 0 | 0 |
20 | 5760 | 0 | 0 |
50 | 12000 | 1 | 12000 |
100 | 20920 | 1 | 20920 |
200 | 36400 | 1 | 36400 |
Notes (as printed in the workbook)
- 1. minimum gradient of eaves gutter is 1:500
- 2. assumes and effective width : depth ratio of 2:1
- 3. clearance - this gutter calculation based on 25mm clearance
- 4. freeboard - 25mm freeboard depth to gutter height
Rainwater downpipe calculation
Formula ('Rwdp size'!B13)
'Gutter size'!B14
With current values
350.5 = 350.5
Cross-sheet reference: value comes from the Gutter Capacity tab.
Formula ('Rwdp size'!B15)
roofCatchmentArea × 95
With current values
350.5 × 95 = 33297.5
95 mm² of downpipe cross-section per m² of roof catchment.
RWDP diameter options — expand to validate the formula
Formula ('Rwdp size'!B21)
SQRT(4 × (requiredCrossSection / 3.142))
With current values
SQRT(4 × (33297.5 / 3.142)) = 205.8888
From A = πD²/4 with the workbook's π approximation 3.142. The "≈ 200mmØ" nominal label next to this value is hand-typed in the workbook, not computed (D4).
Workbook's hand-typed nominal size: ≈ 200mmØ
Formula ('Rwdp size'!B23)
SQRT(4 × (requiredCrossSection / 2 / 3.142))
With current values
SQRT(4 × (33297.5 / 2 / 3.142)) = 145.5854
From A = πD²/4 with the workbook's π approximation 3.142. The "≈ 150mmØ" nominal label next to this value is hand-typed in the workbook, not computed (D4).
Workbook's hand-typed nominal size: ≈ 150mmØ
Formula ('Rwdp size'!B25)
SQRT(4 × (requiredCrossSection / 3 / 3.142))
With current values
SQRT(4 × (33297.5 / 3 / 3.142)) = 118.87
From A = πD²/4 with the workbook's π approximation 3.142. The "≈ 125mmØ" nominal label next to this value is hand-typed in the workbook, not computed (D4).
Workbook's hand-typed nominal size: ≈ 125mmØ
Workbook recommendation (hand-typed — defect D4, not derived)
"2 number of 100mm Diameter RWDP for roof catchment the above"
"Kindly allow each bay or every 4m per grid a rwdp"
These lines are typed text in the workbook and are inconsistent with the computed diameters above. Treat them as manual engineering judgment for the original project, not output of this calculator.
Inputs (highlighted cells of the 'Material cut list' sheet)
Results — expand any row to validate its formula
Formula ('Material cut list'!B18)
CEILING((gutterLength / (sheetLength − gutterLap)) / IF(gutterGirth × 2 > sheetWidth, 1, 2), 1)
With current values
CEILING((100000 / (1200 − 50)) / IF(2300 × 2 > 2300, 1, 2), 1) = 87
Usable length per sheet = sheet length − lap; two gutters are cut from one sheet when two girths fit across the sheet width.
Formula ('Material cut list'!B24)
IF(gutterGirth ≤ 600, 300, IF(gutterGirth ≤ 1200, 600, IF(gutterGirth ≤ 1800, 900, IF(gutterGirth ≤ 2400, 1200, "not available"))))
With current values
IF(2300 ≤ 600, 300, IF(2300 ≤ 1200, 600, IF(2300 ≤ 1800, 900, IF(2300 ≤ 2400, 1200, "not available")))) = 1200
Reference table rows used — Available Gutter Size — picked row (girth ≤ 2400 family)
| Girth (mm) | Width (mm) | Height (mm) | Area (mm²) |
|---|---|---|---|
| 2400 | 1200 | 600 | 720000 |
Formula ('Material cut list'!D24)
IF(gutterGirth ≤ 600, 150, IF(gutterGirth ≤ 1200, 300, IF(gutterGirth ≤ 1800, 450, IF(gutterGirth ≤ 2400, 600, "not available"))))
With current values
IF(2300 ≤ 600, 150, IF(2300 ≤ 1200, 300, IF(2300 ≤ 1800, 450, IF(2300 ≤ 2400, 600, "not available")))) = 600
Reference table rows used — Available Gutter Size — picked row (girth ≤ 2400 family)
| Girth (mm) | Width (mm) | Height (mm) | Area (mm²) |
|---|---|---|---|
| 2400 | 1200 | 600 | 720000 |
Formula ('Material cut list'!B26)
CEILING((endCapWidth × endCapHeight × endCapCount) / (sheetWidth × sheetLength), 0.5)
With current values
CEILING((1200 × 600 × 2) / (2300 × 1200), 0.5) = 1
Formula ('Material cut list'!B32)
CEILING(gutterLength / outletSpacing, 1) + 1
With current values
CEILING(100000 / 6300, 1) + 1 = 17
Formula ('Material cut list'!B34)
CEILING((outletCount × (spoutDiameter + 50) × (spoutDiameter + 50)) / (sheetWidth × sheetLength), 0.5)
With current values
CEILING((17 × (200 + 50) × (200 + 50)) / (2300 × 1200), 0.5) = 0.5
Formula ('Material cut list'!B39)
CEILING(sheetsForGutter + sheetsForEndCaps + sheetsForOutlets, 1)
With current values
CEILING(87 + 1 + 0.5, 1) = 89
Available Gutter Size (fixed reference table — the workbook's darkened side)
girth = width + 2 × height; area = width × height. The highlighted row is the end-cap size the nested IF picks for the current girth.
Flat sheet family | Girth (mm) | Width (mm) | Height (mm) | Area (mm²) |
|---|---|---|---|---|
600 | 600 | 400 | 100 | 40000 |
600 | 600 | 350 | 125 | 43750 |
600 | 600 | 300 | 150 | 45000 |
600 | 600 | 250 | 175 | 43750 |
600 | 600 | 200 | 200 | 40000 |
1200 | 1200 | 900 | 150 | 135000 |
1200 | 1200 | 800 | 200 | 160000 |
1200 | 1200 | 700 | 250 | 175000 |
1200 | 1200 | 600 | 300 | 180000 |
1200 | 1200 | 500 | 350 | 175000 |
1200 | 1200 | 400 | 400 | 160000 |
1800 | 1800 | 1400 | 200 | 280000 |
1800 | 1800 | 1300 | 250 | 325000 |
1800 | 1800 | 1200 | 300 | 360000 |
1800 | 1800 | 1100 | 350 | 385000 |
1800 | 1800 | 1000 | 400 | 400000 |
1800 | 1800 | 900 | 450 | 405000 |
1800 | 1800 | 800 | 500 | 400000 |
1800 | 1800 | 700 | 550 | 385000 |
1800 | 1800 | 600 | 600 | 360000 |
2400 | 2400 | 1800 | 300 | 540000 |
2400 | 2400 | 1700 | 350 | 595000 |
2400 | 2400 | 1600 | 400 | 640000 |
2400 | 2400 | 1500 | 450 | 675000 |
2400 | 2400 | 1400 | 500 | 700000 |
2400 | 2400 | 1300 | 550 | 715000 |
2400 | 2400 | 1200 | 600 | 720000 |
2400 | 2400 | 1100 | 650 | 715000 |
2400 | 2400 | 1000 | 700 | 700000 |
2400 | 2400 | 900 | 750 | 675000 |
2400 | 2400 | 800 | 800 | 640000 |
Legacy vs corrected — same inputs, side by side
Highlighted rows differ between the Excel-faithful (legacy) engine and the corrected engine. The Material Cut List sheet has no known defects, so its results are identical in both versions.
Result | Workbook cell | Legacy (Excel-faithful) | Corrected |
|---|---|---|---|
Total gutter cross section (450 table) | 'Gutter size'!G19 | 101700 mm² | 97050 mm² |
Total gutter cross section (400 table) | 'Gutter size'!G31 | 69320 mm² | 69320 mm² |
Total cross section area of gutter | 'Gutter size'!B18 | 101700 mm² | 97050 mm² |
Min average gutter height (incl. 25 mm freeboard) | 'Gutter size'!B22 | 251 mm | 241 mm |
Min gutter girth (for 1.0 mm thick Galvalume gutter) | 'Gutter size'!B27 | 977 mm | 957 mm |
Required cross section of RWDP (eave gutter) | 'Gutter size'!B36 | 33297.5 mm² | 33297.5 mm² |
Total RWDP cross sectional area required | 'Rwdp size'!B15 | 33297.5 mm² | 33297.5 mm² |
Required diameter, option 1 (1 RWDP) | 'Rwdp size'!B21 | 205.8888 mm | 205.9021 mm |
Required diameter, option 2 (2 RWDP) | 'Rwdp size'!B23 | 145.5854 mm | 145.5948 mm |
Required diameter, option 3 (3 RWDP) | 'Rwdp size'!B25 | 118.87 mm | 118.8777 mm |
Nominal diameter, option 1 (1 RWDP) | 'Rwdp size' — hand-typed vs computed | ≈ 200mmØ (hand-typed) | 250 mm |
Nominal diameter, option 2 (2 RWDP) | 'Rwdp size' — hand-typed vs computed | ≈ 150mmØ (hand-typed) | 150 mm |
Nominal diameter, option 3 (3 RWDP) | 'Rwdp size' — hand-typed vs computed | ≈ 125mmØ (hand-typed) | 125 mm |