Basing Average function range on Date?

[DangerMouse]

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

 

[Bob Phillips]

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

 

[DangerMouse]

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

 

[DangerMouse]

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?

 

[Bob Phillips]

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