Excel for Hydrology – Halford Hydrology

Outline—NWRA class, June 11, 2019

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.

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.

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

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.

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.

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.

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.

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.

Shaded area for pumping with INT and MOD functions— 01_ShadedXY.xlsx.
Map features with XY chart, add XY locator, and sample map area from Goggle Earth—Example_SS-ModelDiagnostics.