-1

I have is a dataset of daily stream runoff values for the past 11 years. It looks like this:

  ID    Year    DD  Apr     May     Jun     Jul     Aug     Sep     Oct 
08HF004 2000    1   26.5    37.6    18.3    12.3    8.35    5.19    7.98                        
08HF004 2000    2   28.8    25.8    19.3    10.4    6.86    4.61    5.86                        
08HF004 2000    3   34.7    22.8    25.9    9.32    5.82    4.07    4.71                            
08HF004 2000    4   29.7    19.4    33.8    9.16    5.5     3.61    4.01                            
08HF004 2000    5   19.9    17.5    38.6    9.01    5.39    3.32    3.53                            
08HF004 2000    6   15      14.6    33.1    9.04    5.22    3.32    3.2                     
08HF004 2000    7   11.6    14.1    27      10.3    4.83    4.55    2.96

...and so forth for 400+ more lines. What I want to do is use VBA to select all the values from each month (April 2000, May 2000, etc) and figure out the average and standard deviation from each month and send them to a cell in the worksheet, or a cell in another worksheet, or, ideally, a new workbook in the directory I can just call "results".

pnuts
  • 58,317
  • 11
  • 87
  • 139
CWHz
  • 3
  • 1
  • 3
  • What have you tried so far? Post back with some code, and what issues you are running into, and many here will be glad to help. – Kyle Apr 08 '15 at 18:55
  • Any reason you can't just use the native [`AVERAGE`](https://support.office.com/en-ZA/article/average-function-7f473887-4818-4212-b85f-474a5be67b8a)- and [`STDEV.S`](https://support.office.com/en-za/article/STDEVS-function-f4660ce6-414a-4225-b671-925fceccbf7b)-formulas? For that matter, what have you tried so far? – eirikdaude Apr 08 '15 at 18:56
  • 1
    This can be done without VBA with the `AVERAGE` and `STDDEV.S` formulas mentioned by @eirikdaude. If you have more than one year and need to filter the data, look at `SUMPRODUCT`. – PeterT Apr 08 '15 at 18:58
  • Whoops sorry I should have clarified, I actually have a bunch of workbooks like this one that I want to do this same process on so I want to develop a macro that can do it for me. Up until this point I've been recording macros then nesting the recorded code in a VBA snippet I found that loops through all the workbooks in the directory. The reason I can't do this again is because the workbooks all vary slightly in the amount of data present - eg some are missing years, cell values are not consistent etc. – CWHz Apr 08 '15 at 20:48
  • If the format of the workbooks isn't consistent, I suspect you'll have trouble implementing a solution for this in VBA too. – eirikdaude Apr 09 '15 at 00:09

1 Answers1

0

I suggest a PivotTable (one month per table) - Year for ROWS and say April for VALUES (once as Average of and once as StdDev of or StdDevp of).

Or you might 'flatten' the data (eg as shown here) and use different views of a single PivotTable:

SO29522989 example

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139