Sum a Column until desired amount then reset

[dfelmey]

Need to the sum of Column E totaled until it is >= to a number (ie $5.00). Once that happens I need the difference over the (ie $5.00) to be calculated also and added to once it reaches a set number (ie $5.00). When complete I need the sum of column reset to $0 to start adding again to start the process over until it reaches set figure (ie$5.00).

Thank you, I am sure I will probably have to clarify, please let me know what you need.

 

[Giordano Bruno]

Suppose you have three columns to do the calculation. If the first value is in E2, F2 has =SUM($E$2:E2). G2 has =INT(F2/5). H2 has =IF(G2>G1,5,0). Copy F2 to H2 to the end of the column. The $5s that occur in column H will appear every time multiples of 5 are exceeded, but the remainder has never been deducted and so does not need to be added back. It will fail if any number in column E exceeds $5.

 

[dfelmey]

Thank you. That works, but I need a different result. Let me try to explain again.

I need help with a formula, that sums col E from top down up until col E reaches $5 (which would eventually be a based on a floating input figure), then reset and sum again from that point until $5 is reached. Based on this example col A (contracts) x col C (price) = Total Earned. When col E is >= $5 a new contract (col B) is earned. Subsequently any earned over $5 from col F is remaining leftover from the cell above is 'banked' until that total reaches the $5 point when an additional contract be earned (col B). So, to recap, need the formula to add Col H until $5.00 is reached, add an additional contract (colB) and any remaining over $5.00 is where the Addition (+) starts from until $5.00 is reached again and the math stops and resets. When multiples of 5 are reached, it adds that may to the new Contracts (colB). it gets further down the rows, there will be manys 10's of new contacts added. Please remember that the $5.00 is going to be a ever changing variable, so I will have a place holder cell to enter as it changes over time. --- Going to have about 2,000+ Rows in spreadsheet. Thank you very much, hope I described as for you to understand. Thanks Again!
[TABLE="width: 889"]
<colgroup><col><col><col span="2"><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD] [TABLE="width: 954"]
<tbody>[TR]
[TD]
[/TD]
[TD] A
current contracts
[/TD]
[TD] B
new contracts
[/TD]
[TD] C
price
[/TD]
[TD] D
total earned
[/TD]
[TD]
E
total
earned
minus $5
[/TD]
[TD]
[/TD]
[TD]
F
SUM Total of [Column E]
WHEN >=$5 Threshold is hit
[/TD]
[TD] G
[/TD]
[TD] H
Total Leftover after buying new contract
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]0
[/TD]
[TD]$0.5
[/TD]
[TD]$5.00
[/TD]
[TD]$0.00
[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD]$5.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]($5.00)
[/TD]
[TD]$0.00(H2)
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]11
[/TD]
[TD]1
[/TD]
[TD]$0.5
[/TD]
[TD]$5.50
[/TD]
[TD]$0.50
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]12
[/TD]
[TD]1
[/TD]
[TD]$0.5
[/TD]
[TD]$6.00
[/TD]
[TD]$1.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]13
[/TD]
[TD]1
[/TD]
[TD]$0.5
[/TD]
[TD]$6.50
[/TD]
[TD]$1.50
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]14
[/TD]
[TD]2
[/TD]
[TD]$0.5
[/TD]
[TD]$7.00
[/TD]
[TD]$2.00
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]16
[/TD]
[TD]1
[/TD]
[TD]$0.5
[/TD]
[TD]$8.00
[/TD]
[TD]$3.00
[/TD]
[TD][TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]$6.50
[/TD]
[TD]($5.00)
[/TD]
[TD]$1.50 (H7)
[/TD]
[TD]F7 + H2 = $6.5 + 0 = $5.00 = $1.50 Left Over in ‘bank’
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]17
[/TD]
[TD]2
[/TD]
[TD]$0.5
[/TD]
[TD]$8.50
[/TD]
[TD]$3.50
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]19
[/TD]
[TD]2
[/TD]
[TD]$0.5
[/TD]
[TD]$9.50
[/TD]
[TD]$4.50
[/TD]
[TD]

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]$4.50
[/TD]
[TD]($5.00)
[/TD]
[TD]$1.00 (H8)
[/TD]
[TD]F8 + H7 = $4.50 + $1.50 = $6.00 - $5.00 = $1.00 Left Over in ‘bank’
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]21
[/TD]
[TD]2
[/TD]
[TD]$0.5
[/TD]
[TD]$10.50
[/TD]
[TD]$5.50
[/TD]
[TD]

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]$5.50
[/TD]
[TD]($5.00)
[/TD]
[TD]$0.5
[/TD]
[TD]F9 + H8 = $6.50 - $5.00 = $1.50 Left Over in ‘bank’
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dfelmey]

Sorry, I don't know how to post a screenshot of my excel in here so the formatting is correct. Used Word and did a paste, it looked fine when I submitted.

 

[Giordano Bruno]

On line 9, you have $5.50 - ($5.00) = $0.50, but the line above states that there is $1.00 left in 'bank'. Should this be added? If so, that would then agree with your comment $1.50 Left Over in 'bank'. Is this an error or is there another explanation.

 

[dfelmey]

On line 9, you have $5.50 - ($5.00) = $0.50, but the line above states that there is $1.00 left in 'bank'. Should this be added? If so, that would then agree with your comment $1.50 Left Over in 'bank'. Is this an error or is there another explanation.

Giordano, it was just a typo error. The math should be the math, no other special calculations.

Thank you

 

[Giordano Bruno]

I have reproduced your sheet and have the values 10 to 21 in A5:A13. New Contracts are in B, Price is in C and Total Earned in D. I have the variable $5.00 entered in cell E3. Row 4 is blank. The formulas are as follows:
D5 =C5*A5.
E5 =D5-$E$3.
F5 =IF(SUM($E$4:E5)-COUNT($F$4:F4)*$E$3>=5,SUM($E$4:E5)-SUM($F$4:F4),"").
G5 =IF(SUM(F5)>0,-$E$3,"").
H5 =IF(SUM(F5)>0,SUM($F$4:G5),"").
Copy them all down to row 13.
The produce the correct figures for the data you have supplied.

 

[dfelmey]

I have reproduced your sheet and have the values 10 to 21 in A5:A13. New Contracts are in B, Price is in C and Total Earned in D. I have the variable $5.00 entered in cell E3. Row 4 is blank. The formulas are as follows:
D5 =C5*A5.
E5 =D5-$E$3.
F5 =IF(SUM($E$4:E5)-COUNT($F$4:F4)*$E$3>=5,SUM($E$4:E5)-SUM($F$4:F4),"").
G5 =IF(SUM(F5)>0,-$E$3,"").
H5 =IF(SUM(F5)>0,SUM($F$4:G5),"").
Copy them all down to row 13.
The produce the correct figures for the data you have supplied.

Thank you again. It is almost what I was looking for. If we can again, let me explain using this example.

Lets say we have tokens: and every time we get 5 more (since the new token costs $5(which is a variable)) we can buy another token, we then add that new token to the 10 (current) (now 11 (10+1 the new token)) and multiply out again. Each time calculating the NEW amount of tokens per the formulas:

10 Tokens x $.5 = $5 ( we can now buy 1 new token) - 0 left over
11 Tokens x .5 x $5.5 (can buy 1 more token) 0.5 left over
12 Tokens x .5 x $6.0 (can buy 1 more token) 1.0 left over PLUS the 0.5 from above = 1.5 in the bank
13 Tokens x .5 x $6.5 (can buy 1 more token) 1.5 left over PLUS the 1.5 from above = 3 in the bank
14 Tokens x .5 x $7.0 (can buy 1 more token) 2.0 left over PLUS the 3.0 from above = 5 in the bank (now can buy an additional token @ the $5 (variable) = 0.0 in the bank
15 (skipped 15 since we bought 2 tokens last round) (went from 14 to 16)
16 Tokens x .5 x $8.0 (can buy 1 more token) 3.0 left over PLUS the 0.0 from above = 3 in the bank
17 Tokens x .5 x $8.5 (can buy 1 more token) 3.5 left over PLUS the 3 from above = 6 in the bank (now can buy 1 additional token) bank (6-5) = 1 in the bank
19 Tokens x .5 x $9.5 (can buy 1 more token) 4.5 left over PLUS the 1 from above = 5.5 in the bank (now can buy 1 additional token) bank (5.5-5) = 0.5 in the bank
21 Tokens x .5 x $10.5 (can buy 2 more tokens) (5+5=10-0.5)=0.5 left over PLUS the 0.5 from above = 1.0 in the bank
23 Tokens x .5 x $11.5 (can buy 2 more tokens) 1.5 left over PLUS the 1 from above = 2.5 in the bank
25 Tokens x .5 x $12.5 (can buy 2 more tokens) 2.5 left over PLUS the 2.5 from above = 5.0 in the bank(now can buy 1 additional token) bank (5.0-5) = 0.0 in the bank
28 Tokens x .5 x $14 (can buy 2 more tokens) 4.00 left over PLUS the 0 from above = 4.0 in the bank
30 Tokens x .5 x $15 (can buy 3 more tokens) 0.0 left over PLUS the 1 from above = 1.0 in the bank
33 Tokens x .5 x $16.5 (can buy 3 more tokens) 1.5 left over PLUS the 1 from above = 2.5 in the bank
34 Tokens x .5 x $17.0 (can buy 3 more tokens) 2.5 left over PLUS the 2.5 from above = 5.0 in the bank(now can buy 1 additional token) bank (5.0-5) = 0.0 in the bank
37 Tokens x .5 x $18.5 (can buy 3 more tokens) 3.5 left over PLUS the 0.0 from above = 3.0 in the bank
40 Tokens x .5 x $20.0 (can buy 4more tokens) 3.5 left over PLUS the 0.0 from above = 3.5 in the bank
44 etc..............

 

[Giordano Bruno]

I think the typos are making this more difficult than it should be. Ref 17. Should be "3.5 PLUS the 3 from above = 6.5 - 5 = 1.5 in the bank."
This difference carries forward so that all of the following lines is wrong, but additionally:
Ref 30. Should be "4.50 from above = 4.50 in the bank."
Ref 40. Should be "0 left over PLUS 4.50 from above = 4.50 in the bank."

I have had to make assumptions about the correct values, but assuming the above observations are valid, please modify the formulas as follows:
A5 10, A6 =A5+1+INT(SUM($F5,$H4)/$E$3)
B5 =1+INT(SUM($F5,$H4)/$E$3) [Note that this returns 1, not 0, but this is the only cell in column C which differs]
C5 0.5 [a constant value]
D5 =C5*A5
E5 =D5-$E$3
F5 =IF(SUM($E$4:E5)-COUNT($F$4:F4)*$E$3>=5,SUM($E$4:E5)-SUM($F$4:F4),"")
G5 =IF(SUM(F5)>0,-$E$3,"")
H5 =IF(SUM(F5)>0,SUM($F$4:G5),"")
Please let me know if that works for you and whether I have correctly interpreted your needs.

 

[dfelmey]

Thank you very much once again for the assistance. Apologize that this is taking this many attempts, I truly appreciate your help. Sorry for the typos in the math, there are no special circumstances, the math should be the math. The formulas seem to work until they start to fail and not "reset" Col H "the bank". So from Line15 on, it just keeps ADDING to H and not bringing it back to under the variable 5(E3) until it is reached again.

I copied down to Line 41. Here is my result at Line 41:
A41: 22451
B41: 8303
C41: 0.5
D41: 11,225.50
E41: 11,220.50
F41: 11,220.50
G41: -5
H41: 41506 (result is F41-G41+H40 (30,290.50))


It looks to fail at Line 15:
A15: 25
B15: 3
C15: 0.5
D15:12.50
E15: 7.50
F15: 7.50
G15: -5
H15: 5.5 (It should be 0.5 --- 7.5(E15 OR F15(not sure which cell you are doing the calc from, looks like F15))-5(G15)=2.5 + 3(from bank of cell H14) = 5.5 THEN should minus the 5 (or multiples of 5 (variable E3) that to give you 0.5 in the bank.
*** So H15 Should be 7.5-5=2.5+3.0=5.5-5=0.5