Percent Change in a List of States

[Rosstamon]

Hello,
I am keeping a running chart on Covid 19 cases per state in the US and its territories. I want to calculate percent change for each state, but on my spreadsheet the new data won't necessarily always be the same number of rows away from the previous data. In the image you can see that New York is on line 7, 71, 136 etc. so they are not always the same number of rows away from the previous row. I'm wondering if there is a formula that will say, in column C, find the previous instance of this states name (in this case New York) and do the calculation. I assume this will be some sort of array with a calculation, but can't quite figure it out.

 

[DRSteele]

This might help. Note it is an official Excel Table that uses structured references.

MrExcel posts18.xlsx
	C	D	E	F	G
6	Date reported	AHS Zone	Count	Ch per Zone	%Ch per Zone
7	3/6/2020	Calgary	1	1
8	3/9/2020	Calgary	3	2	200%
9	3/9/2020	Edmonton	3	3
10	3/10/2020	Calgary	6	3	100%
11	3/10/2020	Edmonton	3	0	0%
12	3/11/2020	Calgary	6	0	0%
13	3/11/2020	Central	1	1
14	3/11/2020	Edmonton	1	-2	-67%
15	3/12/2020	Calgary	2	-4	-67%
16	3/13/2020	Calgary	7	5	250%
17	3/13/2020	Edmonton	1	0	0%
18	3/14/2020	Calgary	13	6	86%
19	3/14/2020	Edmonton	6	5	500%
20	3/15/2020	Calgary	8	-5	-38%
21	3/15/2020	Edmonton	1	-5	-83%
22	3/16/2020	Calgary	16	8	100%
23	3/16/2020	Central	1	0	0%
24	3/16/2020	Edmonton	7	6	600%
25	3/16/2020	North	2	2
26	3/16/2020	South	1	1
27	3/17/2020	Calgary	1	-15	-94%
28	3/17/2020	Central	1	0	0%
29	3/17/2020	Edmonton	8	1	14%
30	3/17/2020	North	1	-1	-50%
31	3/18/2020	Calgary	24	23	2300%
32	3/18/2020	Edmonton	4	-4	-50%
33	3/18/2020	North	1	0	0%
34	3/18/2020	South	3	2	200%
Sheet1 (2)

Cell Formulas
Range	Formula
F7:F34	F7	=[@Count]-SUM((MAXIFS([Date reported],[Date reported],"<"&[@[Date reported]],[AHS Zone],[@[AHS Zone]])&[@[AHS Zone]]=[Date reported]&[AHS Zone])*[Count])
G7:G34	G7	=IFERROR([@Count]/SUM((MAXIFS([Date reported],[Date reported],"<"&[@[Date reported]],[AHS Zone],[@[AHS Zone]])&[@[AHS Zone]]=[Date reported]&[AHS Zone])*[Count])-1,"")

 

[Rosstamon]

Holy SMOKES! Thanks, I'll have to absorb what this is doing for a while before I attempt to apply it. I appreciate your response.