subtraction formula that skips rows with no data

[csliger931]

Hi,

I am needing some help with a formula. I have a spreadsheet containing rows of information representing when data is collected. On certain days, there is no data collected, so the rows for those days are blank. I need a formula that will subtract today's value from the most previous value regardless of when that most previous value occurred. What I do NOT want is a formula that simply references the previous row, since that row may not have any data. See the example table below for my situation. I need a formula that can go in the top-right of the example table that will recognize that there is no data on 05/31 or 05/30, so instead it subtracts from the data on 05/29.
Does anyone know how to do this? I would greatly appreciate any help I can get from you Excel wizards.
Thanks!
Chris
NOTE: MY VALUES DON'T ALWAYS GO IN ASCENDING ORDER, SO I CAN'T SIMPLY HAVE A FORMULA THAT SCANS FOR THE LARGEST VALUE AND SUBTRACTS FROM THAT. IT NEEDS TO RECOGNIZE EMPTY ROWS AND SKIP THEM UNTIL IT GETS TO A DAY WHERE THERE WAS DATA.

06/01/22	59	=(59-47)/(06/01-05/29) = (12)/(3) = 4
05/31/22
05/30/22
05/29/22	47	=(47-41)/(05/29-05/27) = (6)/(2) = 3
05/28/22
05/27/22	41

 

[Eric W]

With Excel 365 and a gap of no more than 10 (which can easily be raised):

Book1
	A	B	C
1	Date	Value	Formula
2	6/1/2022	59	4
3	5/31/2022
4	5/30/2022
5	5/29/2022	47	3
6	5/28/2022
7	5/27/2022	41	#CALC!
Sheet12

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2<>"",LET(f,FILTER(A3:B10,B3:B10<>""),(B2-INDEX(f,1,2))/(A2-INDEX(f,1,1))),"")