Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing cells in pivot tables | Excel Discussion (Misc queries) | |||
Cumulative Summing | Excel Discussion (Misc queries) | |||
Summing Time | Excel Discussion (Misc queries) | |||
Summing Sheets - SUMIF ? | Excel Worksheet Functions | |||
Summing Formula | Excel Worksheet Functions |