CF for Top 4 Consecutive Values

[gannybun]

A	B	C	D	E	F	G	H	I	J	K	L	TOP 4 Consecutive (SUM)
20	20	60	90	10	20	20	20	50	50	50	50	200

Sum of Top 4 Consecutive, the formula i am using is =SUMPRODUCT(MAX(A2:I2+B2:J2+C2:K2+D2:L2))


I am trying to do Conditional Formatting to highlight the cells, that have the top 4 consecutive value but i am unable to find any.
I wonder if any kind folks can help me out.

 

[gannybun]

 

[Peter_SSs]

Welcome to the MrExcel board!

One example is not much to go on. To clarify the requirement, could you give 4 or 5 varied examples and the expected results?

 

[gannybun]

I am trying to find the TOP 4 CONSECUTIVE VALUE in row 2 by using Conditional Formatting to highlight the cells if there are top 4 consecutive value in row 2.

the result will look like the above top 4 highlighted in yellow. but i am unable to make it work in CF.

 

[Peter_SSs]

..

Welcome to the MrExcel board!

One example is not much to go on. To clarify the requirement, could you give 4 or 5 varied examples and the expected results?

 

[gannybun]

i am sorry, newbie here. more examples in Row 2 to 5

I am trying to find the TOP 4 CONSECUTIVE VALUE in by individual rows by using Conditional Formatting to highlight the cells if there are top 4 consecutive value in row 2.
eg Row 2, the result(i did it manually) will look like the below top 4 highlighted in yellow (cell I2,J2,K2 & L2). but i am unable to make it work in CF. looking to see if anyone can help me out here.

 

[Peter_SSs]

Thanks, that definitely shows a different interpretation to what I thought it might have been with just that first example.

See if this works for you.
(You might also investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 12 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	20	20	60	90	10	10	20	20	50	50	50	50
3	40	40	40	60	60	60	90	60	90	90	20	20
4	100	90	700	100	600	10	200	50	700	40	40	0
5	200	200	200	200	700	900	700	500	500	500	500	500
Top 4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:L5	Expression	=LET(st,SUBTOTAL(9,OFFSET($A2,,SEQUENCE(,COLUMNS($A2:$L2)-3,0),,4)),fc,MATCH(MAX(st),st,0),AND(COLUMNS($A:A)>=fc,COLUMNS($A:A)<fc+4))	text	NO

 

[gannybun]

works like a charm!! thank you! lifesaver

=LET(st,SUBTOTAL(9,OFFSET($A2,,SEQUENCE(,COLUMNS($A2:$L2)-3,0),,4)),fc,MATCH(MAX(st),st,0),AND(COLUMNS($A:A)>=fc,COLUMNS($A:A)<fc+4))

are the green column the variable should i decide to move my data set to a different column (eg. column E only)?

 

[Peter_SSs]

works like a charm!! thank you!

You're welcome.

are the green column the variable should i decide to move my data set to a different column (eg. column E only)?

Everywhere I have an A it is the first column of the data. L is the last column of the data.
If you are unsure or have problems changing, just set it up exactly like the sample and then insert columns and/or rows until the data is where you really want it. Excel will do the adjustments for you.