Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Maarten
 
Posts: n/a
Default 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

  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #3   Report Post  
bj
 
Posts: n/a
Default

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

  #4   Report Post  
Maarten
 
Posts: n/a
Default

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




  #5   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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








  #6   Report Post  
Maarten
 
Posts: n/a
Default

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing cells in pivot tables Ted Excel Discussion (Misc queries) 1 April 5th 05 05:10 PM
Cumulative Summing Carpie Excel Discussion (Misc queries) 3 February 11th 05 05:35 PM
Summing Time JDT Excel Discussion (Misc queries) 8 February 8th 05 11:53 PM
Summing Sheets - SUMIF ? Steve W Excel Worksheet Functions 8 December 20th 04 07:49 AM
Summing Formula Steve W Excel Worksheet Functions 4 November 4th 04 01:06 PM


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"