XiKey™DESKTOP
العربيةGet XiLauncher

Learn

Task Guide: Analyzing Watershed Hydrology Telemetry with PivotTables

Alpha status (14 October 2026): XiSheet is planned and its public installer is not verified. This page describes the checked GridCraft foundation; XiKey integration is still in progress. Alpha is free today.

Scenario Overview

In this guide, you will import a raw telemetry CSV from 18 mountain watershed gauging stations, split composite sensor strings using Text to Columns, scrub duplicate packet transmissions, build a PivotTable and Combo Chart comparing monthly rainfall against reservoir inflow, and run Goal Seek to determine the spillway release rate required to hold reservoir capacity at 82%.

Step 1: Import Telemetry CSV and Clean Sensor Strings

  1. Open the raw logger export (watershed-telemetry-2026.csv) via File › Open.
  2. Column A contains composite station tags formatted as BASIN04|STN-12|ELEV1840. Insert two empty columns to the right of Column A, select A2:A4500, and click Data › Text to Columns:
    • Choose Delimited, set the delimiter character to | (pipe), and split into BasinID, StationCode, and ElevationBand.
  3. Because satellite uplinks occasionally transmit duplicate hourly packets, select the full table (Ctrl+Shift+8) and click Data › Remove Duplicates, checking only StationCode and TimestampUTC.

Step 2: Build a Multi-Basin PivotTable with Prepared Source Columns

  1. With any cell in the cleaned telemetry table selected, choose Insert › PivotTable and place it on a new worksheet named BasinSummary.
  2. In the PivotTable Fields side panel, configure the layout:
    • Rows: BasinID, then StationCode
    • Columns: Month
    • Values:
      • Precipitation_mm set to Sum (formatted as #,##0.0 "mm")
      • Streamflow_m3s set to Average (formatted as 0.00 "m³/s")
  3. Before creating the PivotTable, add a source column for runoff volume: precipitation in millimetres × catchment area in square kilometres × 1000 × runoff coefficient. Summarize that column with Sum. This is an illustrative estimate, not a validated reservoir model.
  4. Right-click any basin row to collapse or expand individual gauging stations, and enable Grand Totals and Subtotals.

Step 3: Insert a Combo Chart (Columns + Line)

  1. Select the monthly summary range on BasinSummary and press Alt+F1 (or click Insert › Chart › Combo).
  2. Configure the chart series in the Chart Inspector:
    • Series 1 (Total Precipitation mm): Clustered Column on the primary vertical axis.
    • Series 2 (Mean Streamflow m³/s): Line with Markers plotted on the Secondary Axis.
  3. Set the chart title to Upper Watershed Monthly Precipitation vs. Peak Streamflow and enable data labels on peak spring snowmelt months.

Step 4: Run Goal Seek on the Reservoir Storage Balance

  1. Switch to the ReservoirBalance sheet, and enter initial storage, inflow, evaporation and maximum capacity in cubic metres into B2, B3, B4 and B5; put the release rate in cubic metres per second in B9. Cell B12 calculates storage percentage:
    =(B2+B3-B4-(B9*86400*30))/B5
  2. To find the exact continuous spillway discharge (SpillwayRelease_m3s in cell B9) needed to keep end-of-month storage (B12) at exactly 82.0% (0.82), open Data › What-If Analysis › Goal Seek:
    • Set cell: B12
    • To value: 0.82
    • By changing cell: B9
  3. Click OK. Review the reported solution and confirm the balance independently; Goal Seek does not guarantee convergence for every model.

Sources