Adding Cells and Indexing
 

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?

Adding Cells and Indexing
 

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?

Adding Cells and Indexing
 

The numbers 1 to 24 only total to 300, and if you extend the series you
never get to exactly 336.

I suggest you add a helper column (C) that calculates your running total of
column B so that its easier to see whats happening

then for an exact match to 366 use the formula (not an array formula)
=INDEX($A$1:$A$24,MATCH(E1,$C$1:$C$24,0),1)

To get the first date where the running total exceeds 366 use
=INDEX($A$1:$A$24,MATCH(E1,$C$1:$C$24,1)+1,1)

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

"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?

Adding Cells and Indexing
 

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?

Adding Cells and Indexing
 

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?

Adding Cells and Indexing
 

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?

Adding Cells and Indexing
 

If you like UDFs this one runs about 3 times faster than the array formula.

Option Explicit
Option Base 1
Public Function GetDate(theDates As Range, theData As Range, startDate As
Variant, MagicNumber As Variant) As Variant

Dim vStartRow As Variant
Dim vData As Variant
Dim j As Long
Dim dTot As Double

On Error GoTo Finish
vStartRow = Application.Match(startDate, theDates, 1)

vData = theData.Cells(vStartRow, 1).Resize(MagicNumber, 1).Value2

For j = 1 To MagicNumber
dTot = dTot + vData(j, 1)
If dTot = MagicNumber Then
GetDate = theDates.Cells(vStartRow + j - 1, 1)
Exit For
End If
Next j
Finish:
If GetDate = 0 Then GetDate = CVErr(xlErrNA)
End Function

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?