Formula help needed !

[thegooner]

Hi

Workbook contains 2 worksheets.

Worksheet 1:

Column A - contains a list of dates going down in ascending order.
Column B - contains a numeric value for the adjacent date.

eg:
12/05/2005 // 125.25
13/05/2005 // 252.62

Worksheet 2:

User enters a date into cell A1

I need a formula in cell B1 that will look at the date entered int
cell A1, find the corresponding value for it on worksheet 1, and the
find the total of that value PLUS all values before it (up to a maximu
of 4 rows above).

So, if the date entered = 06/05/2005, and worksheet 1 looked lik
this:

01/05/2005 // 12.53
02/05/2005 // 14.26
03/05/2005 // 125.26
04/05/2005 // 14.24
05/05/2005 // 22.25
06/05/2005 // 31.31
07/05/2005 // 112.68

I'd want the SUM of all cells from B2 to B6 (14.26 > 31.31)

Anyone help on this one?

Thank

 

[Bob Phillips]

=SUM(INDEX(Sheet1!B1:B20,MAX(1,MATCH(A1,Sheet1!A1:A20,0)-4)):INDEX(Sheet1!B1
:B20,MATCH(A1,Sheet1!A1:A20,0)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)