ignore null data in a sum

[ruberg]

It seems like there should be a simple solution for this problem, but I've been tinkering with it for awhile and can't come up with anything.

I have two sheets in my workbook: one holds data (Sheet2), the other processes the data (Sheet1). Sheet2 has temperature data for each hour of every day during the year 1997 - about 9000 rows. Sheet1 averages the temperatures for each day together, and therefore has only 365 rows.

However, some of the data is missing - null values are represented by the value -9999. Sheet1 only averages hourly data if there at least 22 non-null values. If there are fewer than 22 non-null values, a message is written into the cell to indicate such. (See day 119 on Sheet1 in the attached file for an example of this)

I have the formula worked out to this point; however, I'd like to add in one more constraint: if a day has 2 or fewer null values, I still would like to average the data, but omit the null values in the average. (See day 118 on Sheet1 in the attached file for an example of this)

My formula so far:

Please Login or Register  to view this content.

Any suggestions?

[martindwilson]

=IF(SUMPRODUCT(--(Sheet2!$B$1:$B$8780=B118),--(Sheet2!$C$1:$C$8780=-9999))<3,(SUMPRODUCT(--(Sheet2!$B$1:$B$8780=B118),--(Sheet2!$C$1:$C$8780<>-9999),--(Sheet2!$C$1:$C$8780)))/((SUMPRODUCT(--(Sheet2!$B$1:$B$8780=B118)))-SUMPRODUCT(--(Sheet2!$B$1:$B$8780=B118),--(Sheet2!$C$1:$C$8780=-9999))),0)

[ruberg]

That works beautifully. Thank you!