URGENT: Suming numbers until a certain point

[hardgrafting]

Hi,

I am trying to create a formula, that when the trigger in column C is met (1 being trigger met), takes the cash going forward in column A to column D, until the point that the sum of the cash in column D equals % of the cumulative cash at the trigger point.

For example, the trigger hits at C7, where the cumulative cash is 72,000. For the purposes of illustration, I would like to put in column D, the cash flows from column A, from A7 onwards, that equal to 20% of the cumulative cash flows, which is 14,000. So in cell D8 = 3,000 / D9 = 4,000 / D10 = 5,000 / D11 = 6,000 ( I would like to split this since the sum of the cash flows in column D, is greater than 14,000).

Cell D12 onwards, would be zero, thank you




[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]c[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cash[/TD]
[TD]Cumulative[/TD]
[TD]Trigger[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5,000[/TD]
[TD]5,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]50,000[/TD]
[TD]50,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8,000[/TD]
[TD]58,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10,000[/TD]
[TD]68,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2,000[/TD]
[TD]70,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2,000[/TD]
[TD]72,000[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3,000[/TD]
[TD]75,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4,000[/TD]
[TD]79,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5,000[/TD]
[TD]84,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]6,000[/TD]
[TD]90,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5,000[/TD]
[TD]95,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]5,000[/TD]
[TD]100,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]5,000[/TD]
[TD]105,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5,000[/TD]
[TD]110,000[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

I have read this 3 times and do not understand what you want. Why is D12 onwards zero ? Where does 14000 come from ?

 

[VBA Geek]

i think you may looking for this


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">MEDIAN(<font color="Red">0,OFFSET(<font color="Green">B2,MATCH(<font color="Purple">1,C$1:C1,0</font>)-ROW(<font color="Purple"></font>),0</font>)*$G$1,MIN(<font color="Green">A2,OFFSET(<font color="Purple">B2,MATCH(<font color="Teal">1,C$1:C1,0</font>)-ROW(<font color="Teal"></font>),0</font>)*$G$1-IF(<font color="Purple">C1=1,0,SUM(<font color="Teal">OFFSET(<font color="#FF00FF">A2,MATCH(<font color="Navy">1,C$1:C1,0</font>)-ROW(<font color="Navy"></font>)+1,0,-1+ABS(<font color="Navy">MATCH(<font color="Blue">1,C$1:C1,0</font>)-ROW(<font color="Blue"></font>)</font>)</font>)</font>)</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2010
	A	B	C	D	E	F	G
1	Cash	Cumulative	Trigger			percentage	27%
2	5,000	5,000	0
3	50,000	50,000	0
4	8,000	58,000	0
5	10,000	68,000	0
6	2,000	70,000	0
7	2,000	72,000	0
8	3,000	75,000	0
9	4,000	79,000	1
10	5,000	84,000	0	5000
11	6,000	90,000	0	6000
12	5,000	95,000	0	5000
13	5,000	100,000	0	5000
14	5,000	105,000	0	330
15	5,000	110,000	0	0
Sheet16

 

[MartyS]

1. Not sure if it's relevant, but the data in column B [Cumulative] is incorrect from B3 onwards.

2. Is this the entire data set, or will there be further triggers?

If there are further triggers, then sadly the awesome formula you gave no longer works

Example:



[TABLE="width: 1271"]
<tbody>[TR]
[TD="class: xl99, width: 33, align: center"]A[/TD]
[TD="class: xl99, width: 84, align: center"]B[/TD]
[TD="class: xl99, width: 98, align: center"]C[/TD]
[TD="class: xl99, width: 84, align: center"]D[/TD]
[TD="class: xl99, width: 107, align: center"]E[/TD]
[TD="class: xl99, width: 107, align: center"]F[/TD]
[TD="class: xl99, width: 142, align: center"]G[/TD]
[TD="class: xl99, width: 89, align: center"]H[/TD]
[TD="class: xl99, width: 121, align: center"]I[/TD]
[TD="class: xl99, width: 95, align: center"]J[/TD]
[TD="class: xl99, width: 90, align: center"]K[/TD]
[TD="class: xl99, width: 90, align: center"]L[/TD]
[TD="class: xl99, width: 34, align: center"]M[/TD]
[TD="class: xl99, width: 97, align: center"]N[/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]1[/TD]
[TD="class: xl100, width: 84, align: right"]Cash[/TD]
[TD="class: xl98, width: 98, align: right"]Cumulative[/TD]
[TD="class: xl98, width: 84, align: right"]Trigger[/TD]
[TD="class: xl98, width: 107, align: right"]20% of Cumulative at Trigger[/TD]
[TD="class: xl98, width: 107, align: right"]Count of Triggers[/TD]
[TD="class: xl98, width: 142, align: right"]20% Cumulative
- Cash[/TD]
[TD="class: xl98, width: 89, align: right"]Data Output calc[/TD]
[TD="class: xl98, width: 121, align: right"]Cash to use in calc[/TD]
[TD="class: xl98, width: 95, align: right"]Output #1[/TD]
[TD="class: xl98, width: 90, align: right"]Output #2[/TD]
[TD="class: xl105, width: 90, align: right"]Solution Provided[/TD]
[TD="class: xl108, align: right"][/TD]
[TD="class: xl100, width: 97, align: right"]Percentage[/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]2[/TD]
[TD="class: xl101, width: 84, align: right"]0[/TD]
[TD="class: xl67, width: 98, align: right"]0[/TD]
[TD="class: xl67, width: 84, align: right"]0[/TD]
[TD="class: xl67, width: 107, align: right"]0[/TD]
[TD="class: xl67, width: 107, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl106, align: right"]0[/TD]
[TD="class: xl108, align: right"][/TD]
[TD="class: xl109, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]3[/TD]
[TD="class: xl102, width: 84, align: right"]5,000[/TD]
[TD="class: xl73, width: 98, align: right"]5,000[/TD]
[TD="class: xl74, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl75, width: 107, align: right"]0[/TD]
[TD="class: xl89, align: right"]-5,000[/TD]
[TD="class: xl90, align: right"]0[/TD]
[TD="class: xl89"][/TD]
[TD="class: xl91"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]4[/TD]
[TD="class: xl103, width: 84, align: right"]50,000[/TD]
[TD="class: xl69, width: 98, align: right"]55,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl93, align: right"]-55,000[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]5[/TD]
[TD="class: xl103, width: 84, align: right"]8,000[/TD]
[TD="class: xl69, width: 98, align: right"]63,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl93, align: right"]-63,000[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]6[/TD]
[TD="class: xl103, width: 84, align: right"]10,000[/TD]
[TD="class: xl69, width: 98, align: right"]73,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl93, align: right"]-73,000[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]7[/TD]
[TD="class: xl103, width: 84, align: right"]2,000[/TD]
[TD="class: xl69, width: 98, align: right"]75,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl93, align: right"]-75,000[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]8[/TD]
[TD="class: xl103, width: 84, align: right"]2,000[/TD]
[TD="class: xl69, width: 98, align: right"]77,000[/TD]
[TD="class: xl70, width: 84, align: right"]1[/TD]
[TD="class: xl71, width: 107, align: right"]15,400[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]15,400[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107"][/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]9[/TD]
[TD="class: xl103, width: 84, align: right"]3,000[/TD]
[TD="class: xl69, width: 98, align: right"]80,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]12,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]3,000[/TD]
[TD="class: xl92, align: right"]3,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]3,000[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]10[/TD]
[TD="class: xl103, width: 84, align: right"]4,000[/TD]
[TD="class: xl69, width: 98, align: right"]84,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]8,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]4,000[/TD]
[TD="class: xl92, align: right"]4,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]4,000[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]11[/TD]
[TD="class: xl103, width: 84, align: right"]5,000[/TD]
[TD="class: xl69, width: 98, align: right"]89,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]3,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]5,000[/TD]
[TD="class: xl92, align: right"]5,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]5,000[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]12[/TD]
[TD="class: xl103, width: 84, align: right"]6,000[/TD]
[TD="class: xl69, width: 98, align: right"]95,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]-2,600[/TD]
[TD="class: xl94, align: right"]2[/TD]
[TD="class: xl93, align: right"]6,000[/TD]
[TD="class: xl92, align: right"]3,400[/TD]
[TD="class: xl92, align: right"]2,600[/TD]
[TD="class: xl107, align: right"]3,400[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]13[/TD]
[TD="class: xl103, width: 84, align: right"]5,000[/TD]
[TD="class: xl69, width: 98, align: right"]100,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]-7,600[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]14[/TD]
[TD="class: xl103, width: 84, align: right"]5,000[/TD]
[TD="class: xl69, width: 98, align: right"]105,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]-12,600[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]15[/TD]
[TD="class: xl103, width: 84, align: right"]5,000[/TD]
[TD="class: xl69, width: 98, align: right"]110,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]-17,600[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]16[/TD]
[TD="class: xl103, width: 84, align: right"]5,000[/TD]
[TD="class: xl69, width: 98, align: right"]115,000[/TD]
[TD="class: xl70, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]1[/TD]
[TD="class: xl93, align: right"]-22,600[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]17[/TD]
[TD="class: xl104, width: 84, align: right"]4,000[/TD]
[TD="class: xl96, width: 98, align: right"]119,000[/TD]
[TD="class: xl95, align: right"]1[/TD]
[TD="class: xl71, width: 107, align: right"]23,800[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]23,800[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]18[/TD]
[TD="class: xl104, width: 84, align: right"]2,000[/TD]
[TD="class: xl96, width: 98, align: right"]121,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]21,800[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]2,000[/TD]
[TD="class: xl92, align: right"]2,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]2,000[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]19[/TD]
[TD="class: xl104, width: 84, align: right"]8,000[/TD]
[TD="class: xl96, width: 98, align: right"]129,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]13,800[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]8,000[/TD]
[TD="class: xl92, align: right"]8,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]20[/TD]
[TD="class: xl104, width: 84, align: right"]4,000[/TD]
[TD="class: xl96, width: 98, align: right"]133,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]9,800[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]4,000[/TD]
[TD="class: xl92, align: right"]4,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]21[/TD]
[TD="class: xl104, width: 84, align: right"]2,000[/TD]
[TD="class: xl96, width: 98, align: right"]135,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]7,800[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]2,000[/TD]
[TD="class: xl92, align: right"]2,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]22[/TD]
[TD="class: xl104, width: 84, align: right"]4,000[/TD]
[TD="class: xl96, width: 98, align: right"]139,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]3,800[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]4,000[/TD]
[TD="class: xl92, align: right"]4,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]23[/TD]
[TD="class: xl104, width: 84, align: right"]3,000[/TD]
[TD="class: xl96, width: 98, align: right"]142,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]800[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]3,000[/TD]
[TD="class: xl92, align: right"]3,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]24[/TD]
[TD="class: xl104, width: 84, align: right"]8,000[/TD]
[TD="class: xl96, width: 98, align: right"]150,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]-7,200[/TD]
[TD="class: xl94, align: right"]2[/TD]
[TD="class: xl93, align: right"]8,000[/TD]
[TD="class: xl92, align: right"]800[/TD]
[TD="class: xl92, align: right"]7,200[/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]25[/TD]
[TD="class: xl104, width: 84, align: right"]6,000[/TD]
[TD="class: xl96, width: 98, align: right"]156,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]-13,200[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]26[/TD]
[TD="class: xl104, width: 84, align: right"]5,000[/TD]
[TD="class: xl96, width: 98, align: right"]161,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]2[/TD]
[TD="class: xl93, align: right"]-18,200[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]27[/TD]
[TD="class: xl104, width: 84, align: right"]6,000[/TD]
[TD="class: xl96, width: 98, align: right"]167,000[/TD]
[TD="class: xl95, align: right"]1[/TD]
[TD="class: xl71, width: 107, align: right"]33,400[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]33,400[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]28[/TD]
[TD="class: xl104, width: 84, align: right"]4,000[/TD]
[TD="class: xl96, width: 98, align: right"]171,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]29,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]4,000[/TD]
[TD="class: xl92, align: right"]4,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]4,000[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]29[/TD]
[TD="class: xl104, width: 84, align: right"]7,000[/TD]
[TD="class: xl96, width: 98, align: right"]178,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]22,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]7,000[/TD]
[TD="class: xl92, align: right"]7,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]30[/TD]
[TD="class: xl104, width: 84, align: right"]8,000[/TD]
[TD="class: xl96, width: 98, align: right"]186,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]14,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]8,000[/TD]
[TD="class: xl92, align: right"]8,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]31[/TD]
[TD="class: xl104, width: 84, align: right"]7,000[/TD]
[TD="class: xl96, width: 98, align: right"]193,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]7,400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]7,000[/TD]
[TD="class: xl92, align: right"]7,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]32[/TD]
[TD="class: xl104, width: 84, align: right"]7,000[/TD]
[TD="class: xl96, width: 98, align: right"]200,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]400[/TD]
[TD="class: xl94, align: right"]1[/TD]
[TD="class: xl93, align: right"]7,000[/TD]
[TD="class: xl92, align: right"]7,000[/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]33[/TD]
[TD="class: xl104, width: 84, align: right"]6,000[/TD]
[TD="class: xl96, width: 98, align: right"]206,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]-5,600[/TD]
[TD="class: xl94, align: right"]2[/TD]
[TD="class: xl93, align: right"]6,000[/TD]
[TD="class: xl92, align: right"]400[/TD]
[TD="class: xl92, align: right"]5,600[/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
[TR]
[TD="class: xl99, align: center"]34[/TD]
[TD="class: xl104, width: 84, align: right"]7,000[/TD]
[TD="class: xl96, width: 98, align: right"]213,000[/TD]
[TD="class: xl88, width: 84, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]0[/TD]
[TD="class: xl71, width: 107, align: right"]3[/TD]
[TD="class: xl93, align: right"]-12,600[/TD]
[TD="class: xl94, align: right"]0[/TD]
[TD="class: xl93"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl92"][/TD]
[TD="class: xl107, align: right"]0[/TD]
[TD="class: xl72"][/TD]
[TD="class: xl72"][/TD]
[/TR]
</tbody>[/TABLE]

 

[hardgrafting]

VBA Geek, many thanks for providing your solution it is really helpful.

I dont think I explain myself very well, as such, what I a looking to achieve, is that when the trigger is met (there is only 1 trigger). At trigger 1, the cumulative cash flows are 79,000, I want to then take all the ongoing cash flows post the trigger point into column D, until the sum a determined % of the cumulative cash flows at the trigger point. So for example, assume that I want 20% of the future cash flows, until they reach 20% of the trigger (20%*79,000= 15,800).

I want to therefore take all of A10 to D10 (5,000), all of A11 to D11 (6,000) and only 4,800 of A12 to D12.

Hope this makes sense and apologies if I am not explaining myself well

 

[VBA Geek]

Thats what my formula would return:

Excel 2013/2016
	A	B	C	D	E	F	G
1	Cash	Cumulative	Trigger			percentage	20%
2	5,000	5,000	0
3	50,000	50,000	0
4	8,000	58,000	0
5	10,000	68,000	0
6	2,000	70,000	0
7	2,000	72,000	0
8	3,000	75,000	0
9	4,000	79,000	1
10	5,000	84,000	0	5000
11	6,000	90,000	0	6000
12	5,000	95,000	0	4800
13	5,000	100,000	0	0
14	5,000	105,000	0	0
15	5,000	110,000	0	0
Sheet1