I have a series of quartile performances by month, an employee lands in a certain quartile for a month 1,2,3,4. I am counting the max consecutive occurrences of being in Q4, for my example below this occurs Jul, Aug Sep, Oct for a Max Consecutive Occurrence of 4. What I am struggling with figuring out is I need to know what month or column that occurred in so I can calculate what their tenure would have been at that time.
I am using:
={MAX(FREQUENCY(IF(A2:L2=4,COLUMN(A2:L2)),IF(A2:L2<>4,COLUMN(A2:L2))))}
to determine the # of max consecutive occurrences of "4"
Example
[TABLE="width: 500"]
<tbody>[TR]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Hire Date[/TD]
[TD]Column or Month Of Max Q4[/TD]
[TD]Max Q4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]02-01-2017[/TD]
[TD]Oct or 9[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am using:
={MAX(FREQUENCY(IF(A2:L2=4,COLUMN(A2:L2)),IF(A2:L2<>4,COLUMN(A2:L2))))}
to determine the # of max consecutive occurrences of "4"
Example
[TABLE="width: 500"]
<tbody>[TR]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Hire Date[/TD]
[TD]Column or Month Of Max Q4[/TD]
[TD]Max Q4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]02-01-2017[/TD]
[TD]Oct or 9[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]