Prepared in cooperation with The Nature Conservancy

Environmental Flow Allocation and Statistics Calculator

U.S. Department of the Interior

U.S. Geological Survey

Open-File Report 2011–1166

Environmental Flow Allocation and Statistics Calculator

By Christopher P. Konrad

Prepared in cooperation with The Nature Conservancy

Open-File Report 2011–1166

U.S. Department of the Interior

U.S. Geological Survey
U.S. Department of the Interior

KEN SALAZAR, Secretary

U.S. Geological Survey

Marcia K. McNutt, Director

U.S. Geological Survey, Reston, Virginia: 2011

For more information on the USGS—the Federal source for science about the Earth,

its natural and living resources, natural hazards, and the environment, 

visit http://www.usgs.gov or call 1–888–ASK–USGS.

For an overview of USGS information products, including maps, imagery, and publications,

visit http://www.usgs.gov/pubprod

To order this and other USGS information products, visit http://store.usgs.gov

Suggested citation:

Konrad, C.P., 2011, Environmental flow allocation and statistics calculator: U.S. Geological Survey Open-File Report 

2011-1166, 46 p. 

Any use of trade, product, or firm names is for descriptive purposes only and does not imply 

endorsement by the U.S. Government.

Although this report is in the public domain, permission must be secured from the individual 

copyright owners to reproduce any copyrighted material contained within this report.
iii

Contents

Abstract ......................................................................................................................................................................... 1

Introduction.................................................................................................................................................................... 1

General Approach ......................................................................................................................................................... 2

Instructions .................................................................................................................................................................... 3

Required Components ............................................................................................................................................... 3

Input Files............................................................................................................................................................... 3

SETTINGS worksheet............................................................................................................................................ 4

SCENARIOS Worksheet ........................................................................................................................................ 4

EFLOWS Worksheet.............................................................................................................................................. 7

Description of Outputs................................................................................................................................................ 9

SUMMARY Worksheet..........................................................................................................................................10

Worksheets with Annual Values of Streamflow Statistics......................................................................................14

Other Output Worksheets......................................................................................................................................15

Optional Output Files.............................................................................................................................................16

Trouble-Shooting Guidance ......................................................................................................................................16

Summary ......................................................................................................................................................................17

Figures

Figure 1. Flow chart for using Environmental Flow Allocation and Statistics Calculator (EFASC) .............................. 18

Figure 2. Flow chart for Environmental Flow Allocation and Statistics Calculator (EFASC) calculations .................... 19

Conversion Factors

Inch/Pound to SI

Multiply By To obtain

Flow rate

cubic foot per second (ft3/s) 0.02832 cubic meter per second (m3/s)

SI to Inch/Pound

Multiply By To obtain

Flow rate

cubic meter per second (m3/s) 35.31 cubic foot per second (ft3/s)
1

Environmental Flow Allocation and Statistics Calculator

By Christopher P. Konrad

Abstract

The Environmental Flow Allocation and Statistics Calculator (EFASC) is a computer program 

that calculates hydrologic statistics based on a time series of daily streamflow values. EFASC will 

calculate statistics for daily streamflow in an input file or will generate synthetic daily flow series from 

an input file based on rules for allocating and protecting streamflow and then calculate statistics for the 

synthetic time series. The program reads dates and daily streamflow values from input files. The 

program writes statistics out to a series of worksheets and text files. Multiple sites can be processed in 

series as one run. EFASC is written in Microsoft® Visual Basic© for Applications and implemented as a 

macro in Microsoft Office Excel 2007®. EFASC is intended as a research tool for users familiar with 

computer programming. The code for EFASC is provided so that it can be modified for specific 

applications. All users should review how output statistics are calculated and recognize that the 

algorithms may not comply with conventions used to calculate streamflow statistics published by the 

U.S. Geological Survey.

Introduction

Streamflow in most rivers and streams varies considerably over time at scales ranging from less 

than 1 day to decades and longer. The temporal variability and sequencing of streamflow influences 

biophysical processes ranging from sediment transport to fish migration. Streamflow variability also 

regulates water availability for human uses. Because of the high degree of temporal variability of 

streamflow and its broad importance to water availability for river ecosystems and for people living in 

those systems, various streamflow characteristics must be represented in applications of hydrologic 

analysis to social and environmental issues. These applications include assessing the availability and 

reliability of water supplies; modeling of environmental factors influencing biological populations and 

communities; functional classification of stream types; and identification of ecologically significant 

impacts of hydrologic alteration resulting from water management, land use, or climate change. No one 

suite of statistics completely depicts the streamflow characteristics that are important across this range 

of applications and across landscapes with varied hydrology and ecosystems.

The computer program “Environmental Flow Allocation and Statistics Calculator” (EFASC, 

available at http://pubs.usgs.gov/of/2011/1166/EFASC_R1.xlsm) was developed to calculate a range of 

statistics for a daily time series of streamflow and to generate synthetic time-series generated from userspecified rules for allocating and protecting streamflow. The program is intended for research 

applications and, thus, generates statistics that represent a wide variety of streamflow characteristics. 

The source code is written in Microsoft® Visual Basic® 6.5 for Applications and is executed as a macro 

in Microsoft Office Excel 2007® (MS Excel). The source code is provided in this report (Appendix A)

can be copied and executed in MS Excel. Users may also modify or incorporate new algorithms and 

statistics of interest. EFASC allows batch processing of scenarios that can represent different sites, time 
2

periods, or protection/allocation rules. EFASC will write the hydrologic time series for any scenario to 

an ASCII text file permitting further analysis with other hydrologic analysis tools.

EFASC allows a user to specify options for how statistics are calculated. Algorithms for 

calculating some statistics (e.g., streamflow exceedences) are known to use different conventions than 

algorithms used to calculate streamflow statistics published by the U.S. Geological Survey (USGS). 

Because of user options and different conventions, the values of statistics calculated by EFASC are not 

intended to supersede values published by the USGS including those of the National Streamflow 

Statistics Program. Deference should be given to streamflow statistics published by the USGS rather 

than output from EFASC. Any output from EFASC used outside of a research application should be 

verified by the user to assure the statistical calculation follows the appropriate convention for the 

specific application of interest.

General Approach

EFASC calculates statistics from a daily time series. It was developed for daily series of mean 

streamflow but could be applied to other daily time series (maximum streamflow, temperature, stage). 

EFASC will calculate statistics either directly from a record of daily streamflow values or from a 

synthetic daily time series that is derived from a streamflow record plus user-defined rules for allocating 

and protecting streamflow. Either case is referred to as a “scenario.” Each scenario is based on an input 

file of daily streamflow values.

A user must prepare input data, enter settings, and specify scenarios (fig. 1). EFASC will read 

the input file and, depending on user specifications, process daily streamflow for a subset period, 

convert the streamflow to different units, derive a new daily time series to represent streamflow after 

allocation of water to out-of-stream consumptive uses, and calculate statistics for either the input or 

derived time series (fig. 2). To compare, for example, an unmodified to modified time series, the user 

would specify two scenarios—the first scenario with no allocation of water to out-of-stream uses and 

the second scenario with specified allocation and/or protection rules.

EFASC will generate a synthetic time series based on rules for allocating and protecting 

streamflow to simulate the outcome of different types of administrative rules for managing water 

resources. Two types of allocations are allowed: priority allocations made before environmental flow 

protection rules are applied and additional allocations made after environmental protection rules are 

applied. Priority allocations can be used to represent senior water rights that allow a user to withdrawal 

water regardless of junior rights or in-stream flow standards established after the senior right. Additional 

allocations can be used to represent withdrawals permitted only when in stream flow standards are 

achieved or a withdrawal subject to a by-pass requirement that protects in-stream flow.

The user can specify unique rules for both types of allocations and the environmental flow 

protection standards. Neither allocation nor protection rules do not have to be specified, but the user 

should consult the instructions below to understand the consequences of various combinations of rules. 

The user can vary allocations and environmental flow standards during a year by defining “bioperiods” 

on the SCENARIO worksheet using Julian day and, then, specifying allocations and standards for each 

bioperiod. A bioperiod could be, for example, a typical low-flow period beginning on August 1 (day

213) and ending on September 15 (day 258). 
3

Instructions

EFASC must be executed as a macro using MS Excel. Macro functionality must be enabled in 

MS Excel. The code has both computational algorithms and instructions for computations in MS Excel.

The keys “ctrl”+ “e” can be used to run the macro from the MS Excel file.

Components for running the macro

There are four components for running the EFASC macro: (1) input files with hydrologic timeseries data, (2) settings specified on the worksheet SETTINGS, and (3) scenarios specified on the 

worksheet SCENARIOS, and (4) any specification of out-of-stream allocations or environmental flow 

protection standards to generate synthetic time series. Each component is described below.

Input Files

Input files must be formatted as ASCII text with date (year, month, and day) and streamflow (or 

other attribute such as stage represented by a positive numeric value) on one line for each day. The user 

may specify the number of header lines to skip before reading data. The four required data fields (year, 

month, day, and streamflow) should be delimited by commas, spaces, dashes, or semicolons, but the 

fields may appear in any order. The column number for each of the four fields must be known and 

specified for each scenario (see SCENARIOS below). The column numbers can be verified by opening 

up a file in MS Excel and checking to see which columns correspond to each data field. MS Excel will 

automatically parse text data for files with some extensions (e.g., *.csv). If data from these files are not 

parsed correctly, the file can be renamed without the extension.

Input data may have gaps, in which case the missing values must be expressed with no line for 

that day, a blank value for streamflow on the line with date, or “NaN” for the streamflow value. Ice is a 

common reason for gaps in streamflow records, so daily values of “Ice” are treated as missing values. 

Other text values will cause an error and should be edited before processing. Zero values will be treated 

as days with no streamflow and will be used in calculations. Days with missing values are excluded 

from calculations, which can result in misleading statistics. The user can limit the analysis to years with 

365 daily values on the SETTINGS worksheet

The default setting is to treat a dash ("-") as a delimiter, for example, to parse year, month, and 

day from a date string. If a negative value is used to indicate missing streamflow data, then “-“ can be 

removed from the list of delimiters types used when opening QFILE(l), but dates cannot be formatted 

with dashes (e.g., 2010-10-01) as the program will not parse out the year, month, and day into separate 

columns. To remove “ - " from the list of delimiters, the command used for opening the data files should 

be modified so that OtherChar:="-" is instead OtherChar:=False. In this case, the current command,

Workbooks.OpenText Filename:=QFILE(l), _

Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _

ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _

True, Space:=True, Other:=True, OtherChar:="-", FieldInfo:=Array( _

Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True

would be modified to

Workbooks.OpenText Filename:=QFILE(l), _

Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _

ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _

True, Space:=True, Other:=True, OtherChar:=False, FieldInfo:=Array( _

Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True .
4

Among common formats, the EFASC macro will read a "two-column" text file (fig. 1) or files 

generated by the U.S. Geological Survey National Streamflow Information System (NWIS) using the 

default format circa 2010, where lines starting with the “#” character are skipped. Files generated by 

NWIS have either daily mean streamflow in column 6 or in column 10 when daily maximum and 

minimum streamflow are also included in the file. The user must specify the column number for daily 

mean streamflow for each file on the SCENARIOS worksheet. The user should review qualification 

codes for the each record. Estimated values (qualification code “E”) will be processed unless the user 

manually deletes these values or limits the period of analysis to years without estimated values.

SETTINGS worksheet

The user must enter four values on the SETTINGS worksheet in cells B2 through B5. The 

settings are applied to all scenarios that can be modified by a user: (1) a conversion factor to change the 

units of streamflow so that output has different units than the input, (2) the minimum number of water 

years (October 1–September 30) in the range of years specified for each scenario required for 

processing a site, (3) minimum number of days in each water years for that water year to be included in 

calculations, and (4) the first month of the “low-flow year” used to calculate annual low-flow statistics.

The conversion in cell B2 is applied to all output values of streamflow. If the period of analysis is less 

than the minimum number of years in cell B3, statistics will not be calculated for the scenario. The user 

can include years with missing data by entering a value less than 365 days in cell B4. Annual low-flow 

statistics should be calculated for over 12 months beginning with a month unlikely to have low flows. 

Depending on location, low-flow statistics are commonly calculated based on climate years beginning in 

April (enter 4 in cell B5), calendar years beginning in January (enter 1 in cell B5), or water years 

beginning in October (enter 10 in cell B5). Low flow years beginning from June through December are 

assigned the number of the next calendar year. If the low-flow year begins in a month other than 

October, there will be less than 12 months in the first and last low flow years. 

EFASC is not intended to be used to analyze seasonal or partial records, but records with some 

missing values can be processed by setting the minimum number of days per year accordingly. Missing 

data are not equivalent to no-flow values, so seasonal records that excludes periods of no-flow will have 

different values than the same record that includes no-flow periods for any statistics based on time (e.g., 

flow duration, medians, means). No high-flow or low-flow periods will be represented during such 

gaps. The monthly time series of mean streamflow (written to the worksheet MONTHLY_QMEAN) 

will not include missing months, but it will include months with missing days. In the case of missing 

days, streamflow for that month will be averaged only over the days with values.

SCENARIOS Worksheet

The EFASC macro runs a series of user specified scenarios. The user must enter a text string 

(e.g., a letter) that identifies the scenario, the full name of the file with the streamflow record, a starting 

and ending year, specifications about the input files, and options for outputs and processing. Each 

scenario is entered as a separate line on the worksheet SCENARIOS. The macro will process each 

"scenario" listed on the SCENARIO worksheet starting with line 3 until there is a blank row. Additional 

scenarios following a blank row will not be processed, but can be used to as a record of previous 

scenarios. There are 29 columns on the SCENARIOS worksheet for a user to specify the details of each 

scenario:
5

Column A. Scenario

This is a character code that is appended to the file name to identify output for the scenario. The 

same characters can be used for multiple scenarios (e.g., “A” could designate scenarios using 

streamflow records while “B” could be used to designate scenarios with allocation of water to out-ofstream uses).

Column B. File with streamflow data

Exact name of file including any extension (.txt, .csv, etc.)

Column C. Start year

First year to process, may pre-date streamflow record (e.g., 1900)

Column D. End year

Last year to process, may post-date streamflow record (e.g., 2100)

Column E. NWIS file (0 -no, 1-yes)

Enter 1 to indicate that the file was generated by the National Streamflow Information System 

using standard format circa 2010, otherwise enter 0.

Column F. Qmean column number (6-NWIS daily mean streamflow, 10-NWIS daily max, min, mean streamflow)

Indicates the field (column) number for daily mean streamflow according to the specified rule 

for delimiting fields.

Column G. Year column number

Indicates the field (column) number for the calendar year according to the specified rule for 

delimiting fields.

Column H. Month column number

Indicates the field (column) number for the month according to the specified rule for delimiting 

fields.

Column I. Day column number

Indicates the field (column) number for the day of the month according to the specified rule for 

delimiting fields.

Column J. Number of header lines

Indicates the number of header lines preceding streamflow data.

Column K. Type of threshold for high-flow event (1 – streamflow rate, 2 – fraction of time exceeded)

Option for specifying the type of threshold used to define high-flow events. The threshold can be 

specified in terms of a flow rate using the same units as output (after any conversion applied) or as a 

fraction of time (0–1) that the threshold streamflow is exceeded (e.g., the streamflow exceeded 0.1 of 

the time).
6

Column L. High-flow threshold (rate or fraction)

Either the flow rate of the threshold or fraction of time the threshold is exceeded depending on 

option selected in column L.

Column M. Type of threshold for low-flow periods (1 – streamflow rate, 2 – fraction of time exceeded)

Option for specifying the type of threshold used to define low-flow periods. The threshold can 

be specified in terms of the flow rate using the same units as output (after any conversion applied) or as 

a fraction of time that the threshold streamflow is exceeded (e.g., the streamflow exceeded 0.9 of the 

time).

Column N. Low-flow threshold (rate or fraction)

Either the flow rate of the threshold or fraction of time the threshold is exceeded depending on 

option selected in column N.

Column O. Environmental flow calculations (0 - no, 1 - yes)

Option used for scenarios where a synthetic time-series of streamflow will be generated based on 

specified allocation and protection rules. Enter 0 to calculate statistics based on the daily streamflow 

record from the input file. Enter 1 to generate a synthetic series using the allocation and protection rules 

specified on the EFLOWS worksheet.

Column P. Bioperiod 1: the starting day of the year for each bioperiod that can represent months, times of the year 

associated with distinct life stages for biota , irrigation seasons, or other intra-annual patterns; if January 1 is the start of 

bioperiod 1, enter 1

Bioperiods are used to divide a year into continuous periods—each with distinct prior 

appropriation, environmental flow standards, or additional allocation allowances. Bioperiods are defined 

by the day of the year that they begin. Specify bioperiod days for a non-leap year. EFASC will adjust 

the beginning of bioperiods for leap years. A bioperiod will continue to the end of the year or to the day 

before the next bioperiod begins. For example, if the only bioperiod of interest is from August 1 to 

September 15, then three bioperiods should be specified even if only one of the bioperiods is used. 

Bioperiod 1 would begin on day 1, bioperiod 2 would begin on day 213, and bioperiod 3 would begin 

on day 259. There must be at least one bioperiod beginning on January 1 (day 1).

Column Q. Bioperiod 2: if February is the start of bioperiod 2, enter 32

No entry is required in columns S through AC if there is only one bioperiod.

Column R. Bioperiod 3: if March 1 is start of bioperiod 3, enter 60

Column S. Bioperiod 4: if April 1 is start of bioperiod 4, enter 91

Column T. Bioperiod 5: if May 1 is start of bioperiod 5, enter 121

Column U. Bioperiod 6: if June 1 is start of bioperiod 6, enter 152

Column V. Bioperiod 7: if July 1 is start of bioperiod 7, enter 182
7

Column W. Bioperiod 8: if August 1 is start of bioperiod 8, enter 213

Column X. Bioperiod 9: if September 1 is start of bioperiod 9, enter 244

Column Y. Bioperiod 10: if October 1 is start of bioperiod 10, enter 274

Column Z. Bioperiod 11: if November 1 is start of bioperiod 11, enter 305

Column AA. Bioperiod 12: if December 1 is start of bioperiod 12, enter 335

Coumn AB. Write daily streamflow to worksheet QDAILY (0-no, 1-yes)

Column AC. Write daily flow duration and monthly streamflow files (0-no, 1-yes)

Option to generating a time-series of monthly statistics and the cumulative distribution 

streamflow exceeded for each 0.1 percent of the period of analysis (“flow-duration curve”).

Column AD. Date to start calculating 100-day statistics (YYYY-MM-DD)

Enter a date using numeric format (YYYY-MM-DD) for the first day of a 100-day period for 

calculating a subset of statistics. The 100-day statistic can be used, for example, for analysis of 

streamflow associated with a field sample. This field is optional and can be left blank.

EFLOWS Worksheet

No specifications are required on this worksheet if the environmental flow calculation option in 

column O on worksheet “SCENARIOS” for the scenario is”0”. Otherwise, the user must specify rules 

for allocating and protecting streamflow on this sheet.

The synthetic series is calculated in two steps: the specified priority allocation is subtracted from 

recorded daily streamflow (from the input file), any remaining streamflow is protected up to the 

specified environmental flow standard at which point any supplementary allocation is subtracted. In this 

way, priority allocation takes precedent over the environmental flow protection, while the 

environmental flow protection takes precedence over additional allocation. If additional allocations are 

not specified, any streamflow remaining after priority allocation will be protected. If additional 

allocations are specified without an environmental flow standard, they will act like priority allocations 

(that is, the specified amount of water will be allocated if it is available). 

Copy columns A-D of line 2 on EFLOWS workshop down for each scenario. These contain 

formulas with reference information for the scenarios. 

Column A. Scenario

Column B. File with streamflow data

Column C. Start year

Column D. End year
8

Fill out columns E-AP for any scenario that has priority allocation, environmental flow 

protection standards, or additional allocations. These values can be specified in various ways. If 

streamflow rates are used, verify that the specified values have the same units as streamflow after any 

conversion noted on the SETTINGS worksheet.

Column E.

An integer code corresponding to a streamflow statistic that provides a basis for priority 

allocations: 

0 - no priority allocation; 

1 - daily streamflow; 

2 - mean streamflow; 

3 - streamflow exceeded for specified percentile of analysis; 

4 - streamflow exceeded for a specified fraction of bioperiod; 

5 - mean streamflow for bioperiod; 

6 - estimate of 7Q10 based on 10th percentile of the annual (low-flow year) series of 7-day 

minimum streamflow; or 

7 - fixed rate to be entered into each month. 

Enter the appropriate integer code.

Column F. Priority allocation in bioperiod 1

Enter the priority allocation rate in bioperiod 1 using the same units as output (that is, units after 

any conversion specified on the SETTINGS worksheet). The specified rate will be subtracted from daily 

streamflow during the bioperiod before any environmental flow protection standard is applied.

Columns G - Q. Priority allocation in bioperiod 2...Priority allocation in bioperiod 12

Enter a rate for each bioperiod specified on the SCENARIOS worksheet.

Column R. ELOW basis

An integer code corresponding to a streamflow statistic that provides a basis for environmental 

flow (EFLOW) protection standard: 

0 - no EFLOW protection; 

1 - daily streamflow; 

2 - mean streamflow; 

3 - streamflow exceeded for specified percentile of analysis; 

4 - streamflow exceeded for a specified fraction of bioperiod; 

5 - mean streamflow for bioperiod; 

6 - estimate of 7Q10 based on 10th percentile of the annual (low-flow year) series of 7-day 

minimum streamflow; or 

7 - fixed rate to be entered into each month. 

Enter the appropriate integer code.

Column S. ENVIRONMENTAL FLOW standard in bioperiod 1

Fraction of the streamflow statistic specified in previous column or fixed rate for the 

environmental flow protection standard for each period. Enter a fraction or streamflow rate for 

bioperiod 1 (specified on the SCENARIOS worksheet).
9

Columns T– AD. ENVIRONMENTAL FLOW standard in bioperiod 2....EFLOW standard in bioperiod 12

Enter a fraction or streamflow rate for each bioperiod specified on the SCENARIOS worksheet.

Column Ae. Additional allocation basis

An integer code corresponding to a statistic used for additional allocation after priority allocation 

and environmental flow protection: 

0 - no additional allocation; 

1 - daily streamflow; 

2 - mean streamflow for the period of analysis; 

3 - streamflow exceeded for specified percentile of record; 

4 - streamflow exceeded for specified fraction of bioperiod; 

5 - mean streamflow for bioperiod; 

6 - estimate of 7Q10 based on 10th percentile of the annual (low-flow year) series of 7-day 

minimum streamflow; or 

7 - fixed rate to be entered into each month. 

To allocate all streamflow above eflow standard, use code 1 and enter 1 (i.e., 100 percent) for 

each bioperiod. Enter the appropriate integer code.

Column Af. Additional allocation in bioperiod 1

Fraction of statistic used for or fixed rate of the additional out-of-stream allocation for each 

month or bioperiod. Enter a fraction or streamflow rate for bioperiod 1.

Columns AG – AQ. Additional allocation in bioperiod 2...Additional allocation in bioperiod 12

Enter a fraction or streamflow rate for each bioperiod specified on the SCENARIOS worksheet.

Description of Outputs 

EFASC generates a series outputs in separate worksheets within the MS Excel file and has 

options for writing a monthly time series for the period of record of statistics (maximum, mean, median, 

and minimum streamflow; number of high-flow days; and number of low-flow days) and the cumulative 

distribution of streamflow to separate files for each scenario. Seven types of outputs are written directly 

to the EFASC.xlm file: 

1. A summary on the worksheet SUMMARY; 

2. Annual (water year or low-flow year) values of 18 different statistics (green tabbed worksheets); 

3. Summaries of monthly statistics (MONTHQ and MONTH EVENTS); 

4. Environmental flow standards and additional allocation by bioperiod (BIOPERIOD); 

5. Streamflow statistics for the 100 days prior to the specified date (100-DAY STATS); 

6. A time series of monthly mean streamflow (MONTHLY QMEAN); and 

7. Daily streamflow (QDAILY). 

The scenario label appears in the first column of each output worksheet. The output for any 

scenario will be written to the same row on each of these sheets unless a worksheet contains output from 

a previous run, in which case the new output will be appended below the old output. If EFASC is run 

sequentially without deleting output from previous runs, output in a given row of any of the output 

sheets will not correspond to the scenario listed in the same row on the SCENARIOS worksheet or to 
10

the allocation/protection rules listed on the EFLOWS worksheet. In this case, the user will have to keep 

track of the scenarios and associated allocation/protection rules, for example, by saving the scenarios in 

order on the SCENARIOS worksheet below a blank row, which will prevent EFASC from re-running 

the scenarios.

The names of hydrologic statistics can be ambiguous. Based on USGS convention for naming 

hydrologic statistics, each modifier applies to the next one in order. For example, “annual mean 

streamflow” is mean streamflow for a given year while “mean annual streamflow” is the mean of annual 

streamflows. Terminology for monthly statistics is particularly awkward because statistics can be 

calculated for a specific month (mean streamflow for April 2001) to produce a time series of mean 

streamflow (“monthly mean streamflow”) or for each month of a year (e.g., mean January streamflow) 

to represent seasonal patterns (“mean monthly streamflow”). 

SUMMARY Worksheet

EFASC writes output values directly to columns A through N of the SUMMARY worksheet. It 

copies formulas from row 2 of columns O – AL to summarize output from other worksheets. Formulas 

can be entered into row 2 of three additional columns (AM, AN, AO) to calculate additional summary 

statistics. They will be copied automatically for each scenario. Some of these statistics cannot be 

calculated for scenarios with one year or no-flow days.

Column A. Scenario

The name of the data file with the scenario identifier appended.

Column B. Number of years

Number of water years in the period of analysis.

Column C. Mean streamflow

Mean streamflow for the period of analysis: ( Σd=1to D Qd)/D, where D is the number of days in 

the period of analysis.

Column D. Q01 

Streamflow equaled or exceeded 1 percent of the period of analysis. Note that the convention for 

calculating streamflow exceedences is different than percentiles. The streamflow equaled or exceeded 1 

percent of the time is calculated by ranking all daily streamflows from highest to lowest and counting 

down to the streamflow in place d/100. If d/100 is not an integer, then the value is interpolated between 

the integer portion of d/100 and the next (smaller) value.

Column E. Q10 

Streamflow equaled or exceeded 10 percent of the period of analysis.

Column F. Q25 

Streamflow equaled or exceeded 25 percent of the period of analysis.

Column G. Q50 

Streamflow equaled or exceeded 50 percent of the period of analysis.
11

Column H. Q75 

Streamflow equaled or exceeded 75 percent of the period of analysis.

Column I.Q90 

Streamflow equaled or exceeded 90 percent of the period of analysis.

Column J. Q99 

Streamflow equaled or exceeded 99 percent of the period of analysis.

Column K. 50th percentile of absolute value of percent daily change

The median absolute value of percent daily change in streamflow. Percent daily change is 

calculated for each day (d); (Qday1 – Qday0)/Qday0. No-flow days are not included in the calculation. For 

odd-numbered series, the median is determined by ranking all values for day 1 to day d in a series from 

highest to lowest and selecting the value at the d/2+1 place. For even-numbered series, the value is the 

midpoint between the integer portion of d/2+1 and the next (smaller) value.

Column L. Coefficient of variation of daily streamflow

Standard deviation of daily streamflow divided by mean streamflow.

Column M. Coefficient of variation of log-transformed daily streamflow 

Coefficient of variation of log-transformed streamflow: (10 (Var(Log(Q))-1)0.5 where Var(Log(Q)) is 

the variance of the series of log-transformed daily values. No-flow days are not included in the 

calculation.

Column N. Coefficient of variation of log-transformed annual maximum streamflow 

Coefficient of variation of log-transformed streamflow: (10 (Var(Log(Qmax))-1)0.5 where 

Var(Log(Qmax)) is the variance of the annual (water year) series of log-transformed maximum daily 

values. Years when Qmax = 0 are not included in the calculation.

Column O. Median annual maximum streamflow (Qmax)

Median annual (water year) maximum daily streamflow.

Column P. Qmax/Q50

Median annual (water year) maximum daily streamflow (Qmax) divided by median streamflow 

(Q50).

Column Q. Median annual number of high-flow events 

Median of the annual (water year) number of periods when streamflow exceeds the high-flow 

threshold.

Column R. Median annual duration of the longest high-flow event 

Median number of days of the annual (water year) longest period of time when streamflow 

exceeds the high-flow threshold.
12

Column S. Number of months with annual probability >0.5 of having a high-flow event

Number of months (October, November, December, ... , September) that had flows exceeding 

the high-flow threshold in more than one-half of the water years of the period of analysis. Values range

from 0 to 12. 

Column T. Mode of highest streamflow month

Most common month of maximum monthly mean streamflow. The time series of monthly mean 

streamflow is calculated for the period of analysis. The month with the highest monthly mean is 

determined for each year. A value of 1 indicates that January was most often the month with the highest 

mean streamflow for the year. 

Column U. Median annual minimum streamflow (Qmin)

Median annual (low-flow year) minimum daily streamflow.

Column V. Qmin/Q50

Median annual (low-flow year) minimum daily streamflow (Qmin) divided by median 

streamflow (Q50).

Column W. Coefficient of variation of annual minimum streamflow 

Standard deviation of the annual (water year) series of minimum daily streamflow divided by 

mean annual minimum daily streamflow.

Column X. Median annual number of low-flow periods 

Median of the annual (low-flow year) number of periods when streamflow is less than the lowflow threshold.

Column Y. Median annual duration of the longest low-flow period

Median number of days of the annual (low-flow year) longest period of time when streamflow is 

less than the low-flow threshold.

Column Z. Number of months with annual probability >0.5 of having a low-flow period

Number of months (October, November, December, ... , September) that had flows less than the 

low-flow threshold in more than one-half of the low-flow years of the period of analysis. Values range 

from 0 to 12. 

Column AA Mode of lowest streamflow month

Most common month of minimum monthly mean streamflow. The time-series of monthly mean 

streamflow is calculated for the period of analysis. The month with the lowest monthly mean is 

determined for each year. A value of 1 indicates that January was most often the month with the lowest 

mean streamflow for the year. 

Column AB. Fraction of years with no-flow days

Fraction of years with days that had no flow.
13

Column AC. Mean number of no-flow days in years with no-flow days

The total number of days with no flow averaged over years with no-flow days. Years with 

streamflow on all days are not included in the calculation. No-flow days are calculated for each lowflow year.

Column AD. Coefficient of variation of annual streamflow

Standard deviation of mean annual (water year) streamflow divided by mean streamflow.

Column AE. Storm-flow recession coefficient (SFR)

The median annual (water year) value of the 10th percentile of percent daily change for days 

when streamflow was steady (no change) or receding. The 10th percentile represents a relatively fast 

recession rate indicative of storm-flow recession because the values are negative.

Column AF. Base-flow recession coefficient (BFR)

The median annual (water year) value of the median percent daily change for days when 

streamflow was steady (no change) or receding. The median represents a typical recession rate 

indicative of base-flow conditions.

Column AG. Maximum monthly streamflow 

The maximum of monthly mean streamflow (the maximum of means for January, February, 

March,..., December).

Column AH. Minimum monthly streamflow 

The minimum monthly mean streamflow (the minimum of means for January, February, 

March,..., December).

Column AI. Maximum coefficient of variation (CV) of monthly streamflow 

The coefficient of variation of annual values of mean streamflow for each month (January, 

February, March,..., December) is calculated and the maximum value is used. The maximum value 

represents the month with the lowest predictability in streamflow magnitude.

Column AJ. Minimum coefficient of variation (CV) of monthly streamflow

The coefficient of variation of annual values of mean streamflow for each month (January, 

February, March,..., December) is calculated and the minimum value is used. The minimum value 

represents the month with the highest predictability in streamflow magnitude.

Column AK. Normalized range of monthly streamflow

Median annual (water year) value of the difference between maximum monthly mean 

streamflow and minimum monthly mean streamflow divided by annual mean streamflow. If the annual 

mean streamflow is zero, then the normalized range of monthly streamflow is zero.

Column AL. LOG(Q10/Q90)

Logarithm (base 10) of the streamflow exceeded 10 percent of the time divided by streamflow 

exceeded 90 percent of the time. If Q90 = 0, this field will be blank.
14

Column AM. Hydroperiod

Fraction of period of analysis when streamflow was greater than zero.

Worksheets with Annual Values of Streamflow Statistics

Annual values of 18 streamflow statistics are written to the green-tabbed worksheets that are 

labeled by the statistic. The values are calculated for each water year or low-flow year. The 

corresponding water year is listed on the worksheet “YEAR” in the same column as the annual value of 

a statistic. The annual values are used to calculate summary statistics (e.g., median annual low 

streamflow) in the SUMMARY worksheet and can used to evaluate inter-annual variability and trends 

in streamflow. The value in any given cell (e.g., row 2, column C) corresponds to the water year (or 

low-flow year) on the worksheet “YEARS” with the same cell address.

YEAR 

List of water years in the period of analysis.

QMAX

Maximum daily streamflow for each water year.

QMEAN

Mean streamflow for each water year.

QMIN

Minimum daily streamflow for each low-flow year.

Q10

Streamflow exceeded 10 percent of the time for each water year.

Q50

Streamflow exceeded 50 percent of the time for each water year.

Q90

Streamflow exceeded 90 percent of the time for each year.

NO_FLOW_DAYS

Number of days with no streamflow for each low-flow year.

HIGH_NUM

The number of high-flow events for each water year.

HIGH_DUR

The duration in days of the longest high-flow event for each water year.

LOW_NUM

The number of low-flow periods for each low-flow year.
15

LOW_DUR

The duration in days of the longest low-flow period for each low-flow year.

PDC50

The median absolute value of percent daily change in streamflow for each water year.

CV_LOG_DAILY

The coefficient of variation of log-transformed daily streamflow for each water year.

MONTH MAX

The month with the highest monthly mean streamflow for each water year (1 - January, 12 -

December).

MONTH MIN

The month with the lowest monthly mean streamflow for each water year (1-January, 12-

December).

VARMONTH

The difference between maximum monthly mean streamflow and minimum monthly streamflow 

divided by annual mean streamflow for each water year.

BFR

The base-flow recession coefficient (BFR) for each water year. BFR is median percent daily 

change for days when streamflow was steady (no change) or receding. The median represents a typical 

recession rate indicative of base-flow conditions.

SFR

The storm-flow recession coefficient (SFR) for each water year. SFT is the 10th percentile of 

percent daily change for days when streamflow was steady (no change) or receding. The 10th percentile 

represents a relatively fast recession rate indicative of storm-flow recession because the values are 

negative.

Other Output Worksheets

MONTHQ

MONTHQ has monthly mean streamflow (e.g., mean of annual values of October streamflow), 

the coefficient of variation of monthly mean streamflow (e.g., the standard deviation of the annual 

values of October mean streamflow divided by mean October streamflow), and median annual monthly 

mean streamflow (median of annual values of October mean streamflow).

MONTH_EVENTS 

MONTH_EVENTS has four monthly statistics: (1) median annual duration of the longest highflow event, (2) fraction of years with high streamflows in each month, (3) median annual duration of the 

longest low-flow period, and (4) fraction of years with low streamflows in each month.
16

MONTHLY_QMEAN

A time series of monthly mean streamflow for each month in the period of analysis.

BIOPERIODS

The priority allocation, environmental flow standard and additional allocation for each 

bioperiod.

100_DAY_STATS

Statistics calculated for a 100-day period prior to the date specified for the scenario on the 

SCENARIOS worksheet. This can be used, for example, to calculate streamflow characteristics prior to 

collection of biological samples or a site survey.

QDAILY

Daily streamflow values for each scenario are written to this worksheet. For scenarios without 

appropriations or additional allocations, these values are the same as the input data with any conversion. 

For other scenarios, appropriations and allocations are subtracted from the daily values according to the 

rules specified on the EFLOWS worksheet.

Optional Output Files

In the SCENARIOS worksheet, the user has the option of generating a table of the cumulative 

distribution of streamflow (“flow duration”) and a table of monthly values of minimum, median, mean, 

and maximum streamflow, frequency of high-flow events, and duration of low streamflows. The 

cumulative distribution of streamflow is written to the file [input data file name and 

scenario]_FLOWDUR.CSV and monthly values are written to the file [input data file name and 

scenario]_MONTH.CSV. Delete these files if you re-run the macro you must overwrite the old version 

of each file when prompted. Otherwise the program will terminate.

Verification of program

The program can be verified using the file 

http://pubs.usgs.gov/of/2011/1166/EFSAC_R1_verification.xlsm. and 

http://pubs.usgs.gov/of/2011/1166/12189500.txt. The verification file has the results of running EFASC 

using records for water years 1996 to 2000 for USGS station 12189500 (Scenario A). EFASC can be rerun using the same period of analysis (Scenario B). The results for Scenario A and B should be 

identical.

Trouble-Shooting Guidance

A user should consult the flow chart (fig. 1) as an overview on how to prevent errors when 

running EFASC. There are a number of issues that will prevent EFAC from completing a run. Some 

typical errors are listed below. 

Run-time error ‘9’: Subscript out of range

Close other MS Excel files, debug to check the array that is being referenced.

Run-time error ‘76’: Path not found

Close other MS Excel files.
17

Run-time error ‘1004’: Method “OpenText’ of object ‘Workbooks’ failed 

Verify that files names are listed correctly in column B of SCENARIOS and are in the same directory as 

EFASC.

Run-time error ‘1004’: Application-defined or object-defined error

Verify that the formatting specified in the scenario is consistent with the input file. Verify that that MS 

Excel is parsing data from input file into the correct columns.

Run-time error ‘1004’: SaveAs method of Workbook class failed

Choose to replace monthly and flow duration files when prompted, delete old versions of these files, or 

opt to not write output to these files.

Run-time error ‘13’: Type mismatch

Verify that any non-numeric text has been removed from the field with streamflow data in the input file.

Other errors may not cause the program to terminate prematurely, but can produce spurious 

results or allow the program to terminate without producing output. These include input files that do not 

have the requisite number of days with data because of gaps in the record and setting the minimum 

number of years to a value greater than range of years specified in the SCENARIOS worksheet. 

Summary

The Environmental Flow Allocation and Statistics Calculator (EFASC) is a computer program 

that can be used for calculating streamflow statistics from daily streamflow time series using MS Excel. 

EFASC will generate a synthetic time series based on user-specified rules for allocating and protecting 

streamflow to simulate the outcome of different types of administrative rules for managing water 

resources. Two types of allocations are allowed: (1) priority allocations made before environmental flow 

protection rules are applied, and (2) additional allocations made after environmental protection rules are 

applied. A user is required to prepare text files with daily streamflow data, enter settings, and specify 

scenarios that will be run. The code (appendix A) can be modified to calculate additional statistics and 

generate output. EFASC is intended as a research tool. Users should verify that statistics generated by 

EFASC conform to the appropriate conventions for any application. The values generated by EFASC do 

not supersede the values of streamflow statistics published by the U.S. Geological Survey.
18

Figure 1. Flow chart for using Environmental Flow Allocation and Statistics Calculator (EFASC).

Specify priority 

allocation, 

environmental flow 

protection, and 

additional allocation 

on the EFLOWS 

worksheet:

copy cells A2:D2 

down for all scenarios; 

specify starting day for 

sub-year periods (e.b., 

months); specify rules 

for allocation and 

protection.

Prepare daily data file: ASCII text, date and streamflow in 

separate columns, using tab, space, or hyphen as delimiters between 

date and streamflow and within date (NWIS format circa 2010 

satisfies these criteria); identify the column number that year, 

month, day, and streamflow will appear in when the file is opened 

in Excel. These files must be in the same directory as EFASC.

Delete any results that should not be saved on output 

worksheets beginning with line 3. Results for each run are 

appended to any prior results on a worksheet. Do not delete line 2 

on SUMMARY worksheet as it contains formulas, which are 

copied down for each scenario.

Specify the scenarios on the SCENARIOS worksheet: enter the 

file name, starting and ending years, input specifications, output 

options including whether the scenario includes streamflow

allocation and/or protection standards; and the starting day of any 

subyearly periods used in calculations. The macro will process each 

scenario listed until it reaches a blank line. Scenarios below a blank 

line will not be processed.

Specify settings on the SETTINGS worksheet: enter any 

conversion of units, the year basis for low-flow calculations (water, 

climate, calendar, etc.), and criteria for processing missing data.

Run macro by selecting the following menu options:

View, Macro, View Macro, EFASC_r1, Run

MS Excel will be opening the input files and manipulating values if 

the macro is functioning.

Review and verify output.

Troubleshooting requires 

an understanding of 

programming. User 

should refer to the 

process diagram (fig. 2) 

for an overview of main 

components of the 

program.

To re-run scenarios, delete results beginning on line 3 of all output 

sheets. Delete monthly streamflow files and streamflow duration 

files (if they are specified as output). 

To run additional scenarios, enter them in SCENARIOS/EFLOWS 

worksheets starting on line 3. The specifications for previous 

scenarios can be saved on the sheet by copying the them below any 

new scenarios with a blank line separating new scenarios to be run 

from those that have already been run.
19

Figure 2. Streamflow chart for Environmental Flow Allocation and Statistics Calculator (EFASC) calculations.

If specified, read parameters 

from EFLOWS.

Dimension variables and arrays; set current path; write 

values from SETTINGS and files names from 

SCENARIOS; initialize time parameters.

Open QFILE(l); parse data into columns for year, 

month, day, and streamflow; write data for period of 

analysis (POA) that meet criteria for the minimum 

number of days to array DailyFlow.

Read parameters for the scenario from SCENARIOS.

Calculate daily statistics, write to TEMP; calculate 

mean and variation statistics for daily and log 

transformed daily values.

Calculate water year statistics and write to QWY (uses 

values in TEMP); write daily streamflow values to 

bioperiod array. Repeat for each year in POA.

If bioperiods are used, 

calculate bioperiod statistics.

Write cumulative distribution of streamflow exceeded 

to QEX and percent daily change to variables.

Either write output or synthesize daily streamflow.

Write monthly statistics to QMONTH and monthly 

variables; write short-term (100-day) statistics to 

variables when at specified period. Repeat for each 

month in POA. If scenario includes EFLOW 

specifications (priority 

appropriations, environmental 

flow standards, or additional 

allocations) and synthetic 

daily streamflow has not been 

Scenario does not include prior appropriation, calculated.

environmental flow standards, or additional allocations 

or the daily time series has been synthesized account for 

these factors.

Write output to individual worksheets and, if specified, 

“MONTH.CSV” and “FLOWDUR.CSV”.

Calculate priority allocation, 

environmental flow 

standard, and additional 

allocation; synthesize new 

daily time series.

Process the next scenario.
20

This page intentionally left blank.
21

Appendix A. Visual Basic for Applications Code for 

Environmental Flow Allocation and Statistics Calculator 

(EFASC)

'EFASC calculates streamflow statistics from a time series of daily streamflow. The user can

'specify scenarios that allocate water to out-of-stream uses and protect in-stream flows where

'the rules for allocating and protecting streamflow are based on flow statistics as described

'in US Geological Survey Open-File Report 2011-XXXX.

'The EFASC code was written by Christopher Konrad in Microsoft® Visual Basic® 6.5 for Applications and

'implemented as a macro in Microsoft Office Excel 2007 ®. This code was last edited on 6 June 2011.

'The program was executed on a Intel® CoreTM i7 CPU running at 2.67 GHz with a 64-bit operating

'system (Microsoft Windows 7 Professional®).

Sub EFASC_R1()

 MsgBox ("Environmental Flow Allocation and Statistics Calculator - Release 1 - 6 June 2011")

'Arrays and variables for reading data

 Dim FILE_NAME(1000) As String, QFILE(1000) As String, DIRECT As String, SCEN(1000) As String

 Dim LASTFILE As Integer

'Variables for settings

 Dim UNITS As Double, MIN_YEARS As Integer, MIN_DAYS As Integer, LOWFLOW_MONTH As Integer

 

'Cumulative number of days in the year prior to the first day of each month (leap years adjusted during processing).

 Dim JDAYFORMONTH(12) As Integer

 JDAYFORMONTH(1) = 0

 JDAYFORMONTH(2) = 31

 JDAYFORMONTH(3) = 59

 JDAYFORMONTH(4) = 90

 JDAYFORMONTH(5) = 120

 JDAYFORMONTH(6) = 151

 JDAYFORMONTH(7) = 181

 JDAYFORMONTH(8) = 212

 JDAYFORMONTH(9) = 243

 JDAYFORMONTH(10) = 273

 JDAYFORMONTH(11) = 304

 JDAYFORMONTH(12) = 334

 

'Set the current directory, the text files and the workbook with this macro must be in the same directory

 ChDir ActiveWorkbook.Path

 DIRECT = ActiveWorkbook.Path

'Get settings from sheet "SETTINGS"

 Sheets("SETTINGS").Select

 Range("b1").Select

 UNITS = ActiveCell(2, 1)

 MIN_YEARS = ActiveCell(3, 1)

 MIN_DAYS = ActiveCell(4, 1)

 LOWFLOW_MONTH = ActiveCell(5, 1)

 

'The first column of sheet "SCENARIOS" has a list of the names of the data files. The names are read into a

'character array QFILE(i)
22

 Sheets("SCENARIOS").Select

 Range("A1").Select

 i = 1

 Do Until ActiveCell(i + 2, 2) = ""

 SCEN(i) = ActiveCell(i + 2, 1)

 FILE_NAME(i) = ActiveCell(i + 2, 2)

 QFILE(i) = DIRECT & "\" & FILE_NAME(i)

 i = i + 1

 Loop

 LASTFILE = i - 1

'INITIALIZE VARIABLES AND ARRAYS FOR EACH SCENARIO

 Dim STARTWY As Integer, ENDWY As Integer, THRESH_HIGH_TYPE As Integer, THRESH_LOW_TYPE As 

Integer

 Dim CALC_EFLOW As Integer, WRITE_FLOWDUR As Integer, WRITE_DAILYS As Integer

 

'Variables for looping

 Dim EFLOW_RUN As Integer, WRITE_RESULTS As Integer

'Variables for tracking time

 Dim WY As Integer, YR As Integer, MN As Integer, DY As Integer, WY_NEXTDAY As Integer

 Dim LFY As Integer, LEAPYEAR As Integer

 Dim NUMDAY As Long, NUMWY As Integer, NUMLFY As Integer, DYINYEAR As Integer, DYINMONTH As 

Integer

 Dim NEXTLOWFLOWYEAR As Integer, ZERODAY As Integer

 Dim NUM_BPS As Long, NUMDAY_BP As Long

'Arrays and variables for prior appropriations, environmental flows, and additional allocations

 Dim PRIOR_APPRO_BASIS As Integer, EFLOW_BASIS As Integer, ADD_ALLOCATION_BASIS As Integer

 Dim PRIOR_APPRO_PERCENT(12) As Double, PRIOR_APPRO(12) As Double

 Dim EFLOW_PERCENT(12) As Double, EFLOW_STD(12) As Double

 Dim ADD_ALLOCATION_PERCENT(12) As Double, ADD_ALLOCATION(12) As Double

 Dim PRIOR As Double, EFLOW As Double, ALLOC As Double

'Arrays for daily streamflow

 Dim DailyFlow(73200, 14) As Double, TempDailyFlow(366, 5) As Double, QEX(1000) As Double

'Variables for streamflow, mean streamflow, and log transforms

 Dim Q As Double, QSUM As Double, QMEAN As Double, LOGQSUM As Double, LOGQMEAN As Double

'Arrays for streamflow by each water year at a site, maximum of 200 years

 Dim WYDAYS(200) As Integer

 Dim QWY(200, 25) As Double, QDYWY(200, 366) As Double

'Arrays for streamflow by month and by bioperiod

 Dim QMONTH(2400, 10) As Double, QBP(73200, 12) As Double, BPQSUM(12) As Double, BPQMEAN(12) As Double

'Array that provide index of bioperiod for each month (e.g., BP_START(1) is the day of the year when bioperiod 1 starts)

 Dim BP_START(12) As Integer, DYINBP(12) As Integer

'Arrays for monthly streamflow statistics

 Dim MONTHQSUM(12) As Double, CVMONTHQ(12) As Double, MEDMONTHQ(12) As Double

 Dim MEDMONTHHIGHDUR(12) As Double, MEDMONTHLOWDUR(12) As Double

 Dim YEARSLOWFLOWINMONTH(12), YEARSHIGHFLOWINMONTH(12) As Integer
23

'Variables for daily variation

 Dim CVQ As Double, SUMSQDEVQ As Double, CVLOGQ As Double, SUMSQDEVLOGQ As Double

 Dim LOGQMAX(200) As Double, MEANLOGQMAX As Double, SSQDEVLOGQMAX As Double, VARLOGQMAX 

As Double, CVLOGQMAX As Double

'Variables percent daily change

 Dim PDC50 As Double

'Variables for indexing location of flows exceeded 10, 50, and 90 percent of the time

 Dim TENTH As Integer, MED As Integer, NINETITH As Integer

'Variables for high and low flow events

 Dim HIGH_FLOW As Integer, HIGH_YEAR As Integer, DURHIGH As Integer

 Dim LOW_FLOW As Integer, LOW_YEAR As Integer, DURLOW As Integer

 Dim TH As Double, THRESH_HIGH As Double, TL As Double, THRESH_LOW As Double

 

'Variable for short-termflow statistics

 Dim DYS100PRIOR As Integer, DURHIGH100 As Integer, DURLOW100 As Integer

 Dim QMAX100 As Double, QMIN100 As Double, QMEAN100 As Double, PDC100 As Double

 Dim SAMPLEDATE As Date, CURRENTDATE As Date

 

'MAIN LOOP OPENS EACH TEXT FILE WITH STREAMFLOW FOR SCENARIO L

 For L = 1 To LASTFILE

'READ PARAMETERS FOR EACH SCENARIO

 Sheets("SCENARIOS").Select

 Range("A1").Select

 STARTWY = ActiveCell(L + 2, 3)

 ENDWY = ActiveCell(L + 2, 4)

 QCOL = ActiveCell(L + 2, 6)

 NWIS = ActiveCell(L + 2, 5)

 

 If NWIS = 1 Then

 YRCOL = 3

 MNCOL = 4

 DYCOL = 5

 Else

 YRCOL = ActiveCell(L + 2, 7)

 MNCOL = ActiveCell(L + 2, 8)

 DYCOL = ActiveCell(L + 2, 9)

 NUMHEADERLINES = ActiveCell(L + 2, 10)

 End If

 

 THRESH_HIGH_TYPE = ActiveCell(L + 2, 11)

 TH = ActiveCell(L + 2, 12)

 THRESH_LOW_TYPE = ActiveCell(L + 2, 13)

 TL = ActiveCell(L + 2, 14)

 CALC_EFLOW = ActiveCell(L + 2, 15)

 For i = 1 To 12

 BP_START(i) = ActiveCell(L + 2, i + 15)

 If BP_START(i) > 0 Then

 NUM_BPS = i

 End If

 Next

 WRITE_DAILYS = ActiveCell(L + 2, 28)

 WRITE_FLOWDUR = ActiveCell(L + 2, 29)

 SAMPLEDATE = ActiveCell(L + 2, 30)
24

'READ PARAMETERS FOR PRIOR APPROPRIATION, ENVIRONMENTAL FLOW STANDARDS, AND OTHER 

ALLOCATIONS

 If CALC_EFLOW = 1 Then

 Sheets("EFLOWS").Select

 Range("A1").Select

 PRIOR_APPRO_BASIS = ActiveCell(L + 2, 5)

 EFLOW_BASIS = ActiveCell(L + 2, 18)

 ADD_ALLOCATION_BASIS = ActiveCell(L + 2, 31)

 For i = 1 To NUM_BPS

 PRIOR_APPRO_PERCENT(i) = ActiveCell(L + 2, i + 5)

 EFLOW_PERCENT(i) = ActiveCell(L + 2, i + 18)

 ADD_ALLOCATION_PERCENT(i) = ActiveCell(L + 2, i + 31)

 Next

 End If

 

'READ STREAMFLOW DATA FROM THE TEXT FILE IN THE ARRAY QFILE(l) FOR THE INITIAL RUN

'FieldInfo defines data type for each column where Array(column,data type) and data type = 2 if text

100 If NWIS = 1 Then

 Workbooks.OpenText Filename:=QFILE(L), _

 Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _

 ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _

 True, Space:=True, Other:=True, OtherChar:="-", FieldInfo:=Array( _

 Array(1, 2), Array(2, 2)), TrailingMinusNumbers:=True

'Bypassess headers

 i = 1

 Do Until ActiveCell(i, 1).Value = "USGS"

 i = i + 1

 Loop

 Else

 Workbooks.OpenText Filename:=QFILE(L), _

 Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _

 ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _

 True, Space:=True, Other:=True, OtherChar:="-", TrailingMinusNumbers:=True

 i = NUMHEADERLINES + 1

 End If

 ActiveCell(i, 1).Select

'READ DAILY STREAMFLOW DATA FROM TEXT FILE (year, month, day, Q in specified units) INTO TempDailyFlow

'where i is the row number of the text file and j is the row for the DailyFlow array.

'The loop stops reading at a line without a year. The loop is only executed on streamflow records (EFLOW_RUN=0)

 i = 1

 j = 0

 Do Until ActiveCell(i, YRCOL) = ""

 YR = ActiveCell(i, YRCOL)

 MN = ActiveCell(i, MNCOL)

 DY = ActiveCell(i, DYCOL)

 If MN > 9 Then

 WY = YR + 1

 Else

 WY = YR

 End If

 If ActiveCell(i + 1, MNCOL) > 9 Then

 WY_NEXTDAY = ActiveCell(i + 1, YRCOL) + 1

 Else

 WY_NEXTDAY = ActiveCell(i + 1, YRCOL)
25

 End If

'The next lines limits processing to the range of water years specified in "SCENARIOS".

 If WY >= STARTWY Then

 If WY <= ENDWY Then

 If (ActiveCell(i, QCOL) = "Ice") Then

 GoTo 200

 End If

 If (ActiveCell(i, QCOL) = "") Then

 GoTo 200

 End If

 If (ActiveCell(i, QCOL) = "NaN") Then

 GoTo 200

 End If

 If (ActiveCell(i, QCOL) < 0) Then

 GoTo 200

 End If

 j = j + 1

'TempDailyFlow is used for reading data from the text file one water year at a time.

'TempDailyFlow columns: water year [1], year [2], month [3], day [4], streamflow [5].

'Missing values, NaN, and negative values are skipped.

 TempDailyFlow(j, 1) = WY

 TempDailyFlow(j, 2) = YR

 TempDailyFlow(j, 3) = MN

 TempDailyFlow(j, 4) = DY

 TempDailyFlow(j, 5) = ActiveCell(i, QCOL) * UNITS

'IF A WATER YEAR MEETS THE MINIMUM NUMBER OF DAYS CRITERIA,

'WRITE STREAMFLOW DATA FROM TempDailyFLow TO DailyFlow AND CALCULATE DAILY STATS AT THE 

END OF EACH WATER YEAR

'NUMDAY is cumulative number of days of record for all years that have minimum number of daily values.

'DailyFlow columns: water year [1], year [2], month [3], day [4], streamflow [5], seven-day low flow [6],

'absolute percent daily change [7], and signed value of percent daily change [8], log10 of streamflow [9],

'water year number [10], bioperiod [11] assigned to the day, low flow year [12], and number of low flow year [13].

200 If WY <> WY_NEXTDAY Then

 If j >= MIN_DAYS Then

 NUMWY = NUMWY + 1

 For k = 1 To j

 NUMDAY = NUMDAY + 1

 DailyFlow(NUMDAY, 1) = TempDailyFlow(k, 1)

 DailyFlow(NUMDAY, 2) = TempDailyFlow(k, 2)

 DailyFlow(NUMDAY, 3) = TempDailyFlow(k, 3)

 DailyFlow(NUMDAY, 4) = TempDailyFlow(k, 4)

 DailyFlow(NUMDAY, 5) = TempDailyFlow(k, 5)

 DailyFlow(NUMDAY, 10) = NUMWY

 If LOWFLOW_MONTH >= 6 Then

 If (DailyFlow(NUMDAY, 3) >= LOWFLOW_MONTH) Then

 DailyFlow(NUMDAY, 12) = DailyFlow(NUMDAY, 2) + 1

 Else

 DailyFlow(NUMDAY, 12) = DailyFlow(NUMDAY, 2)

 End If

 Else

 If (DailyFlow(NUMDAY, 3) >= LOWFLOW_MONTH) Then

 DailyFlow(NUMDAY, 12) = DailyFlow(NUMDAY, 2)

 Else

 DailyFlow(NUMDAY, 12) = DailyFlow(NUMDAY, 2) - 1
26

 End If

 End If

 If LFY < DailyFlow(NUMDAY, 12) Then

 LFY = DailyFlow(NUMDAY, 12)

 NUMLFY = NUMLFY + 1

 End If

 DailyFlow(NUMDAY, 13) = NUMLFY

 Next k

 End If

'Reset j for the next water year

 j = 0

'Ends statement WY<>WY_NEXTDAY

 End If

'Ends statement IF WY<ENDWY

 End If

'Ends statement IF WY > STARTWY

 End If

'Advances to the next day (i)

 i = i + 1

 Loop

'The program finished reading data into array DailyFlow and closes the text file.

 ActiveWorkbook.Close SaveChanges:=False

'Process sites that met criteria for minimum number of years.

 If NUMWY >= MIN_YEARS Then

'LOOP TO CALCULATE STREAMFLOW STATISTICS

'EFLOW_RUN is used to calculate statistics from adjusted streamflow time-series using the eflow standards.

'Initially, EFFLOW_RUN = 0 for each scenario. If there are not allocation or protection rules, statistics are calculated

'from the input data. Otherwise, EFLOW_RUN switches to 1 and the rules are used to generate a synthetic times series,

'which is used to calculate statistics

 

'This loop is run using data read from the input file (EFLOW_RUN = 0) and

're-run for scenarios with EFLOWS on the synthesized daily values after prior appropriations,

'environmental flows, and future allocation (EFLOW_RUN = 1).

 Do While EFLOW_RUN < 2

 ZERODAY = 0

 QSUM = 0

 QMEAN = 0

 SUMSQDEVQ = 0

 CVQ = 0

 LOGQSUM = 0

 LOGQMEAN = 0

 SUMSQDEVLOGQ = 0

 CVLOGQ = 0

 SUMSQDEVLOGQ = 0

 MEANLOGQMAX = 0

 SSQDEVLOGQMAX = 0

 VARLOGQMAX = 0

 CVLOGQMAX = 0

 PDC50 = 0

 TENTH = 0

 MED = 0

 NINETITH = 0

 HIGH_FLOW = 0
27

 HIGH_YEAR = 0

 DURHIGH = 0

 LOW_FLOW = 0

 LOW_YEAR = 0

 DURLOW = 0

 DYS100PRIOR = 0

 FIRST100DAY = 0

 DURHIGH100 = 0

 DURLOW100 = 0

 QMAX100 = 0

 QMIN100 = 0

 QMEAN100 = 0

 PDC100 = 0

 Erase QWY, QMONTH, QBP, BPQSUM, BPQMEAN, DYINBP, MONTHQSUM, CVMONTHQ, 

MEDMONTHQ, LOGQMAX

 Erase MEDMONTHHIGHDUR, MEDMONTHLOWDUR, YEARSLOWFLOWINMONTH, 

YEARSHIGHFLOWINMONTH

'Daily flow statistics (mean, 7-day, percent change)

'Calculates QSUM and LOGQSUM

 For i = 1 To NUMDAY

 QSUM = DailyFlow(i, 5) + QSUM

 If DailyFlow(i, 5) > 0 Then

 DailyFlow(i, 9) = Log(DailyFlow(i, 5)) / Log(10)

 Else

 ZERODAY = ZERODAY + 1

 End If

 LOGQSUM = DailyFlow(i, 9) + LOGQSUM

'Write seven day low flow to DailyFlow, column 6

 If i > 6 Then

 If DateSerial(DailyFlow(i, 2), DailyFlow(i, 3), DailyFlow(i, 4)) - _

 DateSerial(DailyFlow(i - 6, 2), DailyFlow(i - 6, 3), DailyFlow(i - 6, 4)) = 6 Then

 DailyFlow(i, 6) = (DailyFlow(i - 6, 5) + DailyFlow(i - 5, 5) + DailyFlow(i - 4, 5) + _

 DailyFlow(i - 3, 5) + DailyFlow(i - 2, 5) + DailyFlow(i - 1, 5) + DailyFlow(i, 5)) / 7

 Else

 DailyFlow(i, 6) = 1000 * (DailyFlow(i, 5) + 1)

 End If

 Else

 DailyFlow(i, 6) = 1000 * (DailyFlow(i, 5) + 1)

 End If

 

'Write absolute value of daily percent change to DailyFlow, column 7 and signed value daily percent change to DailyFlow, 

column 8

 If i > 1 Then

 If DateSerial(DailyFlow(i, 2), DailyFlow(i, 3), DailyFlow(i, 4)) - _

 DateSerial(DailyFlow(i - 1, 2), DailyFlow(i - 1, 3), DailyFlow(i - 1, 4)) = 1 Then

 If DailyFlow(i - 1, 5) > 0 Then

 DailyFlow(i, 7) = Abs(DailyFlow(i, 5) - DailyFlow(i - 1, 5)) / DailyFlow(i - 1, 5)

 DailyFlow(i, 8) = (DailyFlow(i, 5) - DailyFlow(i - 1, 5)) / DailyFlow(i - 1, 5)

 Else

 If DailyFlow(i, 5) = 0 Then

 DailyFlow(i, 7) = 0

DailyFlow(i, 8) = 0

 Else
28

 DailyFlow(i, 7) = 1

DailyFlow(i, 8) = 1

 End If

 End If

 End If

 End If

 Next i

 

'WRITE ANNUAL FLOW STATISTICS TO QWY AND BP STATISTICS

'Initialize array QWY that stores water year statistics in columns:

'water year [1], number of days [2], 'Qmean [3], Qmax [4], Q10 [5], Q50 [6], Q90 [7],

'Qmin [8],7-day low flow [9], number of zero flow days [10],

'number of peak events [11], length of longest peak event [12],

'number of low flow events [13], length of longest low flow event [14],

'coefficient of variation of log of daily flows [15],percent daily change [16]

'range of log transformed monthly flows [17], month of high flow [18], month of low flow [19],

'50th percentile of non-zero percent daily change (base flow recession) [20] and

'10th percentile of non-zero percent daily change (storm flow recession) [21].

'CALCULATE STATISTICS FOR EACH WATER YEAR

'i indexes each day in the record, j indexes the water year ordinal number, k is

'TEMP is used for sorting flow, log flow, and percent daily change.

 Sheets("TEMP").Select

 Range("A1").Select

 For i = 1 To NUMDAY

 j = DailyFlow(i, 10)

 If QWY(j, 1) = 0 Then

 k = 0

 End If

 k = k + 1

 QWY(j, 1) = DailyFlow(i, 1)

 QWY(j, 2) = QWY(j, 2) + 1

 QWY(j, 3) = QWY(j, 3) + DailyFlow(i, 5)

 If QWY(j, 4) < DailyFlow(i, 5) Then

 QWY(j, 4) = DailyFlow(i, 5)

 QWY(j, 18) = DailyFlow(i, 3)

 End If

'Annual minimum, 7-day low flow, and number of no-flow days for the low-flow year

 NUMLFY = DailyFlow(i, 13)

 QWY(NUMLFY, 8) = DailyFlow(i, 5)

 QWY(NUMLFY, 9) = DailyFlow(i, 6)

 QWY(NUMLFY, 10) = 0

 QWY(NUMLFY, 19) = DailyFlow(i, 3)

 If QWY(NUMLFY, 8) > DailyFlow(i, 5) Then

 QWY(NUMLFY, 8) = DailyFlow(i, 5)

 QWY(NUMLFY, 19) = DailyFlow(i, 3)

 End If

 If QWY(NUMLFY, 9) > DailyFlow(i, 6) Then

 QWY(NUMLFY, 9) = DailyFlow(i, 6)

 End If

 If DailyFlow(i, 5) = 0 Then

 QWY(NUMLFY, 10) = QWY(NUMLFY, 10) + 1

 End If
29

 

'WRITE DATA TO BP ARRAYS

'Calculate julian day to evaluate bioperiod - neither are adjusted for leap years

 DYINYEAR = JDAYFORMONTH(DailyFlow(i, 3)) + DailyFlow(i, 4)

'Determine the bioperiod (b) for the current day (DYINYEAR), account for Feb 29

 If CALC_EFLOW = 1 Then

 If (DYINYEAR = 60 And DailyFlow(i, 3) = 2) Then

 DYINYEAR = DYINYEAR - 1

 End If

 For b = 1 To NUM_BPS

 If DYINYEAR >= BP_START(b) Then

 DailyFlow(i, 11) = b

 End If

 Next

 b = DailyFlow(i, 11)

 QBP(DYINBP(b), b) = DailyFlow(i, 5)

 DYINBP(b) = DYINBP(b) + 1

 End If

'WRITE FLOW AND PERCENT DAILY CHANGE DATA FROM DailyFlow ARRAY TO "TEMP" SHEET FOR EACH 

WATER YEAR

 ActiveCell(k, 1) = DailyFlow(i, 5)

 ActiveCell(k, 2) = DailyFlow(i, 9)

 ActiveCell(k, 3) = DailyFlow(i, 7)

 ActiveCell(k, 4) = DailyFlow(i, 8)

 

'CALCULATE STATISTICS AT THE END OF THE WATER YEAR

'Mean annual flow and WY flow duration(Q10, Q50, Q90) statistics

 If DailyFlow(i + 1, 1) <> QWY(j, 1) Then

 QWY(j, 3) = QWY(j, 3) / QWY(j, 2)

 Range(ActiveCell(1, 1), ActiveCell(QWY(j, 2), 4)).Select

 Selection.Sort Key1:=ActiveCell, Order1:=xlDescending, _

 Header:=xlNo, OrderCustom:=1, MatchCase:=False, _

 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 ActiveCell(1, 1).Select

 TENTH = Int(QWY(j, 2) * 0.1)

 MED = Int(QWY(j, 2) * 0.5)

 NINETITH = Int(QWY(j, 2) * 0.9)

 QWY(j, 5) = ActiveCell(TENTH, 1) - (ActiveCell(TENTH, 1) - ActiveCell(TENTH + 1, 1)) * (QWY(j, 2) * 

0.1 - TENTH)

 QWY(j, 6) = ActiveCell(MED, 1) - (ActiveCell(MED, 1) - ActiveCell(MED + 1, 1)) * (QWY(j, 2) * 0.5 -

MED)

 QWY(j, 7) = ActiveCell(NINETITH, 1) - (ActiveCell(NINETITH, 1) - ActiveCell(NINETITH + 1, 1)) * 

(QWY(j, 2) * 0.9 - NINETITH)

 If (QWY(j, 2) - QWY(j, 10)) > 1 Then

'Annual variance of log transformed flows used to calculate annual coefficient of variation

 QWY(j, 15) = WorksheetFunction.Var(Range(ActiveCell(1, 2), ActiveCell(QWY(j, 2) - QWY(j, 10), 2)))

'Annual median absolute percent daily change in streamflow

 QWY(j, 16) = WorksheetFunction.Median(Range(ActiveCell(1, 3), ActiveCell(QWY(j, 2) - QWY(j, 10), 

3)))

 End If

'Annual median of non-positive percent daily change (base flow recession)

 If QWY(j, 2) > QWY(j, 10) Then
30

 Range(ActiveCell(1, 4), ActiveCell(QWY(j, 2) - QWY(j, 10), 4)).Select

 If QWY(j, 16) > 0 Then

 Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, _

 Header:=xlNo, OrderCustom:=1, MatchCase:=False, _

 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 k = 1

 Do Until ActiveCell(k, 1) > 0

 k = k + 1

 Loop

 MED = Int(k * 0.5)

 TENTH = Int(k * 0.1)

 QWY(j, 20) = ActiveCell(MED, 1) - (ActiveCell(MED, 1) - ActiveCell(MED + 1, 1)) * (k * 0.5 - MED)

 If TENTH > 0 Then

 QWY(j, 21) = ActiveCell(TENTH, 1) - (ActiveCell(TENTH, 1) - ActiveCell(TENTH + 1, 1)) * (k * 

0.1 - TENTH)

 Else

 QWY(j, 21) = ActiveCell(1, 1)

 End If

 Else

 QWY(j, 20) = 0

 QWY(j, 21) = 0

 End If

 ActiveCell(QWY(j, 2) + 1, -2).Select

 Else

 ActiveCell(QWY(j, 2) + 1, 1).Select

 End If

'Advance the active cell in TEMP to the first line of the next water year

 End If

 Next i

'CALCULATE MEAN AND VARIABILITY STATISTICS FOR PERIOD OF ANALYSIS

 QMEAN = QSUM / NUMDAY

 LOGQMEAN = LOGQSUM / (NUMDAY - ZERODAY)

 For i = 1 To NUMDAY

 SUMSQDEVQ = (DailyFlow(i, 5) - QMEAN) ^ 2 + SUMSQDEVQ

 SUMSQDEVLOGQ = (DailyFlow(i, 9) - LOGQMEAN) ^ 2 + SUMSQDEVLOGQ

 Next i

'Coefficient of variation of daily flows

 CVQ = ((SUMSQDEVQ / (NUMDAY - 1)) ^ 0.5) / QMEAN

'Coefficient of variation of log daily flows transformed back into dimensional space

 If SUMSQDEVLOGQ / (NUMDAY - ZERODAY - 1) < 100 Then

 CVLOGQ = (10 ^ (SUMSQDEVLOGQ / (NUMDAY - ZERODAY - 1)) - 1) ^ 0.5

 End If

'Initialize array QEX that stores flow duration values where the index (i) is 10 times duration that the given flow is exceeded

'(e.g., QEX(1) is the flow exceeded 0.1 percent of the time, QEX(100) is the flow exceeded 10 percent of the time).

'QEX has flow exceeded from 0.1 to 100 percent of the time in increments of 0.1 percent.

 For i = 1 To 1000

 QEX(i) = 0

 Next i

'Sorts streamflow data in "TEMP" in descending order, fills an array (QEX) with streamflow equaled or

'exceeded 0.1 to 100 percent of the time in increments of 0.1 time for record of complete water years.

'If there is not an exact number of days corresponding to a given duration (i/1000), an average of flows

'for days j and j+1 is weighted by the difference between j/NUMDAY and i/1000.

'The first value in the array, QEX(0,1), is the maximum flow.

 Range("A1:d1").Select
31

 Range(Selection, Selection.End(xlDown)).Select

 Selection.Sort Key1:=Cells(1, 1), Order1:=xlDescending, _

 Header:=xlNo, OrderCustom:=1, MatchCase:=False, _

 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 For i = 1 To 1000

 j = Int(NUMDAY * (i / 1000))

 If j = 0 Then

 QEX(i) = ActiveCell(1, 1)

 Else

 QEX(i) = ActiveCell(j, 1) - ((NUMDAY) * (i / 1000) - j) * (ActiveCell(j, 1) - ActiveCell(j + 1, 1))

 End If

 Next i

 

'Thresholds for high flow and low flow events

 If EFLOW_RUN = 0 Then

 If THRESH_HIGH_TYPE = 2 Then

 THRESH_HIGH = QEX((TH) * 1000)

 Else

 THRESH_HIGH = TH

 End If

 If THRESH_LOW_TYPE = 2 Then

 THRESH_LOW = QEX((TL) * 1000)

 Else

 THRESH_LOW = TL

 End If

 End If

 

'Calculate median absolute percent daily change for days with streamflow

 Range("C1").Select

 Range(ActiveCell(1, 1), ActiveCell(NUMDAY - ZERODAY, 1)).Select

 Selection.Sort Key1:=ActiveCell, Order1:=xlDescending, _

 Header:=xlNo, OrderCustom:=1, MatchCase:=False, _

 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 j = Int(NUMDAY + 1) / 2

 PDC50 = ActiveCell(j, 1) - (ActiveCell(j, 1) - ActiveCell(j + 1, 1)) * ((NUMDAY + 1) / 2 - j)

 

 Range("A1:D1").Select

 Range(Selection, Selection.End(xlDown)).ClearContents

 

'Estimated 10-year, 7-day flow flow

 Range("A1").Select

 For i = 1 To NUMLFY

 ActiveCell(i, 1) = QWY(i, 9)

 Next i

 EST7Q10 = Application.WorksheetFunction.Percentile(Range(ActiveCell(1, 1), ActiveCell(i, 1)), 0.1)

 Range(ActiveCell(1, 1), ActiveCell(i, 1)).ClearContents

 

'Bioperiod calculations if environmental flow standard or additional allocation is based on exceedence percentile in period,

'otherwise, bioperiod calculations are done after other flow statistics

 If (CALC_EFLOW = 1 And EFLOW_RUN = 0) Then

 If ((PRIOR_APPRO_BASIS = 4 Or EFLOW_BASIS = 4) Or ADD_ALLOCATION_BASIS = 4) Then

 Range("A1").Select

 For i = 1 To NUM_BPS

 For j = 1 To DYINBP(i)

 ActiveCell(j, 1) = QBP(j, i)
32

 Next j

 Range(Selection, Selection.End(xlDown)).Select

 Selection.Sort Key1:=ActiveCell, Order1:=xlDescending, _

 Header:=xlNo, OrderCustom:=1, MatchCase:=False, _

 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 Range("A1").Select

 If PRIOR_APPRO_BASIS = 4 Then

 k = Int(DYINBP(i) * PRIOR_APPRO_PERCENT(i))

 If k = 0 Then

 PRIOR_APPRO(k) = ActiveCell(1, 1)

 Else

 PRIOR_APPRO(i) = ActiveCell(k, 1) - (ActiveCell(k, 1) - ActiveCell(k + 1, 1)) * (DYINBP(i) * 

PRIOR_APPRO_PERCENT(i) - k)

 End If

 End If

 If EFLOW_BASIS = 4 Then

 j = Int(DYINBP(i) * EFLOW_PERCENT(i))

 If j = 0 Then

 EFLOW_STD(i) = ActiveCell(1, 1)

 Else

 EFLOW_STD(i) = ActiveCell(j, 1) - (ActiveCell(j, 1) - ActiveCell(j + 1, 1)) * (DYINBP(i) * 

EFLOW_PERCENT(i) - j)

 End If

 End If

 If ADD_ALLOCATION_BASIS = 4 Then

 k = Int(DYINBP(i) * ADD_ALLOCATION_PERCENT(i))

 If k = 0 Then

 ADD_ALLOCATION(k) = ActiveCell(1, 1)

 Else

 ADD_ALLOCATION(i) = ActiveCell(k, 1) - (ActiveCell(k, 1) - ActiveCell(k + 1, 1)) * (DYINBP(i) 

* ADD_ALLOCATION_PERCENT(i) - k)

 End If

 End If

 Range(Selection, Selection.End(xlDown)).ClearContents

 Next i

 End If

 If ((PRIOR_APPRO_BASIS = 5 Or EFLOW_BASIS = 5) Or ADD_ALLOCATION_BASIS = 5) Then

 BPSUMQ = 0

 For i = 1 To NUM_BPS

 For j = 1 To DYINBP(i)

 BPSUMQ = QBP(j, i) + BPSUMQ

 Next j

 If DYINBP(i) > 0 Then

 BPQMEAN(i) = BPSUMQ / DYINBP(i)

 End If

 Next i

 End If

 End If

'MONTHLY STATISTICS AND STATISTICS FOR 100 DAYS PRIOR TO SPECIFIED DATE

'Monthly flow statistics for each month of the period of record are in array QMONTH where the columns are year [1],

'month [2]), mean streamflow for the month [3], minimum streamflow for the month [4], maximum streamflow for the month 

[5],

'days above high threshold [6], and days below low threshold [7], median streamflow [8], and water year [10].

'Monthly flow stats are written to QMONTH where the rows are each month (m) of each complete year of record.
33

 m = 1

 Range("A1").Select

 For i = 1 To NUMDAY

 If QMONTH(m, 1) = 0 Then

 QMONTH(m, 1) = DailyFlow(i, 2)

 QMONTH(m, 2) = DailyFlow(i, 3)

 QMONTH(m, 3) = 0

 QMONTH(m, 4) = DailyFlow(i, 5)

 QMONTH(m, 5) = DailyFlow(i, 5)

 QMONTH(m, 6) = 0

 QMONTH(m, 7) = 0

 QMONTH(m, 8) = 0

 QMONTH(m, 9) = 0

 QMONTH(m, 10) = DailyFlow(i, 1)

 DYINMONTH = 1

 End If

 ActiveCell(DYINMONTH, 1) = DailyFlow(i, 5)

 j = DailyFlow(i, 10)

 

 QMONTH(m, 3) = QMONTH(m, 3) + DailyFlow(i, 5)

 If QMONTH(m, 5) < DailyFlow(i, 5) Then

 QMONTH(m, 5) = DailyFlow(i, 5)

 End If

 If QMONTH(m, 4) > DailyFlow(i, 5) Then

 QMONTH(m, 4) = DailyFlow(i, 5)

 End If

'Count and length of annual peak events, QWY(wy,11) and QWY(wy,12)

 If DailyFlow(i, 5) >= THRESH_HIGH Then

 QMONTH(m, 6) = QMONTH(m, 6) + 1

 If HIGH_YEAR = 0 Then

 QWY(j, 11) = QWY(j, 11) + 1

 HIGH_YEAR = 1

 End If

 DURHIGH = DURHIGH + 1

 If DURHIGH > QWY(j, 12) Then

 QWY(j, 12) = DURHIGH

 End If

 Else

 HIGH_YEAR = 0

 DURHIGH = 0

 End If

 

'Count and length of low flow events, QWY(lfy,13) and QWY(lfy,14)

 If DailyFlow(i, 5) <= THRESH_LOW Then

 QMONTH(m, 7) = QMONTH(m, 7) + 1

 If DailyFlow(i, 13) > 0 Then

 LFY = DailyFlow(i, 13)

 If LOW_YEAR = 0 Then

 QWY(LFY, 13) = QWY(LFY, 13) + 1

 LOW_YEAR = 1

 End If

 DURLOW = DURLOW + 1

 If DURLOW > QWY(LFY, 14) Then

 QWY(LFY, 14) = DURLOW
34

 End If

 End If

 Else

 LOW_MONTH = 0

 LOW_YEAR = 0

 DURLOW = 0

 End If

'If the next line of DailyFlow is from the next month, then Qmean and Qmedian for the month are calculated and

'the month index m move forward.

 If DailyFlow(i, 3) <> DailyFlow(i + 1, 3) Then

 QMONTH(m, 3) = QMONTH(m, 3) / DYINMONTH

 QMONTH(m, 8) = Application.WorksheetFunction.Median(Range(Selection, Selection.End(xlDown)))

 Range(Selection, Selection.End(xlDown)).ClearContents

 m = m + 1

 Else

 End If

 DYINMONTH = DYINMONTH + 1

 

'Statististics for flows 100 days prior to sampling

 CURRENTDATE = DateSerial(DailyFlow(i, 2), DailyFlow(i, 3), DailyFlow(i, 4))

 If CURRENTDATE > (SAMPLEDATE - 100) Then

 If CURRENTDATE <= SAMPLEDATE Then

 If FIRST100DAY = 0 Then

 QMIN100 = DailyFlow(i, 5)

 FIRST100DAY = 1

 End If

 If DailyFlow(i - 1, 5) > 0 Then

 DYFLOW100 = DYFLOW100 + 1

 PDC100 = Abs(DailyFlow(i, 5) + DailyFlow(i - 1, 5)) / DailyFlow(i - 1, 5)

 End If

 QMEAN100 = QMEAN100 + DailyFlow(i, 5)

 If QMAX100 < DailyFlow(i, 5) Then

 QMAX100 = DailyFlow(i, 5)

 End If

 If DailyFlow(i, 5) > THRESH_HIGH Then

 DURHIGH100 = DURHIGH100 + 1

 End If

 If QMIN100 > DailyFlow(i, 5) Then

 QMIN100 = DailyFlow(i, 5)

 End If

 If DailyFlow(i, 5) < THRESH_LOW Then

 DURLOW100 = DURLOW100 + 1

 End If

 End If

 End If

'Reset values for new water year

 If DailyFlow(i, 1) <> DailyFlow(i + 1, 1) Then

 HIGH_YEAR = 0

 DURHIGH = 0

 End If

 If DailyFlow(i, 12) <> DailyFlow(i + 1, 12) Then

 LOW_YEAR = 0

 DURLOW = 0

 End If
35

 Next i

 m = m - 1

'Median monthly mean

 k = 1

 Range("A1").Select

 For i = 1 To m

 j = Int(QMONTH(i, 2))

 MONTHQSUM(j) = QMONTH(i, 3) + MONTHQSUM(j)

 k = Int((i - 1) / 12 + 1)

 ActiveCell(k, j) = QMONTH(i, 3)

 Next i

 

 For i = 1 To 12

 Range(ActiveCell(1, i), ActiveCell(k, i)).Name = "MonthRange"

 If Application.WorksheetFunction.Count(Range("MonthRange")) > 0 Then

 MEDMONTHQ(i) = Application.WorksheetFunction.Median(Range("MonthRange"))

 If Application.WorksheetFunction.Average(Range("MonthRange")) > 0 Then

 If k > 1 Then

 CVMONTHQ(i) = Application.WorksheetFunction.StDev(Range("MonthRange")) / 

Application.WorksheetFunction.Average(Range("MonthRange"))

 End If

 End If

 End If

 Range("A1").Select

 Next i

'Annual variation of monthly streamflow

 For i = 1 To NUMWY

 Range(ActiveCell(i, 1), ActiveCell(i, 12)).Name = "MonthRange"

 If QWY(i, 3) > 0 Then

 If Application.WorksheetFunction.Min(Range("MonthRange")) > 0 Then

 QWY(i, 17) = (Application.WorksheetFunction.Max(Range("MonthRange")) -

(Application.WorksheetFunction.Min(Range("MonthRange")))) / QWY(i, 3)

 End If

 Else

 QWY(i, 17) = 0

 End If

 Next i

 Range(ActiveCell(1, 1), ActiveCell(NUMWY, 12)).ClearContents

'Monthly statistics of high flow events and low flow events

'i is the month for the period of analysis, j is the month number (1 to 12), and k is year

 For i = 1 To m

 j = Int(QMONTH(i, 2))

 k = Int((i - 1) / 12 + 1)

 ActiveCell(k, j) = QMONTH(i, 6)

 If QMONTH(i, 6) > 0 Then

 YEARSHIGHFLOWINMONTH(j) = YEARSHIGHFLOWINMONTH(j) + 1

 End If

 Next i

 

 For i = 1 To 12

 Range(ActiveCell(1, i), ActiveCell(k, i)).Name = "MonthRange"

 If Application.WorksheetFunction.Count(Range("MonthRange")) > 0 Then

 MEDMONTHHIGHDUR(i) = Application.WorksheetFunction.Median(Range("MonthRange"))
36

 Range("MonthRange").ClearContents

 End If

 Range("A1").Select

 Next i

 

 For i = 1 To m

 j = Int(QMONTH(i, 2))

 k = Int((i - 1) / 12 + 1)

 ActiveCell(k, j) = QMONTH(i, 7)

 If QMONTH(i, 7) > 0 Then

 YEARSLOWFLOWINMONTH(j) = YEARSLOWFLOWINMONTH(j) + 1

 End If

 Next i

 

 For i = 1 To 12

 Range(ActiveCell(1, i), ActiveCell(k, i)).Name = "MonthRange"

 If Application.WorksheetFunction.Count(Range("MonthRange")) > 0 Then

 MEDMONTHLOWDUR(i) = Application.WorksheetFunction.Median(Range("MonthRange"))

 Range("MonthRange").ClearContents

 Range("A1").Select

 End If

 Next i

 

'INSERT ADDITIONAL STATISTICAL CALCULATIONS HERE

'ARRAYS AVAILABLE - DailyFlow(1:NUMDAYS,1:11), QMONTH(1:NUMWYx12,1:10), QWY(1:NUMWY,1:21)

 

'OUTPUT

'Write output if there are no eflow calculations (CALC_EFLOW = 0) or

'if eflow calculations have been applied (CALC_EFLOW=1 and EFLOW_RUN=1)

 If CALC_EFLOW = 0 Then

 WRITE_RESULTS = 1

 Else

 If CALC_EFLOW = 1 Then

 If EFLOW_RUN = 1 Then

 WRITE_RESULTS = 1

 End If

 End If

 End If

 If WRITE_RESULTS = 1 Then

'Write water year

 Sheets("YEAR").Select

 A = 1

 Do Until ActiveCell(A, 1) = ""

 A = A + 1

 Loop

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 1)

 Next i

 Sheets("QMEAN").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY
37

 ActiveCell(A, i + 1).Value = QWY(i, 3)

 Next i

 

 Sheets("QMAX").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 MEANLOGQMAX = 0

 j = 0

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 4)

 If QWY(i, 4) > 0 Then

 LOGQMAX(i) = Log(QWY(i, 4)) / Log(10)

 MEANLOGQMAX = LOGQMAX(i) + MEANLOGQMAX

 j = j + 1

 End If

 Next i

 MEANLOGQMAX = MEANLOGQMAX / j

 

 SSQDEVLOGQMAX = 0

 For i = 1 To NUMWY

 SSQDEVLOGQMAX = (LOGQMAX(i) - MEANLOGQMAX) ^ 2 + SSQDEVLOGQMAX

 Next i

 If NUMWY > 1 Then

 VARLOGQMAX = SSQDEVLOGQMAX / (NUMWY - 1)

 CVLOGQMAX = ((10 ^ VARLOGQMAX) - 1) ^ 0.5

 End If

 

 Sheets("QMIN").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMLFY

 If QWY(i, 8) < 1000000000 Then

 ActiveCell(A, i + 1).Value = QWY(i, 8)

 End If

 Next i

 Sheets("NO_FLOW_DAYS").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMLFY

 ActiveCell(A, i + 1).Value = QWY(i, 10)

 Next i

 

 Sheets("Q10").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 5)

 Next i

 

 Sheets("Q50").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 6)

 Next i
38

 

 Sheets("Q90").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 7)

 Next i

 

 Sheets("HIGH_NUM").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 11)

 Next i

 

 Sheets("HIGH_DUR").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 12)

 Next i

 

 Sheets("LOW_NUM").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMLFY

 ActiveCell(A, i + 1).Value = QWY(i, 13)

 Next i

 

 Sheets("LOW_DUR").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMLFY

 ActiveCell(A, i + 1).Value = QWY(i, 14)

 Next i

 

 Sheets("PDC50").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 16)

 Next i

 

 Sheets("CV_LOG_DAILY").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = (10 ^ (QWY(i, 15)) - 1) ^ 0.5

 Next i

 

 Sheets("MONTH_MAX").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 If QWY(i, 18) > 0 Then

 ActiveCell(A, i + 1).Value = QWY(i, 18)
39

 End If

 Next i

 

 Sheets("MONTH_MIN").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMLFY

 If QWY(i, 19) > 0 Then

 ActiveCell(A, i + 1).Value = QWY(i, 19)

 End If

 Next i

 

 Sheets("VAR_MONTH").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 17)

 Next i

 

 Sheets("BFR").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 20)

 Next i

 

 Sheets("SFR").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMWY

 ActiveCell(A, i + 1).Value = QWY(i, 21)

 Next i

 

'Write mean flow and 1st percentile of percent daily change to SUMMARY sheet

 Sheets("SUMMARY").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 ActiveCell(A, 2) = NUMWY

 ActiveCell(A, 3) = QMEAN

 ActiveCell(A, 4) = QEX(10)

 ActiveCell(A, 5) = QEX(100)

 ActiveCell(A, 6) = QEX(250)

 ActiveCell(A, 7) = QEX(500)

 ActiveCell(A, 8) = QEX(750)

 ActiveCell(A, 9) = QEX(900)

 ActiveCell(A, 10) = QEX(990)

 ActiveCell(A, 11) = PDC50

 ActiveCell(A, 12) = CVQ

 ActiveCell(A, 13) = CVLOGQ

 ActiveCell(A, 14) = CVLOGQMAX

 Range(ActiveCell(0, 15), ActiveCell(0, 40)).Copy

 Range(ActiveCell(A, 15), ActiveCell(A, 40)).Select

 ActiveSheet.Paste

 Range("A3").Select

 

'Write monthly Qmean to sheet "MONTHLY_QMEAN"
40

' If WRITE_RESULTS = 1 Then

 Sheets("MONTHLY_QMEAN").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To m

 ActiveCell(A, i + 1) = QMONTH(i, 3)

 Next i

 

'Write monthly flow stats to "MONTHQ"

 Sheets("MONTHQ").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To 12

 If i > 9 Then

 j = i - 9

 Else

 j = i + 3

 End If

 ActiveCell(A, j + 1) = MONTHQSUM(i) / NUMWY

 ActiveCell(A, j + 14) = CVMONTHQ(i)

 ActiveCell(A, j + 27) = MEDMONTHQ(i)

 Next i

 

'Write monthly flow stats to "MONTH_EVENTS"

 Sheets("MONTH_EVENTS").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To 12

 If i > 9 Then

 j = i - 9

 Else

 j = i + 3

 End If

 ActiveCell(A, j + 1) = MEDMONTHHIGHDUR(i)

 ActiveCell(A, j + 14) = YEARSHIGHFLOWINMONTH(i) / NUMWY

 ActiveCell(A, j + 27) = MEDMONTHLOWDUR(i)

 ActiveCell(A, j + 40) = YEARSLOWFLOWINMONTH(i) / NUMWY

 Next i

 

'Write bioperiod flow stats

 Sheets("BIOPERIODS").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUM_BPS

 ActiveCell(A, i + 1) = PRIOR_APPRO(i)

 ActiveCell(A, i + 14) = EFLOW_STD(i)

 ActiveCell(A, i + 28) = ADD_ALLOCATION(i)

 Next i

 

'Write statistics for flow 100 days prior to sampling

 If DYFLOW100 > 0 Then

 Sheets("100_DAY_STATS").Select

 Range("A3").Select

 ActiveCell(A, 1) = FILE_NAME(L) & "_" & SCEN(L)

 ActiveCell(A, 2) = QMEAN100 / 100

 ActiveCell(A, 3) = QMAX100
41

 ActiveCell(A, 4) = QMIN100

 ActiveCell(A, 5) = DURHIGH100

 ActiveCell(A, 6) = DURLOW100

 ActiveCell(A, 7) = PDC100 / DYFLOW100

 End If

'INSERT ADDITIONAL COMMANDS FOR WRITING OUTPUT USING THE FOLLOWING LINES AS A TEMPLATE

' Sheets(NAME OF SHEET IN DOUBLE QUOTES).Select

' Range("A3").Select

' ActiveCell(A, 1) = FILE_NAME(l) & "_" & SCEN(l)

' ActiveCell(A, 2) = NAME OF VARIABLE

'End of writing output to worksheets except daily flows

' End If

'Write minimum, median, mean, and maximum daily flow for each month in the period of record

'to file for each scenario ending with "MONTH.CSV"

 If (WRITE_FLOWDUR = 1) Then

 MonthlyFile = FILE_NAME(L) & "_" & SCEN(L) & "_MONTH.CSV"

 Set NewBook = Workbooks.Add

 With NewBook

 .SaveAs Filename:=MonthlyFile, FileFormat:=xlCSV

 End With

 Range("A1").Select

 

 For i = 1 To m

'Monthly flow stats: year, month, minimum, median, mean, maximum, duration of high flows, and duration of low flows

 ActiveCell(i, 1) = QMONTH(i, 1)

 ActiveCell(i, 2) = QMONTH(i, 2)

 ActiveCell(i, 3) = QMONTH(i, 4)

 ActiveCell(i, 4) = QMONTH(i, 8)

 ActiveCell(i, 5) = QMONTH(i, 3)

 ActiveCell(i, 6) = QMONTH(i, 5)

 ActiveCell(i, 7) = QMONTH(i, 6)

 ActiveCell(i, 8) = QMONTH(i, 7)

 Next i

 ActiveWorkbook.Close SaveChanges:=True

'Write summary data from flow duration to "FLOW_DUR" file

 FlowDurFile = FILE_NAME(L) & "_" & SCEN(L) & "_FLOWDUR.CSV"

 Set NewBook = Workbooks.Add

 With NewBook

 .SaveAs Filename:=FlowDurFile, FileFormat:=xlCSV

 End With

 Range("A1").Select

 j = 0

 For i = 1 To 1000

 j = j + 1

 ActiveCell(j, 1) = i / 1000

 ActiveCell(j, 2) = QEX(i)

 Next i

 ActiveWorkbook.Close SaveChanges:=True

 End If

'WRITE DAILY TIME-SERIES OF STREAMFLOW TO "QDAILY" AND CALCULATE EFLOWS

'Columns for each site are date, streamflow record, flow after appropriation and allocation (EFLOW scenario).
42

' If WRITE_RESULTS = 1 Then

 If WRITE_DAILYS = 1 Then

 Sheets("QDAILY").Select

 Range("A1").Select

 b = 1

 Do Until ActiveCell(1, b) = ""

 b = b + 1

 Loop

 ActiveCell(1, b) = "DATE"

 ActiveCell(1, b + 1) = FILE_NAME(L) & "_" & SCEN(L)

 For i = 1 To NUMDAY

 ActiveCell(i + 1, b) = DailyFlow(i, 3) & " - " & DailyFlow(i, 4) & " - " & DailyFlow(i, 2)

 ActiveCell(i + 1, b + 1) = DailyFlow(i, 5)

 Next i

 End If

 EFLOW_RUN = 2

 Else

'Determine eflow standards and additional allocations based on statistics for the initial scenario (EFLOW_RUN = 0)

'Otherwise (EFLOW_RUN=1 or 2), this section in skipped because the standards and allocations have already been 

calculated.

 Select Case EFLOW_RUN

 Case 0

 Select Case PRIOR_APPRO_BASIS

 Case 2

 For i = 1 To NUM_BPS

 PRIOR_APPRO(i) = QMEAN * PRIOR_APPRO_PERCENT(i)

 Next i

 

 Case 3

 For i = 1 To NUM_BPS

 j = Int(PRIOR_APPRO_PERCENT(i) * 1000)

 PRIOR_APPRO(i) = QEX(j)

 Next i

'Case 4 was already calculated

 Case 5

 For i = 1 To 12

 PRIOR_APPRO(i) = BPQMEAN(i) * PRIOR_APPRO_PERCENT(i)

 Next i

 Case 6

 If NUMLFY > 10 Then

 For i = 1 To NUM_BPS

 PRIOR_APPRO(i) = EST7Q10 * PRIOR_APPRO_PERCENT(i)

 Next i

 Else

 MsgBox ("Record not long enough to estimate 7Q10")

 For i = 1 To 12

 EFLOW_STD(i) = 0

 Next i

 End If

 

 Case 7

 For i = 1 To NUM_BPS

 PRIOR_APPRO(i) = PRIOR_APPRO_PERCENT(i)
43

 Next i

 End Select

 

'Establish environmental flow standard for each bioperiod (i)

 Select Case EFLOW_BASIS

'Case 1 is calculated when the daily flows are written to sheet QDAILY

 Case 2

 For i = 1 To NUM_BPS

 EFLOW_STD(i) = QMEAN * EFLOW_PERCENT(i)

 Next i

 Case 3

 For i = 1 To NUM_BPS

 j = Int(EFLOW_PERCENT(i) * 1000)

 EFLOW_STD(i) = QEX(j)

 Next i

'Case 4 was already calculated

 Case 5

 For i = 1 To 12

 EFLOW_STD(i) = BPQMEAN(i) * EFLOW_PERCENT(i)

 Next i

 Case 6

 If NUMLFY > 10 Then

 For i = 1 To NUM_BPS

 EFLOW_STD(i) = EST7Q10 * EFLOW_PERCENT(i)

 Next i

 Else

 MsgBox ("Record not long enough to estimate 7Q10")

 For i = 1 To 12

 EFLOW_STD(i) = 0

 Next i

 End If

 

 Case 7

 For i = 1 To NUM_BPS

 EFLOW_STD(i) = EFLOW_PERCENT(i)

 Next i

 End Select

'Establish maximum ADD_ALLOCATION for each period (i)

 Select Case ADD_ALLOCATION_BASIS

'Case 1 is calculated when the time-series is written to QDAILY sheet.

 Case 2

 For i = 1 To NUM_BPS

 ADD_ALLOCATION(i) = QMEAN * ADD_ALLOCATION_PERCENT(i)

 Next i

 

 Case 3

 For i = 1 To NUM_BPS

 j = Int(ADD_ALLOCATION_PERCENT(i) * 1000)

 ADD_ALLOCATION(i) = QEX(j)

 Next i
44

'Case 4 was already calculated

 Case 5

 For i = 1 To 12

 ADD_ALLOCATION(i) = BPQMEAN(i) * ADD_ALLOCATION_PERCENT(i)

 Next i

 Case 6

 If NUMLFY > 10 Then

 For i = 1 To NUM_BPS

 ADD_ALLOCATION(i) = EST7Q10 * ADD_ALLOCATION_PERCENT(i)

 Next i

 Else

 MsgBox ("Record not long enough to estimate 7Q10")

 For i = 1 To 12

 ADD_ALLOCATION(i) = 0

 Next i

 End If

 

 Case 7

 For i = 1 To NUM_BPS

 ADD_ALLOCATION(i) = ADD_ALLOCATION_PERCENT(i)

 Next i

 End Select

'Daily prior appropriations and eflow standards

 For i = 1 To NUMDAY

 QDAY = DailyFlow(i, 5)

 If PRIOR_APPRO_BASIS = 1 Then

 PRIOR = PRIOR_APPRO_PERCENT(DailyFlow(i, 11)) * QDAY

 Else

 PRIOR = PRIOR_APPRO(DailyFlow(i, 11))

 End If

 If PRIOR > QDAY Then

 PRIOR = QDAY

 EFLOW = 0

 ALLOC = 0

 Else

 If EFLOW_BASIS = 1 Then

 EFLOW = EFLOW_PERCENT(DailyFlow(i, 11)) * QDAY

 Else

 EFLOW = EFLOW_STD(DailyFlow(i, 11))

 End If

 If EFLOW > QDAY - PRIOR Then

 EFLOW = QDAY - PRIOR

 ALLOC = 0

 Else

 If ADD_ALLOCATION_BASIS = 1 Then

 ALLOC = ADD_ALLOCATION_PERCENT(DailyFlow(i, 11)) * QDAY

 Else

 ALLOC = ADD_ALLOCATION(DailyFlow(i, 11))

 End If

 If ALLOC > QDAY - PRIOR - EFLOW Then

 ALLOC = QDAY - PRIOR - EFLOW

 End If

 End If

 End If
45

'Time-series of streamflow after prior appropriation and additional allocations for EFLOW_RUN statistics

 DailyFlow(i, 5) = QDAY - PRIOR - ALLOC

 If DailyFlow(i, 5) < 0 Then

 MsgBox ("whoa")

 End If

 Next i

'Switches the value of ELFLOW_RUN from 0 to 1

 EFLOW_RUN = 1

 

'Switches the value of ELFLOW_RUN from 1 to 2

 Case 1

 EFLOW_RUN = 2

 

 Case 2

 EFLOW_RUN = 2

 End Select

'If eflows, and allocation are not specified, then the program will just loop through the data once and calculate statistics

 End If

'Loop for EFLOW_RUN<2

 Loop

'If site doesn't have the minimum number of years, above lines are skipped

 End If

 

'RE-INITIALIZE VARIABLES AND ARRAYS FOR EACH SCENARIO

 STARTWY = 0

 ENDWY = 0

 THRESH_HIGH_TYPE = 0

 THRESH_LOW_TYPE = 0

 CALC_EFLOW = 0

 WRITE_FLOWDUR = 0

 WRITE_DAILYS = 0

 EFLOW_RUN = 0

 WRITE_RESULTS = 0

 WY = 0

 YR = 0

 MN = 0

 DY = 0

 WY_NEXTDAY = 0

 LFY = 0

 LEAPYEAR = 0

 NUMDAY = 0

 NUMWY = 0

 NUMLFY = 0

 DYINYEAR = 0

 DYINMONTH = 0

 NEXTLOWFLOWYEAR = 0

 ZERODAY = 0

 NUM_BPS = 0

 PRIOR_APPRO_BASIS = 0

 EFLOW_BASIS = 0

 ADD_ALLOCATION_BASIS = 0

 PRIOR = 0

 EFLOW = 0
46

 ALLOC = 0

 Erase PRIOR_APPRO_PERCENT, PRIOR_APPRO, EFLOW_PERCENT, EFLOW_STD, 

ADD_ALLOCATION_PERCENT, ADD_ALLOCATION

 Erase DailyFlow, TempDailyFlow, QEX, WYDAYS, QWY, QDYWY, QMONTH

 Erase QBP, BPQSUM, BPQMEAN, BP_START, DYINBP, MONTHQSUM, CVMONTHQ, MEDMONTHQ, 

LOGQMAX

 Erase MEDMONTHHIGHDUR, MEDMONTHLOWDUR, YEARSLOWFLOWINMONTH, 

YEARSHIGHFLOWINMONTH

 Q = 0

 QSUM = 0

 QMEAN = 0

 LOGQSUM = 0

 LOGQMEAN = 0

 CVQ = 0

 SUMSQDEVQ = 0

 CVLOGQ = 0

 SUMSQDEVLOGQ = 0

 MEANLOGQMAX = 0

 SSQDEVLOGQMAX = 0

 VARLOGQMAX = 0

 CVLOGQMAX = 0

 PDC50 = 0

 TENTH = 0

 MED = 0

 NINETITH = 0

 HIGH_FLOW = 0

 HIGH_YEAR = 0

 DURHIGH = 0

 LOW_FLOW = 0

 LOW_YEAR = 0

 DURLOW = 0

 TH = 0

 THRESH_HIGH = 0

 TL = 0

 THRESH_LOW = 0

 DYS100PRIOR = 0

 FIRST100DAY = 0

 DURHIGH100 = 0

 DURLOW100 = 0

 QMAX100 = 0

 QMIN100 = 0

 QMEAN100 = 0

 PDC100 = 0

 SAMPLEDATE = 0

 CURRENTDATE = 0

 Next L

End Sub
Printed on recycled paper

Konrad— Environmental Flow Allocation and Statistics Calculator—Open-File Report 2011–1166
Publishing support provided by the U.S. Geological Survey

Publishing Network, Tacoma Publishing Service Center

For more information concerning the research in this report, contact the

 Director, Washington Water Science Center

U.S. Geological Survey

934 Broadway, Suite 300

Tacoma, Washington 98402

http://wa.water.usgs.gov