ignore empty data across a range

[ruberg]

I'd like to compute the average of a few numbers, but only if the data has a certain number of non-null values. I've attached my Excel sheet for reference.

I have relevant data in columns B and D, where B represents a day and D represents temperature. I'd like to average the temperatures together for each day and place the result in column E. However, this must be done only if each day has at least 22 non-null values (null values are represented by -9999).

A perfect example is day 296 - my average is thrown completely off by the existence of several null values in the last half of the day.

In addition to the problem above, I'd also like to only compute the average using the non-null values (ie, if a day has 23 non-null values and 1 null value, I want to ignore the null value in the average calculation - see day 222 as an example).

Thanks in advance for the help!