Hydrologic Analysis with Excel

Outline— NGM, September 10-11, 2019

Class material has been revised to reflect inclusion of step-drawdown and coordinate conversion discussions. Material is broken into 2 zip files because of a 128 Mb limit on file size.

01_Excel-Navigation

• Configure Excel—Stop autosave, clear default directory, trusted locations, show developer tab in ribbon.
• Keyboard navigation and blocks of data, function keys, F2, F4, F8, F9, F11, and heavily used keyboard shortcuts such as, Ctrl+1.
• Status bar –Quick sampling functions—01_PRISM_4.0km.NV.xlsx.
• Paste special—Values, math, transpose—02_NamedRanges.xlsx.  
• Named ranges and data validation—02_NamedRanges.xlsx.

02_AQ-SingleWell_PUMPING+FlowLOG

1. Cooper-Jacob interpretation
2. Recovery analysis
3. Jacob—Lohman analysis of flowing wells
4. Reporting transmissivity
5. Complications from thermal expansion
6. Conventional interpretation of flow logs
7. Effects of vertical flow and turbulence on interpretation of flow logs

03_Excel-Plotting

• Create stacked bar and lines— 01_BarLine.xlsx.  
• Bar charts differentiate positive & negative— 02_Departure.xlsx.   
• XY Adding elements to charts –
  Copy-paste, Direct entry in formula bar— 03_XYmap.xlsx. 
• Specify labels from ranges— 04_FC_isotope_PAIRS.xlsx.
• Adding custom symbols to series by pasting— 05_sir2012-5196_app3.xlsm. 
• Digitize data with chart— 06_Digitize_from_Picture-in-a-Chart.xlsx.
• Compute perimeter and area of polygon— 07_XY_Perimeter+Area.xlsx.

04_AQ-SingleWell_SLUGS

• Uses and terminology
• Bouwer & Rice
• Oscillating or underdamped slug tests
• Interpreting recovery in tight formations

05_Excel-ConditionalFormatting

• Shade PRISM with generic flood—01_PRISM_4.0km.NV.xlsx.
• Controlled shading of PRISM—01_PRISM_4.0km.NV.xlsx.
• Introduce INT and MOD functions—02_CHV_StandardizedLOG.xlsm. 
• Build standardized log with remove duplicates, conditional formatting, MROUND, and  VLOOKUP function—02_CHV_StandardizedLOG.xlsm. 
• Apply functions in a scrap workbook to avoid stray named ranges. 

06_Excel-Text+Logic_Functions

• Introduce LEFT, VALUE, LEN, FIND, MID, RIGHT, IF, and ISNUMBER functions— 01_TextManipulate.xlsx. 
• Introduce SUBSTITUTE, TEXT, and HYPERLINK functions—01_TextManipulate.xlsx. 
• If statements and logic— 02_IFplus.xlsx.  
• Create scatter plot and categorize data — 03_TransmissivityDV3.xlsx.  
• Error handling with IF statements— 04_ErrorHandling.xlsx

07_SIM-Geology+Hydrology

• Analysis of relation between transmissivity estimates from aquifer-tests and predicted hydraulic conductivity distributions from geohydrologic frameworks in southern Nevada.
• Field-scale estimates of flow measurements, K, or transmissivity are compared to fracture counts, fault effects, thickness of geohydrologic units, and depth.
• Recommendations for designing and calibrating groundwater-flow models.

08_Excel-Regression

• Analyze specific conductance-chloride from Death Valley—
  Regress in chart and duplicate with equations—01_linearQW.xlsx.
• Analyze log-transformed specific capacity-transmissivity relations and
  demonstrate limitations of regression equations—
  02_Example_T-SC-DeathValley+OtherSCeqs.xlsx.

09_Excel-SeriesSEE-Sampler

• Demonstrate SeriesSEE, an Excel Add-In for analyzing time series or geophysical logs. 
• Install and illustrate example with class.

10_Excel-IF-Histograms

• Create Google Earth cover of PRISM output for Nevada—01_ARRAY_PRISM_4.0km.NV.xlsx & KMLmakeARRAY.GridPoly.v2.xlsm. 
• Dynamically subsample PRISM output with OFFSET and MATCH functions—01_ARRAY_PRISM_4.0km.NV.xlsx.
• Build histogram and sum cumulative precipitation volumes with COUNTIF and SUMIF functions—01_ARRAY_PRISM_4.0km.NV.xlsx.

11_ SIM-Geo2K_Calibration+ModelDiagnostics

• Illustrate creating and calibrating of groundwater model from southern Nevada
  • Distributing hydraulic conductivities with a geologic framework
  • Stress-response approach to calibrating flow models
  • Calibration criteria for steady-state model—Differences between simulated and measured water levels, water-level profiles, discharges, transmissivities. 
  • Calibration criteria for transient model—Differences between simulated and measured water-level changes and spring flows. Illustrate with hydrographs mapped in Google Earth (KMZ files).   
  • Mapping hydraulic-property distributions to KMZ files.  Qualitatively evaluate where estimates “Look funny.”
• Map features with XY chart, add XY locator, and sample map area from Google Earth—Example_SS-ModelDiagnostics.  

12_Excel-Solver

• Explain weighted sum-of-squares objective functions and parameter transforms necessary for using the Solver add-in— 00_ParameterEstimation+SOLVER.pptx.    
• 2-parameter Theis example—01_Theis_Solution+Sensitivity.xlsm.
• 3-parameter Theis with image well for analyzing aquifer test—02_TheisStream.xlsm.
• Estimate A, B, and C for a velocity index-stage relation—
  03_Velocity-Stage-Relation.xlsm.
• Differences between regularization with zones and prior information—04_RegularizeParameters-Compare.xlsm.

13_Excel-ReducingDATA

• Translate 3-column data to a table—Use remove duplicates, MATCH, and OFFSET functions—01_3columnQW.xlsx.
• Parse stream flow data with text to columns— 02_MuddyR_finish.xlsx. 
• Reduce data with pivot tables— 02_MuddyR_finish.xlsx.  
• Dynamically reduce with MATCH and OFFSET functions— 02_MuddyR_finish.xlsx. 

14_AQ-MultipleWell+DrawdownEstimation

• Discuss interpretation of multiple-well aquifer with analytical and numerical tools.
• Present examples of detecting small drawdowns with water-level modeling.
• Demonstrate effects of additional wells and information with 2016 and 2018 carbonate-rock aquifer tests at Long Canyon.

15_Excel-Piper+Stiff-Plots

• Present tool for plotting Piper diagram and mapping Stiff plots with example from Southern Nevada.   

16_Excel-AdvancedCharts

• Shaded area for pumping with INT and MOD functions— 01_ShadedXY.xlsx.  

17_Excel-UserFunctions_WaterBalance

• Simulate pit lake with simple model of precipitation, ET, and groundwater inflow. Simulated stage solved with forward differences. Demonstrates interpolation in look-up tables — 01_WaterBalance.xlsm.
• Replicate pit lake model with user-defined function that iteratively solves for lake stage. Demonstrates ease of applying user-defined functions — 01_WaterBalance.xlsm.
• Demonstrates user-defined functions for determining if a XY location is inside or outside of a polygon and translating coordinates between UTM and latitude-longitude — InOut+UTM2LL_Functions.xlsm.
• Demonstrate user-defined functions for simulating drawdowns from variable pumping rates by superimposing Theis solutions — Theis_SuperTime.xlsm.