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
<(E-Mail Removed)> wrote in message
news:d99c7cc0-24b9-4f63-9c81-(E-Mail Removed)...
> 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
>
|
Similar Threads
