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?

Here's an array formula**.

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

=INDEX(A1:A20,MATCH(TRUE,SUBTOTAL(9,OFFSET(B1,,,ROW(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" <(E-Mail Removed)> wrote in message
news:CC5357D6-EBFE-4025-839C-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
news:CC5357D6-EBFE-4025-839C-(E-Mail Removed)...
>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?

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,,,ROW(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" <(E-Mail Removed)> wrote in message
> news:CC5357D6-EBFE-4025-839C-(E-Mail Removed)...
> >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?
>
>
>

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(OFFSET(DataNumbers,MATCH(StartDate,DateRange,1),0,GENumber,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" <(E-Mail Removed)> wrote in message
news:43371926-9BD6-4D8C-A527-(E-Mail Removed)...
> 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,,,ROW(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" <(E-Mail Removed)> wrote in message
>> news:CC5357D6-EBFE-4025-839C-(E-Mail Removed)...
>> >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?
>>
>>
>>

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(OFFSET(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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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(OFFSET(DataNumbers,MATCH(StartDate,DateRange,1),0,GENumber,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" <(E-Mail Removed)> wrote in message
> news:43371926-9BD6-4D8C-A527-(E-Mail Removed)...
>> 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,,,ROW(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" <(E-Mail Removed)> wrote in message
>>> news:CC5357D6-EBFE-4025-839C-(E-Mail Removed)...
>>> >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?
>>>
>>>
>>>
>
>

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" <(E-Mail Removed)> wrote in message
news:43371926-9BD6-4D8C-A527-(E-Mail Removed)...
> 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,,,ROW(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" <(E-Mail Removed)> wrote in message
>> news:CC5357D6-EBFE-4025-839C-(E-Mail Removed)...
>> >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?
>>
>>
>>