Swimming Pool Water Chemistry & Saturation Index Calculator: pool chemical dosing calculator and saturation index log in Excel
Full description
For chief engineers, pool supervisors and general managers who run hotel pools and spas. You log daily tests (temperature, pH, free and total chlorine) and weekly balance tests (total alkalinity, calcium hardness, cyanuric acid, total dissolved solids). The workbook calculates the saturation index for every reading by the pool-industry factor method (pH + temperature, calcium, alkalinity and TDS factors) and by the full saturation pH equation, with alkalinity corrected for cyanuric acid. Each reading is checked against editable target ranges for pools and spas, including the free chlorine to CYA ratio. The dose plan turns the latest readings into product quantities: chlorine to reach target, breakpoint chlorination from combined chlorine, acid or soda ash from a carbonate and cyanurate buffering model, bicarbonate, calcium chloride, stabiliser and the partial drain needed to dilute CYA, TDS or hardness.
Save with a bundle: this workbook is also in the Hotel Engineering & MEP Pro Bundle, 25 workbooks for $59 instead of $319. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.
What is inside
- Pool_Water_Chemistry_Saturation_Index_Calculator.xlsx: 12 tabs, 38,813 formulas, 3 native charts, 15 integrity checks, 18-row Method tab
- Daily_Log: 1,200 rows with 1,095 illustrative sample readings (3 pools x 365 days)
- Weekly_Tests: 260 rows with 161 sample tests and Low/High flags for alkalinity, hardness, CYA and TDS
- Water_Balance engine: 19 columns per reading, factor method and full equation side by side
- Compliance: 11 columns per reading with flags for free chlorine, combined chlorine, pH, temperature, FC:CYA ratio and saturation index
- Dose_Plan: 85 formula rows per pool for 4 pool slots, with an override row for every reading
- SI_WhatIf: an 11 x 6 grid (pH x temperature) and a 6 x 6 grid (hardness x alkalinity) for any pool
- Monthly_Summary: 48 rows (12 months x 4 pool slots) with averages, compliance share and breaches
- Settings: 22 inputs with units, 4 derived constants, targets for 9 parameters by pool type, 11-row temperature factor table, pool register with volume calculation, 10 chemical products
- Dashboard: 9 KPI tiles, check status, latest status by pool and 3 charts on one page
- README.txt and LICENSE-and-Terms.txt
How it works
- On Settings, choose °C or °F, metres or feet and the saturation index method, then review the target ranges, pools and chemical products.
- Log daily tests on Daily_Log and weekly balance tests on Weekly_Tests, in date order.
- Read the flags on Compliance and the saturation index on Water_Balance; Monthly_Summary and the Dashboard show the trend.
- Before dosing, open Dose_Plan: it lists the quantity and cost of each product and the order to follow.
- Use SI_WhatIf to see how pH, temperature, hardness or alkalinity changes move the saturation index.
Details
- Format: one XLSX workbook
- Language: English
- Units: mg/L (ppm), mg/L as CaCO3, m³ and °C; °F and feet can be entered; dose quantities also shown in lb or US gal
- Software: Excel 2016 or later (Windows or Mac) or LibreOffice Calc; also opens in Google Sheets, where chart styling may differ
- No macros, no array formulas, no external links
- Delivery: instant download
Good to know
- Sample data is illustrative. Replace it with your own tests.
- Target ranges, chemistry constants, product strengths and costs are editable inputs. Verify local requirements and your supplier data.
- This is an operating tool, not engineering, health or safety advice. Follow product labels and safety data sheets.
- Dose quantities assume a closed water system; add acid or soda ash in parts and retest.
- Single-user license.
FAQ
Which saturation index method should I use?
Either. The factor method matches the printed pool charts; the full equation is the water-treatment form. Pick one on Settings; both are always calculated side by side.
Can I work in °F, feet and US gallons?
Yes for temperature and pool dimensions: set the units on Settings and the engine converts. Dose quantities show kg or L and lb or US gal.
Why is the acid dose different from a rule-of-thumb chart?
The workbook uses a carbonate and cyanurate buffering model with your actual alkalinity, CYA and pH, so high CYA or high alkalinity water needs more acid than a generic chart suggests.
How many pools can I track?
Four pools or spas, each with its own type, volume, products and targets. Use a second copy of the workbook for more.
Does it replace my local pool register?
No. It calculates and flags; your local code decides the required tests, frequencies and limits. Set the targets to match it.
