ignore empty data across a range

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!

with helper columns to give days 1-365 col f and one to count non null values col g then use averageif as in col e (note its an array formula confirmed with ctrl+shift+enter)
if you dont want the extra column g
use
=IF(SUMPRODUCT(--(B1:B8760=F1),--(D1:D8760<>-9999))>21,AVERAGE(IF(B1:B8760=F1,D1:D8760)),"less than 22 non null values") in col e instead (again array entered)
see my sig on how to enter arrays

does f9 make it work

f9 recalulates
perhaps you should try copying the whole lot to a new work sheet.
i have seen this happen before and cant for the life of me remember how i cured it.

to recalculate