Pulling Data with 2 constraints

[csliger931]

Hi,

I am trying to set up a calculation that calculates the flow RATE between dates where I actually have data. In the example table below, I have a total flow of 90 gallons on 05/05, and a total flow of 30 gallons on 05/02. Obviously, I don't want to have the formula set up to where its subtracting the row below it, because the row below it may be empty. So I need formula that will take the current flow for that day (i.e. 90 gallons) and then subtract the most previous date's flow (i.e, 30 gallons), and then divide that by the amount of days elapsed between those two entries (05/05 - 05/02 = 3). So, my final formula would result = (90-30)/(05/05-05/02) = 60/3 = 20 gal/day. Any ideas on how to do this? Keep in mind that the days between data collection may vary. I imagine that the winning formula is going to use the INDEX(2,1/) formula that seems to solve everything, but I have no idea how that would look (I'm still learning Excel basics). Anyways, I would greatly appreciate any help you Excel wizards could give me :D

Thanks!
Chris

DATE	FLOW (gallons)	RATE (gal/day)
05/05	90	=(B2-B5)/(A2-A5) ???
05/04
05/03
05/02	30

 

[C Moore]

Would it be helpful to summarize? Then you could subtract the row below it. Dynamic array function filter has you enter one formula and it will spill down. Next to the spill area the simple rate formula would go.

scratchbook excel testing.xlsm
	L	M	N	O	P	Q
13	Date	Flow		Date	Flow	Rate
14	5-May	90		5/5/2022	90	20
15	4-May			5/2/2022	30
16	3-May
17	2-May	30
Sheet21

Cell Formulas
Range	Formula
O14:P15	O14	=FILTER(L14:M17,NOT(ISBLANK(M14:M17)))
Q14	Q14	=(P14-P15)/(O14-O15)
Dynamic array formulas.