= Average(If(A2:A37<Today(),F2:F27,""))
Press Ctrl+Shift+Enter as this is an array formula
as for the second question, Goto Tools, Options, Chart Tab, Check
Active Chart Plot empty values as: Not Plotted


Charles Chickering

jermsalerms wrote:
This is a two part question.
I have a table with sales data that I am trying to average and chart.
Each row repesents data for each month (36 rows one for each month for
3 years worth of data). The information is all being pulled from other
worksheets and in months that have not occurred yet (Sept, oct, nov,
dec) the data is a "0".

1. I need a formula that will average only the current month and months
that have already occurred keeping in mind that the average is more than
a 1yr history.

2. I need to figure out how to apply data in row F to a chart so that
the chart is only charting data from the current month back and not
including future months.

A2:37 contains date in mmm-yy format
F2:37 contains data to be averaged (and data for chart)


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=574295