You can do this with a formula

=AVERAGE(IF((Sheet1!A1:A20<=Sheet2!A1)*(Sheet1!A1: A20=Sheet2!A1-5),Sheet1!B
1:B20))

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nougain" wrote in
message ...

I have two sheets in my workbook. In sheet 1, Column A (say sellingDate)
has dates and column B (say apples) has some numbers. In sheet 2 I have
column A (say givenFridayDate) where I have dates and column B
(calAverage) where I need to calculate some valve using the UDF.

What I need to do is to have some function that takes givenFridayDate
value and return average of apples sold for sellingDate less than equal
to givenFridayDate and greater than the date that falls 5 days back from
givenFridayDate (i.e. Last sunday).

I don't know how to use Ranges and filter the data that falls within
given dates. Please suggest some solution. Thanks.


--
nougain
------------------------------------------------------------------------
nougain's Profile:
http://www.excelforum.com/member.php...o&userid=32031
View this thread: http://www.excelforum.com/showthread...hreadid=517826