Basing Average function range on Date?

D

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
 
D

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
 
D

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(b1:(if(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?
 
B

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:D[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top