more sorting

[I. Miller]

I have a 20,000+ row table with twelve columns of monthly precipitation data.
Each column is a month of the year and each row is a yearly record. Missing
data is denoted by -9999. As you can see from a sample of the data below,
yearly records (rows) vary from being complete to having differing amounts of
missing data. It's easy enough to sort out those rows with complete data but
I would also like to sort out those rows with one, two, or three missing data
points (i.e. rows with one, two or three -9999 values). I'm after the sum of
each row (total rain per year) so I would then like to take the rows with
missing data do two things: first, average the other data fields in that row
and use that average value in place of the missing field and then sum the
row, and second, use the average of the adjacent data fields (months) to file
in the missing field (month) and then sum the row.

203 202 -9999 231 96 186 152 646 139 430 169 209
150 148 26 81 448 262 328 568 -9999 -9999 66 301
0 41 121 46 779 198 701 511 107 192 418 249
150 120 310 185 279 672 -9999 -9999 -9999 -9999 -9999 -9999
-9999 108 0 139 448 470 753 82 355 -9999 157 66

Thank you!
Ian

 

[Barb Reinhardt]

Let's say your monthly data is in columns b-m .


For column 2 data
=SUMIF(B2:M2,"<>-9999",B2:M2)
This is the sum of months without a -9999 value entered

TO count the number of times -9999 is entered in the row, use this
=COUNTIF(B2:M2,"=-9999")


Without using VBA, I'd probably use autofilter to find those rows that have
-9999 in them and add the average you want.

Come back if you have more questions.

 

[I. Miller]

Barb, thank you for the post--very helpful!

I still have one question:

Is there anyway to automate taking the average of the months on either side
of a missing field to fill in that field? Take the following example:

203 202 -9999 231 96 186 152 646 139 430 169 209

With your previous instructions I was able to automate using an average
based on the 11 other months and use that value to place into the missing
field. Now I'd like to use the average of "202" and "231" to fill in "-9999".


Also, I suspect this type of data string will cause problems because the two
missing fields are next to one another. Here I'd like to use the average of
"568" and "301" to fill in both missing fields.

150 148 26 81 448 262 328 568 -9999 -9999 66 301

Thanks!
Ian

 

[Pete_UK]

You can't use a formula to change a value in another cell, so you
would have to use a bit of VBA (i.e. a macro) to do what you describe.

Pete

 

[Pete_UK]

Anyway, why wouldn't you take 568 and 66 as the basis of the average
in your last example?

Pete