Conditional Formatting based on rolling month values

[nburaq]

Hi,
I have data shown as below and would like to highlight values based on rule for always last 12 months. In my previous post this was solved bu using formula =SUM(IF(TAKE(FILTER(B2:B25,B2:B25<>""),-12)>30,1,0)) but this time I wanted to highlight cells with this part only TAKE(FILTER(B2:B25,B2:B25<>""),-12)>30 however it does not work well. I will be so glad if someone solve this problem. Thanks in advance !

Datum	Location1
Jan/22	43,70
Feb/22	2,24
Mar/22	28,96
Apr/22	25,57
May/22	41,19
Jun/22	37,42
Jul/22	41,88
Aug/22	37,37
Sep/22	39,31
Oct/22	38,79
Nov/22	37,91
Dec/22	40,40
Jan/23	74,69
Feb/23	59,99
Mar/23	60,69
Apr/23	60,69
May/23	52,00
Jun/23
Jul/23
Aug/23
Sep/23
Oct/23
Nov/23
Dec/23

 

[awoohaw]

so, to be clear. You want to highlight the last 12 rows where both column A and column B are populated?
I ask this because I do not see a baseline date to start going back 12 months.

 

[awoohaw]

try this:

Mr excel questions 53.xlsm
	A	B	C	D
1	Datum	Location1	Cond Formt Formula	Formula Text
2	Jan/22	43.70	FALSE	=AND($B2<>"",$A2>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12))
3	Feb/22	2.24	FALSE
4	Mar/22	28.96	FALSE
5	Apr/22	25.57	FALSE
6	May/22	41.19	FALSE
7	Jun/22	37.42	TRUE
8	Jul/22	41.88	TRUE
9	Aug/22	37.37	TRUE
10	Sep/22	39.31	TRUE
11	Oct/22	38.79	TRUE
12	Nov/22	37.91	TRUE
13	Dec/22	40.40	TRUE
14	Jan/23	74.69	TRUE
15	Feb/23	59.99	TRUE
16	Mar/23	60.69	TRUE
17	Apr/23	60.69	TRUE
18	May/23	52.00	TRUE
19	Jun/23		FALSE
20	Jul/23		FALSE
21	Aug/23		FALSE
22	Sep/23		FALSE
23	Oct/23		FALSE
24	Nov/23		FALSE
25	Dec/23		FALSE
Sheet1

Cell Formulas
Range	Formula
D2	D2	=FORMULATEXT(C2)
C2	C2	=AND($B2<>"",$A2>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12))
C3:C25	C3	=AND(B3<>"",A3>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B25	Expression	=AND($B2<>"",$A2>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12))	text	NO

 

[nburaq]

Hi
Thanks for the solution. This was what I was looking for and is more than perfect!. Actually the only baseline is counting 12 months back each time when entering the data of the current month. In given example, it is JUN/23.

 

[awoohaw]

I'm happy you found a solution here at the forum. i'm sure there are other ways to accomplish the same task, but this works.
You can delete columns C & D as those were just for my development and to display in the post what is going on.

Best Wishes!

 

[nburaq]

Is it possible to add condition to this formula to highlight the values greater than 30 ? Thanks again

 

[awoohaw]

Well, that is a different question that is not pertinent to the topic/title of your original post. I think the forum would want you to start a new thread.
But. have you tried to do this yourself?
Take a look at this youtube tutorial on conditional formatting:

 

[Fluff]

that is a different question that is not pertinent to the topic/title of your original post.

Actually that's exactly what the OP asked for.

 

[awoohaw]

Actually that's exactly what the OP asked for.

You're right @Fluff , I missed that part in the OP. When the OP replied positively in Step 4 with an answer I thought it was complete.
My apologies @nburaq .

 

[awoohaw]

Sorry again, @nburaq

Here is a mini of what you asked for:

Mr excel questions 53.xlsm
	A	B	C	D
1	Datum	Location1	Cond Formt Formula	Formula Text
2	Jan/22	43.70	FALSE	=AND($B2>30,AND($B2<>"",$A2>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12)))
3	Feb/22	2.24	FALSE
4	Mar/22	28.96	FALSE
5	Apr/22	25.57	FALSE
6	May/22	41.19	FALSE
7	Jun/22	37.42	FALSE
8	Jul/22	41.88	FALSE
9	Aug/22	37.37	TRUE
10	Sep/22	39.31	TRUE
11	Oct/22	38.79	TRUE
12	Nov/22	25.00	FALSE
13	Dec/22	40.40	TRUE
14	Jan/23	74.69	TRUE
15	Feb/23	59.99	TRUE
16	Mar/23	60.69	TRUE
17	Apr/23	60.69	TRUE
18	May/23	52.00	TRUE
19	Jun/23	87.00	TRUE
20	Jul/23	90.00	TRUE
21	Aug/23		FALSE
22	Sep/23		FALSE
23	Oct/23		FALSE
24	Nov/23		FALSE
25	Dec/23		FALSE
nburaq

Cell Formulas
Range	Formula
D2	D2	=FORMULATEXT(C2)
C2:C25	C2	=AND($B2>30,AND($B2<>"",$A2>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B25	Expression	=AND($B2>30,AND($B2<>"",$A2>=AGGREGATE(14,6,(($A$2:$A$25<>"")*($A$2:$A$25)*($B$2:$B$25<>"")),12)))	text	NO