Progressive summing

Hello,
I am looking for a method in Excel to count the number of steps one should
take through a column with numbers in order to reach a certain threshold
value. Example: if cells A1:A8 contain 1, 2, 1, 3, 6, 1, 1, 2
How can I calculate in the next column (B1:B8) for each cell the number of
steps one should take through column A in order to reach that the sum of the
next x steps is >= 4?
In the previous example the result should be (B1:B8): 3 (1+2+1), 3 (2+1+3),
2 (1+3), 2 (3+6), 1 (6), 3 (1+1+2), #N/A, #N/A

Can somebody help me?
Many thanks,
Maarten

Maarten,

How many steps might it take? Always 3 or less? Or many hundreds?

That will impact the possible solutions.

HTH,
Bernie
MS Excel MVP


"Maarten" <Maarten@discussions.microsoft.com> wrote in message
news:13658E38-E9DE-40DB-A010-A18097AC5A71@microsoft.com...
> Hello,
> I am looking for a method in Excel to count the number of steps one should
> take through a column with numbers in order to reach a certain threshold
> value. Example: if cells A1:A8 contain 1, 2, 1, 3, 6, 1, 1, 2
> How can I calculate in the next column (B1:B8) for each cell the number of
> steps one should take through column A in order to reach that the sum of
the
> next x steps is >= 4?
> In the previous example the result should be (B1:B8): 3 (1+2+1), 3
(2+1+3),
> 2 (1+3), 2 (3+6), 1 (6), 3 (1+1+2), #N/A, #N/A
>
> Can somebody help me?
> Many thanks,
> Maarten
>

If there are always integers greater than 0 in column A, enter in B1
=if(A1>=4,1,if(A1+A2>=4,2,if(A1+A2+A3>=4,3,if(A1+A2+A3+A4>=4,4,na()))))
and drag down.

"Maarten" wrote:

> Hello,
> I am looking for a method in Excel to count the number of steps one should
> take through a column with numbers in order to reach a certain threshold
> value. Example: if cells A1:A8 contain 1, 2, 1, 3, 6, 1, 1, 2
> How can I calculate in the next column (B1:B8) for each cell the number of
> steps one should take through column A in order to reach that the sum of the
> next x steps is >= 4?
> In the previous example the result should be (B1:B8): 3 (1+2+1), 3 (2+1+3),
> 2 (1+3), 2 (3+6), 1 (6), 3 (1+1+2), #N/A, #N/A
>
> Can somebody help me?
> Many thanks,
> Maarten
>

It might take up to a few hundred steps
The solution 'bj' posted works if the number of cells is limited (like in
the example), but is very laborious if the column contains lots of cells

"Bernie Deitrick" wrote:

> Maarten,
>
> How many steps might it take? Always 3 or less? Or many hundreds?
>
> That will impact the possible solutions.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Maarten" <Maarten@discussions.microsoft.com> wrote in message
> news:13658E38-E9DE-40DB-A010-A18097AC5A71@microsoft.com...
> > Hello,
> > I am looking for a method in Excel to count the number of steps one should
> > take through a column with numbers in order to reach a certain threshold
> > value. Example: if cells A1:A8 contain 1, 2, 1, 3, 6, 1, 1, 2
> > How can I calculate in the next column (B1:B8) for each cell the number of
> > steps one should take through column A in order to reach that the sum of
> the
> > next x steps is >= 4?
> > In the previous example the result should be (B1:B8): 3 (1+2+1), 3
> (2+1+3),
> > 2 (1+3), 2 (3+6), 1 (6), 3 (1+1+2), #N/A, #N/A
> >
> > Can somebody help me?
> > Many thanks,
> > Maarten
> >
>
>
>

Maarten,

Then I would recommend using a User-Defined-Function, definition below, used
like this, relative to your example:

=ProgSum(A1:A$8,4)

Note the $8 - you should anchor the lowest cell to reduce calc time.

Copy the code into a codemodule in your workbook, and it should work fine.

HTH,
Bernie
MS Excel MVP

Function ProgSum(inRange As Range, _
SumTarget As Double) As Variant
Dim myCell As Range
ProgSum = 0
For Each myCell In inRange
ProgSum = ProgSum + 1
SumTarget = SumTarget - myCell.Value
If SumTarget <= 0 Then Exit Function
Next myCell
ProgSum = "Not Avail"
End Function

"Maarten" <Maarten@discussions.microsoft.com> wrote in message
news:0449EE21-DEDD-4E70-AF2C-79368C551A39@microsoft.com...
> It might take up to a few hundred steps
> The solution 'bj' posted works if the number of cells is limited (like in
> the example), but is very laborious if the column contains lots of cells
>
> "Bernie Deitrick" wrote:
>
> > Maarten,
> >
> > How many steps might it take? Always 3 or less? Or many hundreds?
> >
> > That will impact the possible solutions.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Maarten" <Maarten@discussions.microsoft.com> wrote in message
> > news:13658E38-E9DE-40DB-A010-A18097AC5A71@microsoft.com...
> > > Hello,
> > > I am looking for a method in Excel to count the number of steps one
should
> > > take through a column with numbers in order to reach a certain
threshold
> > > value. Example: if cells A1:A8 contain 1, 2, 1, 3, 6, 1, 1, 2
> > > How can I calculate in the next column (B1:B8) for each cell the
number of
> > > steps one should take through column A in order to reach that the sum
of
> > the
> > > next x steps is >= 4?
> > > In the previous example the result should be (B1:B8): 3 (1+2+1), 3
> > (2+1+3),
> > > 2 (1+3), 2 (3+6), 1 (6), 3 (1+1+2), #N/A, #N/A
> > >
> > > Can somebody help me?
> > > Many thanks,
> > > Maarten
> > >
> >
> >
> >

Great, it works!
Thanks a lot!

"Bernie Deitrick" wrote:

> Maarten,
>
> Then I would recommend using a User-Defined-Function, definition below, used
> like this, relative to your example:
>
> =ProgSum(A1:A$8,4)
>
> Note the $8 - you should anchor the lowest cell to reduce calc time.
>
> Copy the code into a codemodule in your workbook, and it should work fine.
>
> HTH,
> Bernie
> MS Excel MVP
>
> Function ProgSum(inRange As Range, _
> SumTarget As Double) As Variant
> Dim myCell As Range
> ProgSum = 0
> For Each myCell In inRange
> ProgSum = ProgSum + 1
> SumTarget = SumTarget - myCell.Value
> If SumTarget <= 0 Then Exit Function
> Next myCell
> ProgSum = "Not Avail"
> End Function
>
> "Maarten" <Maarten@discussions.microsoft.com> wrote in message
> news:0449EE21-DEDD-4E70-AF2C-79368C551A39@microsoft.com...
> > It might take up to a few hundred steps
> > The solution 'bj' posted works if the number of cells is limited (like in
> > the example), but is very laborious if the column contains lots of cells
> >
> > "Bernie Deitrick" wrote:
> >
> > > Maarten,
> > >
> > > How many steps might it take? Always 3 or less? Or many hundreds?
> > >
> > > That will impact the possible solutions.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Maarten" <Maarten@discussions.microsoft.com> wrote in message
> > > news:13658E38-E9DE-40DB-A010-A18097AC5A71@microsoft.com...
> > > > Hello,
> > > > I am looking for a method in Excel to count the number of steps one
> should
> > > > take through a column with numbers in order to reach a certain
> threshold
> > > > value. Example: if cells A1:A8 contain 1, 2, 1, 3, 6, 1, 1, 2
> > > > How can I calculate in the next column (B1:B8) for each cell the
> number of
> > > > steps one should take through column A in order to reach that the sum
> of
> > > the
> > > > next x steps is >= 4?
> > > > In the previous example the result should be (B1:B8): 3 (1+2+1), 3
> > > (2+1+3),
> > > > 2 (1+3), 2 (3+6), 1 (6), 3 (1+1+2), #N/A, #N/A
> > > >
> > > > Can somebody help me?
> > > > Many thanks,
> > > > Maarten
> > > >
> > >
> > >
> > >
>
>
>