Rate/Day over X Most Recent Entries

[Bossi]

My table's inputs include the date, the person for whom the data is for, and then various data values. Each of these values is cumulative & keeps climbing continuously upward. How can I add in a column that identifies the daily rate of change between the X most recent entries?

In the example above I use X = 3, so it's looking at the three most recent entries. The "Rate/Day over X Days" column is what I hope for the answer to be (assuming I didn't botch the manually-entered formulae for my example)

At its core I'm just trying to take "(GivenValue - ThirdRecentValue) / (GivenDate - ThirdRecentDate)", where Given refers to the date for that current row.

Of course the Given Date/Value are easy. But how can I find the Xth most recent date/value, where I'm finding the Xth entry before the given date?

I feel like there's an easy way to do this but I'm not yet figuring it out.

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F
1
2				X =	3
3
4		Date	Name	Input	Rate/Day over X Entries
5		2024.10.01	Bossi	1	N/A
6		2024.10.01	Joe	1	N/A
7		2024.10.02	Bossi	3	N/A
8		2024.10.03	Bossi	6	2.5
9		2024.10.04	Bossi	10	3.5
10		2024.10.08	Bossi	19	2.6
11		2024.10.10	Joe	20	N/A
12		2024.10.15	Bossi	40	2.7
13		2024.10.16	Bossi	45	3.3
14		2024.10.17	Joe	44	2.7
15		2024.10.20	Bossi	65	5.0
16		2024.10.21	Bossi	66	4.2
17		2024.10.22	Bossi	66	0.5
18		2024.10.22	Joe	50	2.5
19		2024.10.23	Bossi	68	1.0
20		2024.10.23	Bossi	78	12.0
21		2024.10.23	Joe	65	3.5
22
Sheet3

Cell Formulas
Range	Formula
E5:E21	E5	=LET(x,$E$2,f,FILTER(B$5:D5,C$5:C5=C5),r,ROWS(f),IF(r<x,"N/A",(INDEX(f,r,3)-INDEX(f,r-x+1,3))/(INDEX(f,r,1)-INDEX(f,r-x+1,1))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E21	Other Type	Color scale		NO

 

[Peter_SSs]

Playing around with this I came up with a very marginally shorter version of Eric's formula in E5, copied down or a longer version in F5 but does not need to be copied down.

24 10 25.xlsm
	A	B	C	D	E	F
1
2				X =	3
3
4		Date	Name	Input	Rate/Day over X Entries	Rate/Day over X Entries
5		2024.10.01	Bossi	1	N/A	N/A
6		2024.10.01	Joe	1	N/A	N/A
7		2024.10.02	Bossi	3	N/A	N/A
8		2024.10.03	Bossi	6	2.5	2.5
9		2024.10.04	Bossi	10	3.5	3.5
10		2024.10.08	Bossi	19	2.6	2.6
11		2024.10.10	Joe	20	N/A	N/A
12		2024.10.15	Bossi	40	2.7	2.7
13		2024.10.16	Bossi	45	3.3	3.3
14		2024.10.17	Joe	44	2.7	2.7
15		2024.10.20	Bossi	65	5.0	5.0
16		2024.10.21	Bossi	66	4.2	4.2
17		2024.10.22	Bossi	66	0.5	0.5
18		2024.10.22	Joe	50	2.5	2.5
19		2024.10.23	Bossi	68	1.0	1.0
20		2024.10.23	Bossi	78	12.0	12.0
21		2024.10.23	Joe	65	3.5	3.5
Rate per day

Cell Formulas
Range	Formula
F5:F21	F5	=BYROW(B5:D21,LAMBDA(r,LET(x,E2,c,INDEX(r,2),f,TAKE(FILTER(B$5:INDEX(r,3),C$5:c=c),-x),IFERROR((INDEX(f,x,3)-INDEX(f,1,3))/(INDEX(f,x,1)-INDEX(f,1,1)),"N/A"))))
E5:E21	E5	=LET(x,E$2,f,TAKE(FILTER(B$5:D5,C$5:C5=C5),-x),IFERROR((INDEX(f,x,3)-INDEX(f,1,3))/(INDEX(f,x,1)-INDEX(f,1,1)),"N/A"))
Dynamic array formulas.

 

[Eric W]

A bit shorter yet, but a bit more obscure:

=LET(x,$E$2,f,FILTER(B$5:D5,C$5:C5=C5),r,ROWS(f),a,r-{0,1}*(x-1),IF(r<x,"N/A",SLOPE(INDEX(f,a,3),INDEX(f,a,1))))

 

[Bossi]

EDIT - Solved my follow-up issue!

Thanks!! In trying to adapt this to my actual spreadsheet, however, I'm running into one issue that my example missed...

Let's say I have one more column in between the one I'm actually interested in... so now Column D is full of irrelevant data, and I'm trying to read out of what's now Column E?

Yet again I feel like there's probably just a small tweak to one of the formula above that I'm not yet finding.

 

[Bossi]

Ahh, nevermind - found what I needed to change! I suspected one of those numbers was a column modifier... just had to change a 3 to a 4. Solved! Thanks!!

 

[Eric W]

Ah, you just beat me! Glad you found the issue. For future reference, it's best to include all pertinent information in your question, including row and column headers, and any other columns. Ideally, try using the xl2bb tool to show your worksheet. That allows the helpers here to copy your data directly without having to retype everything. You'll likely get faster responses if you do.