Excel 2003 Subract current value from value on most previous date

[rrrexcel]

Please help me find the best way, in Excel 2003, to calculate the difference between the values on the last two most recent occurrences of data.

Here is a sample of the data. For example: I am looking for a formula to find the difference between Ann's End Bal on 4-Oct, and Ann's End Bal on 1-Oct and give me $15. And I need that formula to also find the difference between Ann's End Bal on 9-Oct, and Ann's End Bal on 4-Oct and give me $1.

	A	B	C	D
1	Desc	Date	End Bal	Desired Result
2	Ann	1-Oct	$10.00
3	Paul	2-Oct	$20.00
4	Greg	3-Oct	$30.00
5	Ann	4-Oct	$25.00	$15.00
6	Paul	5-Oct	$36.00	$16.00
7	Greg	6-Oct	$47.00	$17.00
8	Paul	7-Oct	$38.00	$2.00
9	Greg	8-Oct	$50.00	$3.00
10	Ann	9-Oct	$26.00	$1.00

<tbody>
</tbody>

Any guidance you can provide is appreciated.

 

[Snakehips]

rrrexcel,

If you are ok to utilise a 'helper' column then maybe....

Helper column here is F but can be any column of your choosing and can of course be hidden.
Range of date currently max of 10000 rows - adjust if necessary.

Excel 2007
	A	B	C	D	E	F
1	Desc	Date	End Bal	Desired Result		Helper
2	Ann	01-Oct	£ 10.00			Ann1
3	Paul	02-Oct	£ 20.00			Paul1
4	Greg	03-Oct	£ 30.00			Greg1
5	Ann	04-Oct	£ 25.00	£ 15.00		Ann2
6	Paul	05-Oct	£ 36.00	£ 16.00		Paul2
7	Greg	06-Oct	£ 47.00	£ 17.00		Greg2
8	Paul	07-Oct	£ 38.00	£ 2.00		Paul3
9	Greg	08-Oct	£ 50.00	£ 3.00		Greg3
10	Ann	09-Oct	£ 26.00	£ 1.00		Ann3
11	Ann	10-Oct	£ 34.00	£ 8.00		Ann4
12						0
Sheet7

Cell Formulas
Range	Formula
D2	=IF(ISNA(C2-INDEX(C$1:C$10000,MATCH(A2&COUNTIF(A$2:A2,A2)-1,F$1:F$10000,0),1)),"",C2-INDEX(C$1:C$10000,MATCH(A2&COUNTIF(A$2:A2,A2)-1,F$1:F$10000,0),1))
F2	=A2&COUNTIF(A$2:A2,A2)

Hope that helps.

 

[rrrexcel]

Thank you, Snakehips. Your solution appears to work perfectly (in Excel 2010). I will study your formulas and try to apply it to my old spreadsheet (Excel 2003). I am grateful for your response and clear speech.

 

[rrrexcel]

I am writing to say, "Thank you", again to Snakehips. The formulas worked perfectly on my old computer, and I learned a lot about using the countif with the index match. Great Solution.

 

[Aladin Akyurek]

Without an additional range,,,

D2, copied down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),C2-LOOKUP(9.99999999999999E+307,1/($A$1:A1=A2),$C$1:C1),"")