Progressive summing

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
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
 
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
 
Great, it works!
Thanks a lot!

Bernie Deitrick said:
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
 
Back
Top