Progressive summing

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

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.

 

[Guest]

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]

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

 

[Guest]

Great, it works!
Thanks a lot!

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