Highlighting 36 consecutive months of highest value

[ChrisCione]

I am using the following formula to find the largest sum of 36 consecutive values in a sizable range: MaxN(36,K2:K181)

Is there a method to highlight or otherwise identify which 36 consecutive values Excel is using?

 

[duggie33]

This is what I came up with. To work with a smaller example, I used six consecutive values rather than 36. I am certain there is a cleaner way to do this, but I have not figured it out...

Book1
	A	B
1		21
2	162	870
3	61	1020
4	166	1307
5	56	1222
6	236	1526
7	189	1429
8	312	1369
9	348	1181
10	81	957
11	360	989
12	139	1000
13	129	1156
14	124	1253
15	124	1186
16	113	1179
17	371	1307
18	295	1141
19	226	1119
20	57	1241
21	117	1468
22	241	1665
23	205	1483
24	273	1366
25	348	1242
26	284
27	314
28	59
29	88
30	149
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MATCH(MAX(B2:B30),B2:B30,0)
B2:B30	B2	=IF(COUNT(A2:A7)=6,SUM(A2:A7),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A30	Expression	=AND(ROW(A2)>$B$1,ROW(A2)<=$B$1+6)	text	NO

Hope that helps,

Doug

 

[Fluff]

No idea what MaxN is, but how about

Fluff.xlsm
	K			N
2	39			94
3	61			79
4	21			53
5	17			80
6	94			96
7	79			63
8	53			95
9	80			83
10	96			88
11	63			79
12	95			95
13	83			32
14	88			99
15	79			75
16	95			100
17	32			97
18	99			71
19	75			71
20	100			72
21	97			25
22	71			72
23	71			83
24	72			82
25	25			76
26	72			23
27	83			52
28	82			59
29	76			88
30	23			94
31	52			91
32	59			26
33	88			78
34	94			96
35	91			42
36	26			98
37	78			79
38	96
39	42
40	98
41	79
42	69
43	80
44	40
45	33
46	37
47	72
48	48
49	26
50	100
Data

Cell Formulas
Range	Formula
N2:N37	N2	=LET(s,SCAN(0,SEQUENCE(180),LAMBDA(a,b,SUM(CHOOSEROWS(K2:K300,SEQUENCE(36,,b))))),CHOOSEROWS(K2:K300,SEQUENCE(36,,XMATCH(MAX(s),s))))
Dynamic array formulas.