Average if is not greater than today

[jermsalerms]

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)

 

[Die_Another_Day]

= 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

 

[Die_Another_Day]

= 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]

the first part worked great...except I was wondering if you know a
variation of this that WOULD NOT count a cell that has a "0" in it if
the date IS less than today. There are some months were there are no
tansactions and I would like to exclude this from the average for a
particular column.

Also the question about the chart did not work since the cells that
respresent future transaction have zeros in them.

 

[jermsalerms]

the first part worked great...except I was wondering if you know a
variation of this that WOULD NOT count a cell that has a "0" in it if
the date IS less than today. There are some months were there are no
tansactions and I would like to exclude this from the average for a
particular column.

Also the question about the chart did not work since the cells that
respresent future transaction have zeros in them.

 

[Die_Another_Day]

Nest another if statement to fix the zero's
= Average(If(A2:A37<Today(),If(F2:F27<>0,F2:F27,""),""))

HTH

Charles Chickering

 

[Die_Another_Day]

Nest another if statement to fix the zero's
= Average(If(A2:A37<Today(),If(F2:F27<>0,F2:F27,""),""))

HTH

Charles Chickering

 

[jermsalerms]

that extra if statement worked great.

Any ideas on part two (the charting)....based on my last post.

"
Also the question about the chart did not work since the cells that
respresent future transaction have zeros in them.

For example:

A2 = 6/1/06 F2 = 4.0%
A3 = 7/1/06 F3 = 3.5%
A4 = 8/1/06 F4 = 2.9%
A5 = 9/1/06 F5 = 2.1%
A6 = 10/1/06 F6 = 0.0%

For my chart I only want to chart out information that is < Today(). So
that if I look at the chart today it will chart F2:F4, but if I look at
the chart next month it will chart F2:F5.
"

 

[Die_Another_Day]

Can you email me the spreadsheet?

(e-mail address removed)
Charles