Excel formula help needed

[henio]

Hi all,

I have a sheet containing two columns:

Date Qty
1 Nov 1
2 Nov 2
3 Nov 1
4 Nov 4
etc

When the user enters a date on another sheet, I would like to get the average Qty for the prior 7 days.

e.g. User enters date of 12 Nov. I would like to retrieve the average qty for the dates 6 Nov thru 12 Nov. I've tried an array formula with nested 'if's with no success.

Any help would be appreciated.

Cheers,
Henio

 

[ETID]

Look to the database functions of excel

dcount
dsum
daverage

the help illustrates the useage

you will setup you citeria as a copy of the headings in your "data columns" in your case add another date col

under one date heading...=&quot;<=&quot;& start date ,
under the other the formula =&quot;>=&quot;&(cell_ref of start date-7)

usage:
=davergage(columns with date and qty,&quot;qty&quot;,cel ref copy of copy headers of headers,

 

[SkipVought]

Hi,

Here's a way.

=SUM(OFFSET($A$2,MATCH(C11,$A$2:$A$19,0)-7,1,7,1))/7

where c11 has the date value you are looking up.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

Ooops...


=daverage(columns with date and qty,&quot;qty&quot;,cel ref copy of data headers)