Finding the Average of a dynamic date range

[theYaniac]

I am looking to write a formula to give me the average of a range of cells. The column that is being averaged receives a new row of data each day. However, the formula in the column is displays as a zero if it has no current data. I have tried to write an IF formula to leave the cells blank that are a later date than today but can't get the zero to not display. Column J is the column used in the Average formula. The formulas are as follows:
Cell C5 "average expectancy" : =AVERAGE($J$9:$J$261) - All rows in J that are a zero value, later than todays date are diluting the answer. I would like to average only the cells from current date and earlier.
Column J: IF(E9="","",E9/D9) returns the daily risk to reward.
The cells in column E are being totaled by date in a table (E9 =SUMIF(tblTradeLog[Date Exited],C9,tblTradeLog[P&L ])

 

[etaf]

will an Averageif do
=AVERAGEIF($C$9:$C$261,"<"&TODAY(),$J$9:$J$261)

EDIT Fluff answered as posted

or excluding 0

=AVERAGEIF($J$9:$J$261,"<>"&0,$J$9:$J$261)

 

[Fluff]

How about

=AVERAGEIF($J$9:$J$261,"<>0")

 

[theYaniac]

will an Averageif do
=AVERAGEIF($C$9:$C$261,"<"&TODAY(),$J$9:$J$261)

EDIT Fluff answered as posted

or excluding 0

=AVERAGEIF($J$9:$J$261,"<>"&0,$J$9:$J$261)

=AVERAGEIF($J$9:$J$261,"<>"&0,$J$9:$J$261) this worked. Thank you

 

[theYaniac]

How about

=AVERAGEIF($J$9:$J$261,"<>0")

this also works. Much appreciated

 

[etaf]

=AVERAGEIF($J$9:$J$261,"<>"&0,$J$9:$J$261) this worked. Thank you

Fluff post is better , the 2nd range in mine is unneccessary

 

[Fluff]

Glad we could help & thanks fro the feedback.