Basing Average function range on Date?

Hi all,

Many thanks for your assistance yesterday here - http://www.excelforum.com/showthread.php?t=553165

I've created a simple average function that extends range as its filled across columns. The columns relate to months. Where a month has not been completed yet I would like the range to be stunted until it has -- thus future months will only use the average based on current month.

E.G.

Data:

May - 2
June - 2
July - 2
August - 0

Resulting average:

June - Average(May:June)
July - Average(May:July)
August - Average(May:July) ---So it stops until August occurs.

Hope this kinda makes sense.

My column headings are dates so i'm hoping an IF function can be used.

Any help appreciated

Cheers

=AVERAGE(IF(A1:A12>0,A1:A12))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DangerMouse" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi all,
>
> Many thanks for your assistance yesterday here -
> http://www.excelforum.com/showthread.php?t=553165
>
> I've created a simple average function that extends range as its filled
> across columns. The columns relate to months. Where a month has not been
> completed yet I would like the range to be stunted until it has -- thus
> future months will only use the average based on current month.
>
> E.G.
>
> Data:
>
> May - 2
> June - 2
> July - 2
> August - 0
>
> Resulting average:
>
> June - Average(May:June)
> July - Average(May:July)
> August - Average(May:July) ---So it stops until August occurs.
>
> Hope this kinda makes sense.
>
> My column headings are dates so i'm hoping an IF function can be used.
>
> Any help appreciated
>
> Cheers
>
>
> --
> DangerMouse
> ------------------------------------------------------------------------
> DangerMouse's Profile:
http://www.excelforum.com/member.php...o&userid=27755
> View this thread: http://www.excelforum.com/showthread...hreadid=553554
>

Thanks for the reply, am I correct in thinking this limits the range to values that are greater than 0?

This is great apart from with historical values I need 0's to be included its future 0's i need ignored, hence my suggestion regarding date functions.

Cheers

I think im doing a terrible job of explaining myself here, but this is the type of formular I'm looking to create, however it needs to be a little more intelligent if possible, picking up the "month" thats closest to the current date, to a maximum of a specified range.

A1:A12 - Months, formatted as 01/01/06 etc.

B1:B12 - Data that needs averaging

Average - C2:C13 - Average(b1if(a2>(Today()),b1,b2))

What I hope to be able to do is in my Average forumla above - change the reference to B1, to the closest month to the current date in the range A1:A12.

Is this possible?

=AVERAGE(IF(A1:A12<TODAY(),B1:B12))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DangerMouse" <[email protected]>
wrote in message
news:[email protected]...
>
> I think im doing a terrible job of explaining myself here, but this is
> the type of formular I'm looking to create, however it needs to be a
> little more intelligent if possible, picking up the "month" thats
> closest to the current date, to a maximum of a specified range.
>
> A1:A12 - Months, formatted as 01/01/06 etc.
>
> B1:B12 - Data that needs averaging
>
> Average - C2:C13 - Average(b1if(a2>(Today()),b1,b2))
>
> What I hope to be able to do is in my Average forumla above - change
> the reference to B1, to the closest month to the current date in the
> range A1:A12.
>
> Is this possible?
>
>
> --
> DangerMouse
> ------------------------------------------------------------------------
> DangerMouse's Profile:
http://www.excelforum.com/member.php...o&userid=27755
> View this thread: http://www.excelforum.com/showthread...hreadid=553554
>