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" wrote in message
...
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+A 2+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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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