Counting Consecutive Months in Top Third

A

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top