Dynamic Formula To Label Rolling 3 Months.

[Cubist]

Hi,

I need a formula that generates the "label" column below.
In the boolean column, there's always one and only one continuous range of TRUE.
When the boolean = TRUE, I want to generate a "Rolling 3 xxx" label starting from the last 3 months. Sometimes there aren't multiple of 3's so I want to label it "Incomplete Rolling 3".
When the boolean = FALSE, label it "-".

Book1
	A	B	C
2	Month	Boolean	Label
3	1/31/21	FALSE	-
4	2/28/21	FALSE	-
5	3/31/21	FALSE	-
6	4/30/21	FALSE	-
7	5/31/21	TRUE	Incomeplete Rolling 3
8	6/30/21	TRUE	Incomeplete Rolling 3
9	7/31/21	TRUE	Rolling 3 Prior - 2
10	8/31/21	TRUE	Rolling 3 Prior - 2
11	9/30/21	TRUE	Rolling 3 Prior - 2
12	10/31/21	TRUE	Rolling 3 Prior - 1
13	11/30/21	TRUE	Rolling 3 Prior - 1
14	12/31/21	TRUE	Rolling 3 Prior - 1
15	1/31/22	TRUE	Rolling 3 Prior
16	2/28/22	TRUE	Rolling 3 Prior
17	3/31/22	TRUE	Rolling 3 Prior
18	4/30/22	TRUE	Rolling 3 Current
19	5/31/22	TRUE	Rolling 3 Current
20	6/30/22	TRUE	Rolling 3 Current
21	7/31/22	FALSE	-
22	8/31/22	FALSE	-
23	9/30/22	FALSE	-
24	10/31/22	FALSE	-
25	11/30/22	FALSE	-
Sheet1

Cell Formulas
Range	Formula
A4:A25	A4	=EOMONTH(A3,1)

 

[Eric W]

Try:

Book3
	A	B	C
1
2	Month	Boolean	Label
3	1/31/2021	FALSE	-
4	2/28/2021	FALSE	-
5	3/31/2021	FALSE	-
6	4/30/2021	FALSE	-
7	5/31/2021	TRUE	Incomplete Rolling 3
8	6/30/2021	TRUE	Incomplete Rolling 3
9	7/31/2021	TRUE	Rolling 3 Prior - 2
10	8/31/2021	TRUE	Rolling 3 Prior - 2
11	9/30/2021	TRUE	Rolling 3 Prior - 2
12	10/31/2021	TRUE	Rolling 3 Prior - 1
13	11/30/2021	TRUE	Rolling 3 Prior - 1
14	12/31/2021	TRUE	Rolling 3 Prior - 1
15	1/31/2022	TRUE	Rolling 3 Prior
16	2/28/2022	TRUE	Rolling 3 Prior
17	3/31/2022	TRUE	Rolling 3 Prior
18	4/30/2022	TRUE	Rolling 3 Current
19	5/31/2022	TRUE	Rolling 3 Current
20	6/30/2022	TRUE	Rolling 3 Current
21	7/31/2022	FALSE	-
22	8/31/2022	FALSE	-
23	9/30/2022	FALSE	-
24	10/31/2022	FALSE	-
25	11/30/2022	FALSE	-
26
Sheet6

Cell Formulas
Range	Formula
C3:C25	C3	=LET(r,B3:B25,t,COUNTIF(r,TRUE),s,SEQUENCE(t),a,IFS(s>INT(t/3)*3,"Incomplete Rolling 3",s<4,"Rolling 3 Current",s<7,"Rolling 3 Prior",1,"Rolling 3 Prior - "&INT((s-7)/3)+1),v,XLOOKUP(TRUE,r,ROW(r),,0),IF(r,INDEX(a,t+v-ROW(r)),"-"))
A4:A25	A4	=EOMONTH(A3,1)
Dynamic array formulas.