Hi,

I am trying to create a formula that will dynamically calculate a 6 period
average based on a date. I am using Excel 2003 SP2.

Specifically:
Row 1 contains a series of dates (Week Commencing)
Row 2 contains the data I would like to average (Man Days expended during
the week)

I would like to create a calculation that uses the current date, i.e.
references a cell that contains =TODAY(), and calculates the average number
of man days expended per week for the previous six weeks so that when the
current date changes, a new six-week average is generated.

I would be happy to mail the spreadsheet if required.

I very much look forward to hearing from someone.

Thanks,

Paul

Try something like this:

=AVERAGE(INDEX(2:2,1,MATCH(TODAY(),A1:O1,1)-6):INDEX(2:2,1,MATCH(TODAY(),A1:O1,1)-1))

Note: if the today is less than 6 weeks into your list, the formula returns
an error.
That can be prevented, but the formula would be more complicated.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP


"Lancer940" wrote:

Hi,

I am trying to create a formula that will dynamically calculate a 6 period
average based on a date. I am using Excel 2003 SP2.

Specifically:
Row 1 contains a series of dates (Week Commencing)
Row 2 contains the data I would like to average (Man Days expended during
the week)

I would like to create a calculation that uses the current date, i.e.
references a cell that contains =TODAY(), and calculates the average number
of man days expended per week for the previous six weeks so that when the
current date changes, a new six-week average is generated.

I would be happy to mail the spreadsheet if required.

I very much look forward to hearing from someone.

Thanks,

Paul

Create the following formulae:
in cell named 'myday': =TODAY()
in cell named 'stop': =myday-DATE(0,1,WEEKDAY(myday,2)-1)
in cell named 'start': =stop-DATE(0,1,6*7+1)

Name the area containing the dates 'dates'
Name the area containing the values 'values'


'6 period average'
=SUMPRODUCT(values*(datesstart)*(dates<stop))


Lancer940 wrote:
Hi,

I am trying to create a formula that will dynamically calculate a 6 period
average based on a date. I am using Excel 2003 SP2.

Specifically:
Row 1 contains a series of dates (Week Commencing)
Row 2 contains the data I would like to average (Man Days expended during
the week)

I would like to create a calculation that uses the current date, i.e.
references a cell that contains =TODAY(), and calculates the average number
of man days expended per week for the previous six weeks so that when the
current date changes, a new six-week average is generated.

I would be happy to mail the spreadsheet if required.

I very much look forward to hearing from someone.

Thanks,

Paul

One way:
=SUMPRODUCT((TODAY()-A2:A100<41)*B2:B100)/SUMPRODUCT(--(TODAY()-A2:A100<41))

HTH
Kostis Vezerides

Lancer940 wrote:
Hi,

I am trying to create a formula that will dynamically calculate a 6 period
average based on a date. I am using Excel 2003 SP2.

Specifically:
Row 1 contains a series of dates (Week Commencing)
Row 2 contains the data I would like to average (Man Days expended during
the week)

I would like to create a calculation that uses the current date, i.e.
references a cell that contains =TODAY(), and calculates the average number
of man days expended per week for the previous six weeks so that when the
current date changes, a new six-week average is generated.

I would be happy to mail the spreadsheet if required.

I very much look forward to hearing from someone.

Thanks,

Paul

Kostis,

Much appreciated, thanks.

Paul

"vezerid" wrote:

One way:
=SUMPRODUCT((TODAY()-A2:A100<41)*B2:B100)/SUMPRODUCT(--(TODAY()-A2:A100<41))

HTH
Kostis Vezerides

Lancer940 wrote:
Hi,

I am trying to create a formula that will dynamically calculate a 6 period
average based on a date. I am using Excel 2003 SP2.

Specifically:
Row 1 contains a series of dates (Week Commencing)
Row 2 contains the data I would like to average (Man Days expended during
the week)

I would like to create a calculation that uses the current date, i.e.
references a cell that contains =TODAY(), and calculates the average number
of man days expended per week for the previous six weeks so that when the
current date changes, a new six-week average is generated.

I would be happy to mail the spreadsheet if required.

I very much look forward to hearing from someone.

Thanks,

Paul

A very elegant formula. Thank you.

"bda75" wrote:

Create the following formulae:
in cell named 'myday': =TODAY()
in cell named 'stop': =myday-DATE(0,1,WEEKDAY(myday,2)-1)
in cell named 'start': =stop-DATE(0,1,6*7+1)

Name the area containing the dates 'dates'
Name the area containing the values 'values'


'6 period average'
=SUMPRODUCT(values*(datesstart)*(dates<stop))


Lancer940 wrote:
Hi,

I am trying to create a formula that will dynamically calculate a 6 period
average based on a date. I am using Excel 2003 SP2.

Specifically:
Row 1 contains a series of dates (Week Commencing)
Row 2 contains the data I would like to average (Man Days expended during
the week)

I would like to create a calculation that uses the current date, i.e.
references a cell that contains =TODAY(), and calculates the average number
of man days expended per week for the previous six weeks so that when the
current date changes, a new six-week average is generated.

I would be happy to mail the spreadsheet if required.

I very much look forward to hearing from someone.

Thanks,

Paul