In the analysis of water resources data, summaries of data can be helpful. For example, you may be interested in summarizing flow data for a stream. Flow data can often be found on the USGS website.
An example of daily average flow data for the White River at Newport can be found here:
USGS White River at Newport Daily Flow Data
This gage is located on the White River in Arkansas and has a period of record extending from October 1927 to present.
For this data set, the user may want to compute the overall average, the average for each month, and to determine the maximum flow value along with the associated date. Using Visual Basic in Excel for these three computations is shown below. It should be noted that a smaller dataset is used in these computations as a proof of concept and for testing of the code.
In the figure below, the setup of the spreadsheet is shown. In column A, I have put the dates associated with my time series data. In column B, I have put the data. I have three CommandButtons. These are titled "Average", "July Average", and "maximum value". Each of these has code associated with them to compute the desired result. The result for the average value will appear in cell F10. The result for the July average will appear in cell F11. The maximum value and associated date will appear in cells E14 and F14, respectively.
Computation of the Average
The code associated with the "Average" CommandButton is shown below. In this code, I want to step through each value and add that value to my running sum. I also want to keep track of how many values I have stepped through. I begin by initializing my running sum and running count to zero.
I want to start in cell B1. As long as the cell is not empty (Do Until IsEmpty(ActiveCell) loop), I offset my location by 1 row and zero columns and make that the active cell. So, the code moves from cell B1 to B2 and begins summing the values and keeping track of the count. In our example, when we get to cell B9 (last row of data), the loop continues once more to cell B10. Since there is not value there, it adds nothing to the sum, but it has incremented my count by one. It then exits the loop since it is an empty cell. Since I have one extra in my count, I then subtract one from the count when computing my average.
Computation of the July Average
Computing the average for only the month of July uses similar logic to that shown above. However, there are a few differences. The code for this computation is shown below.
For this code, the variable, Monthnum, is set to an Integer type. For this code, I started in cell A1. I want to get the flow values so I move over one column to the right and down one row. I then want to check to see if the month is July so I move back one column to the left. If the month is July (Monthnum = 7), I then update the running sum for July, and I update the count by 1. Once all of the values in the table have been checked, the average is computed by dividing the running sum by the count. In this case, the count value is used without subtracting 1 since the count is only updated if the month is July.
Computation of the Maximum Value and Associated Date
The code for computing the maximum value and the associated date is shown below.
For this code, the variable, maxdate, is set to a type of Date.
I first initialized the variable, maxval, to zero. I begin the code in cell B1. I then move down one row for each step in the loop. The variable, maxval, will either remain the same or be updated to the value in the active cell. If the active cell value exceeds the currently stored maximum value, then the variable maxval is updated. If it is less than or equal to the currently stored maximum value, then the variable remains as its current value. The maxdate (stored as a Date type) is updated only when a new maximum value occurs. If the maximum value occurs several times throughout the time series, only the first date will show. If the user wants to show the multiple dates when it occurs, additional coding is needed. This will be shown in a future post.
Results
Once the coding is done, each one of the buttons is clicked to provide the results. This is shown below.
Comments
Post a Comment