I
I. Miller
I have yearly precipitation records divided by month. Missing values are
denoted by -9999. I am trying to calculate yearly totals. I have done it
(with help from this forum!) by averaging the present values and entering
that for the missing value (see second table). However, I would like to take
the records of months adjacent to months with missing records (-9999),
average those adjacent records, and use that value to add to the total. For
example, in row4, Jun. and Jul. are missing. If I take the average of the
other ten months I get 310 per month. Added to the total based on the other
ten months I get a yearly value of 3720 (see second table). However, if I
average May and Aug. in row4, I get 55. Added to the total of existing
months, I end up with 3210. Also note that there are many years where the
missing values occur at the end (row3). In the example of row3, I'd like to
use the average of Oct. and Jan. to add (twice in this case) to the total for
the rest of the months.
If I do need a macro, I'd much appreciate a suggested script. I don't have
much experience with VBA (only scipts that I could better do with worksheet
functions) but I certianly know how to edit and run them.
Jan. Feb. Mar. Apr. May Jun. Jul. Aug. Sep.
Oct. Nov. Dec.
row1 0 0 0 0 18 62 1218 2149 190
-9999 0 0
row2 0 0 0 0 0 120 1098 1188 632
-9999 0 0
row3 1030 340 2170 90 1110 0 0 0 330 290
-9999 -9999
row4 430 250 240 970 70 -9999 -9999 40 400 190
310 200 row5 400 -9999 690 560 450 500 -9999 20 610
10 420 2070
Tot. missing Mon_avg Year total
row1 3637 1 330.63 3967.63
row2 3038 1 276.18 3314.18
row3 5360 2 536 6432
row4 3100 2 310 3720
row5 6730 2 673 8076
Thank you!
Ian
denoted by -9999. I am trying to calculate yearly totals. I have done it
(with help from this forum!) by averaging the present values and entering
that for the missing value (see second table). However, I would like to take
the records of months adjacent to months with missing records (-9999),
average those adjacent records, and use that value to add to the total. For
example, in row4, Jun. and Jul. are missing. If I take the average of the
other ten months I get 310 per month. Added to the total based on the other
ten months I get a yearly value of 3720 (see second table). However, if I
average May and Aug. in row4, I get 55. Added to the total of existing
months, I end up with 3210. Also note that there are many years where the
missing values occur at the end (row3). In the example of row3, I'd like to
use the average of Oct. and Jan. to add (twice in this case) to the total for
the rest of the months.
If I do need a macro, I'd much appreciate a suggested script. I don't have
much experience with VBA (only scipts that I could better do with worksheet
functions) but I certianly know how to edit and run them.
Jan. Feb. Mar. Apr. May Jun. Jul. Aug. Sep.
Oct. Nov. Dec.
row1 0 0 0 0 18 62 1218 2149 190
-9999 0 0
row2 0 0 0 0 0 120 1098 1188 632
-9999 0 0
row3 1030 340 2170 90 1110 0 0 0 330 290
-9999 -9999
row4 430 250 240 970 70 -9999 -9999 40 400 190
310 200 row5 400 -9999 690 560 450 500 -9999 20 610
10 420 2070
Tot. missing Mon_avg Year total
row1 3637 1 330.63 3967.63
row2 3038 1 276.18 3314.18
row3 5360 2 536 6432
row4 3100 2 310 3720
row5 6730 2 673 8076
Thank you!
Ian