Oops, forgot to subtract 1 for the starting offset

=INDEX(OFFSET(DateRange,MATCH(StartDate,DateRange, 1)-1,0,GENumber,1),MATCH(TRUE,SUBTOTAL(9,OFFSET(OFFSE T(DataNumbers,MATCH(StartDate,DateRange,1)-1,0,GENumber,1),,,ROW(INDIRECT("1:"
& GENumber))))=GENumber,0),1)

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Charles Williams" wrote in message
...
Here is a small modification to Biff's array formula:

assuming that
DateRange is a dynamic range for the Date column (first column)
The Date column is in ascending date order (if not change the 1 in each
MATCH function to 0)
DataNumbers is a dynamic range for the numbers column (second column)
The lowest possible number in DataNumbers is 1
StartDate is the name of the cell containing the date you want to pick in
the date column
GENumber is name of the cell containing the magic 336 number

=INDEX(OFFSET(DateRange,MATCH(StartDate,DateRange, 1),0,GENumber,1),MATCH(TRUE,SUBTOTAL(9,OFFSET(OFFS ET(DataNumbers,MATCH(StartDate,DateRange,1),0,GENu mber,1),,,ROW(INDIRECT("1:"
& GENumber))))=GENumber,0),1)

Needs entering with CSE

regards
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Archengineer" wrote in message
...
Ok, I forgot to explain the information in the cells adequately. The
data in
the second column is a random number from 1 to 24 and both columns have
an
infinite number of cells (one new cell date and the corresponding data is
added each day). I am going to pick a "number" cell in the second column
based on the date in the first cell. From there I will add consecutive
cells
in the second column until they total 336 or greater. Once I reach at
least
336 the output must tell me the date at which it was reached. I hope
that
explains it better.


"T. Valko" wrote:

Here's an array formula**.

What if the total isn't *exactly* 336?

=INDEX(A1:A20,MATCH(TRUE,SUBTOTAL(9,OFFSET(B1,,,RO W(INDIRECT("1:"&ROWS(B1:B20)))))=E1,0))

Where E1 = 336

If the total of column B is <336 then the formula returns #N/A

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Archengineer" wrote in message
...
I have two columns. One column contains dates the other contains
numbers
from 1 to 24. I need to add consecutive cells in the second column
until
I
total 336 and then output the date at which that occurs. I've been
racking
my brain on how to do this and I think there's any easy answer, but I
can't
find it. Can someone help me?