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
<tbody>
</tbody>
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
Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Hire Date | Column or Month Of Max Q4 | Max Q4 | ||
4 | 4 | 4 | 3 | 3 | 4 | 4 | 4 | 4 | 3 | 2 | 2 | 02-01-2017 | Oct or 9 | 4 | ||
<tbody>
</tbody>