Need a formula to subtract the last value from penultimate value

[pedro-egoli]

In my workbook sheet 2 in column D has balances that are added to periodically.
In sheet 1 I need a formula in cells P25:P100 that will give me the sum of last value minus the penultimate value.
EG Sheet 2 D9 is last cell and i need D9-D8 to show up initially in sheet 1 P25 and continue until D10 becomes the last value in sheet 2 when in say S25 would show balance of sheet 2 D10-D9
Thanks

 

[QuestionBaker]

So, you want Sheet 1 P25 to give Sheet 2 (D9-D8) but when data is entered in at Sheet 2 (D10) you want the answer to (D10-D9)?
Where do you want this new result?
Sheet 1 (P25) or
Sheet 1 (P26)
Why have you mentioned P25:P100? W

 

[pedro-egoli]

Thanks QuestionBaker,
Best explained with an extract from sheet 1 and 2
Sheet 1 looks like this (columns A:D)

8/07/2020	$703,186	$703,186	$399,407
9/07/2020	$694,358	$694,358	$391,908
11/07/2020	$707,185	$707,185	$401,942

and Sheet 2 is the desired result (Row P25:R25)

9/10/2020	10/07/2020	11/07/2020
-$7,499	-$7,499	$10,034

I want to see in sheet 2 the daily change in movement in column D shown in row 25 starting at P25 and dragged across row

 

[Amit Tandon]

26Aug19.xlsx
	A	B	C	D	E	F	G	H	I	J
1						7/9/2020	7/10/2020	7/11/2020	7/12/2020	7/13/2020
2	7/8/2020	$703,186	$703,186	$399,407		($7,499)	($7,499)	($7,499)	$10,034
3	7/9/2020	$694,358	$694,358	$391,908
4	7/12/2020	$707,185	$707,185	$401,942
Sheet12

Cell Formulas
Range	Formula
G1:J1	G1	=F1+1
F2:J2	F2	=IF(F1>MAX($A2:$A10),"",INDEX($D2:$D10,MATCH(F1,$A2:$A10))-INDEX($D2:$D10,MAX(MATCH(F1,$A2:$A10)-1,1)))

 

[QuestionBaker]

how are you calculating for 10/07/2020?
I do not see any entry over there, so is it has to repeat 9/7/2020 - 8/7/2020?
and the 10 in 9/10/2020 in sheet 2 of your example, is it a typo or is it intended?

 

[pedro-egoli]

Thanks Amit Tandon,
Your solution looks very promising.
Will need to convert to my 2 worksheets
Question Baker,
Because there was no entry on 10/7/20 I just want the previous figure from 9/7/20to appear
Good pick up with the 9/10/20 should have been 9/7/20

 

[pedro-egoli]

Amit Tandon ,
Just to let you know i got our solution to work using my 2 sheets.
Thank you

 

[Amit Tandon]

Amit Tandon ,
Just to let you know i got our solution to work using my 2 sheets.
Thank you

Great to know your query is resolved.

Best Regards