Counting Consecutive Months in Top Third

[agrandstaff]

Ok. I need to have a formula which would look at a number of columns
and tell me now many 'consecutive' months someone has been in the top
33% of the numbers in that column to date. IE . . .Ann was in the top
33% in Jan and Feb but not in March so in March I would want the
formula to spit out '2', In April I would want it to start over again
at '1' (If in fact she was in the top 33% in March, if not then it
should say '0'). Hardest part in figuring this out (in my head
anyway)
is how to get it to give me the number as of the current date.
Figures
=NON() would have to be in there somewhere but not sure how. Hope I
explained all that properly.

JAN FEB MAR
Jane 34.5 44.6 77.8
Bob 22.3 12.5 34.6
Bill 32.6 87.7 44.4
Jill 44.3 66.5 34.8
Ann 87.6 55.2 15.6
Eric 74.5 33.3 13.8
Jim 44.7 22.8 22.3
Lisa 56.4 55.4 55.9
Sam 67.1 64.8 70.0

 

[Bernie Deitrick]

You need to make another table. For example if your table starts in A1, with names in column B and
labels in row 1, then in another cell in column B, say B21, perhaps, use the formula

=IF(RANK(B2,B$2:B$10)<=(COUNTA(B$2:B$10)/3),"Top","")

and copy to be as large as your data table. Then you can use a formula like

=IF(XXX21="Top",1+SUMPRODUCT((C21:XXX21=B21:YYY21)*(C21:XXX21="Top")),0)
where XXX is the last column's letter, and YYY is the last column but one's letter.


HTH,
Bernie
MS Excel MVP