Moving and pausing a reference cell along with an IF function

I'm trying to work out how a cell in a function should move down the column in line with the function, but then not advance once a condition has been triggered.

In this example, my function initially references A1. This cell will of course change to a2, a3 etc automatically as the function is copied down the column. Once the condition for the function is met, I'd like all subsequent rows to reference the original cell until the condition breaks, then jump down to the next instance of a match.

My function so far (column E) is

=IF(D1>C1,(((B1-A1)*100)*12))

This is fine for the first row which meets the criteria, but in this example, I'd like both B4 and B5 to reference A4 (B4-A4 & B5-A4), similarly E8, E9 & E10 should use A8 to subtract from B8-B10. I'm trying to calculate the figures in column E and am interested in the sum of B minus the cell in row A which first meets my condition.

	A	B	C	D	E
1	0.99800	0.99934	30	16
2	0.99885	0.99945	20	14
3	0.99567	0.99933	25	17
4	0.99456	0.99744	20	22	3.456
5	0.99377	0.99555	22	25	1.188
6	0.99467	0.99542	30	28
7	0.99433	0.99560	40	35
8	0.99465	0.99684	31	32	2.628
9	0.99345	0.99554	30	36	1.068
10	0.99445	0.99500	24	25
11	0.99677	0.99673	30	29

Would really appreciate any pointers for what direction I should be concentrating on to try and solve this.

Many thanks,

Gareth

Sorry my explanation wasn't clear, here is a sample workbook.

The formula and desired results are column E in my original example (calculated manually). Instead of calculating B-A row by row, I want to calculate B minus only the A value from the row when the condition was met. After the condition stops being met, I'd like things to repeat the next time it matches.

AliGW,

My manually calculated expected results are column E in the original example, column J in the excel file.

Thanks,

Gareth

My mistake, I've added column K with manually calculated expected results.

Thanks,

Gareth

Thanks for your reply. Sorry I seem to be doing a terrible job of explaining what I'm trying to achieve.

At its most basic, I want to subtract C from D, but when I copy the formula down, I want to subtract each row in D from one cell in column C. Normally this is no issue but since throughout my data this happens several times, each time with a different static cell in C, I'm stuck with how to complete the formula.

For 'currently being used, I want them to be ignored', I want only the green cells to be used. So J2 contains D2-C2, J3 contains D3-C2, C never changes until the next time my conditional formula triggers.

Gareth

Try this array function
=IF(I2>G2,(((D2-IFERROR(LOOKUP(2,1/($I$1:I1<=$G$1:G1),$C$2:C2),INDEX(C:C,MATCH(1,INDEX(--($I$2:I2>$G$2:G2),),)+1)))*100)*12)),

BMV,

That works perfectly! Thanks so much for taking the time to look at it.

Thanks to all for your help, I'd have never worked it out on my own!

Gareth