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

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

[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.

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]

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

HTH

Charles Chickering
jermsalerms wrote:
> 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
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=574295

[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?

[email protected]
Charles

jermsalerms wrote:
> 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.
> "
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=574295