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
Last edited by martindwilson; 07-23-2009 at 07:53 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Great, that's very helpful; thank you! However, I've encountered one strange problem: the formula works - clicking the "fx" button brings up the "function arguments" window and the result is correct. However, in the cell it shows up as a zero. Any ideas on what could be wrong? I've tried changing the formatting and extending the width of the column, but neither works.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks