Ok, so if you're just going to count the number of times each contestant
makes #1 and ignore other status rankings - that has to be up to you, because
there are many reasonable ways to do it - then on paper it would look
something like this:

Person A: 1, 3, , , 1, 4 - 2 times at #1
Person B: 2, 1, 3, 3, 4, 2 - 1 time at #1
Person C: 1, 2, 2, 4, 5, 1 - 2 times at #1

Since person A and person C tied - they both hit #1 twice - you do the tie
breaker by the number of times they got status #2. Person C got that twice,
person A not at all, so C wins.

How do you do that in Excel? One way might be to have an extra column out
at the right for each status; if the status code runs from 1 to 5, then have
five extra columns, each one doing a COUNTIF for that column's status in that
row's range of cells. If the days of the month run from columns C to AE, for
instance, then in AF could be =COUNTIF(Crow:AErow,1) to get the number of 1s.
In AG is =COUNTIF(Crow:AErow,2) and so on. Format these five rows as "00"
so they all have leading zeros ("01", "05" and so on) even if they're
single-digit numbers. Then in AK put =VALUE(AFrow&AGrow&AHrow&AIrow&AJrow),
which concatenates all the values into a single ten-digit number with all the
1s for that worker in the leading two digits and all the fives in the
trailing two. The person with the highest number had the most 1s, or (if
there's a tie for 1s) the most 2s, and so on. You'll have to check for ties
all the way down, because if you're giving out prizes on that basis people
get sensitive about it. But a tie all the way down would be pretty rare.

I have a sample in a spreadsheet I can send you if you want to contact me
via email.

--- "Vercingetorix.XIII" wrote:
ANSWER: 1st would win because he was #1 twice.

--- "Vercingetorix.XIII" wrote:
You are right, I am confused so will re-phrase the question-who wins? How do
I figure it out? should there be a middle value? ( the list grows daily) or
should I just take top 5 or so? Any suggestions?

--- "Bob Bridges" wrote:
Two questions:

1) How do you choose to weight the scores? For example, given two
persons {1, 4, 5, 1} and {1, 2, 2, 2}, does the first one win because he
was a #1 status twice and the second only once, or does the second
win because his average is higher than the first? And if a lower status
lowers the average, by how much? Does a 3 lower it by only a little
more than a 2, or much more?

2) You said you wanted to take absences into account, but in what way? By
not counting it toward the average at all, or by assigning it some middle
value, or by assigning it some really terrible value, or what?

If you're looking for suggestions on how you might weigh the different
values, well - or if you know how you want to weigh them but aren't sure
how to calculate the average given your weighting system, that's well too
but it's a different question.

Here are two sample calculations:

Method 1: The straight numerical status values are their own weight (a
status #1 counts 1, status #3 counts 3 etc), the overall score is the average
of all values, and missing values are not counted as part of the average:
Person A: 1, 3, , , 1, 4 - avg=(1+4+1+2)/4 = 2.25
Person B: 2, 1, 3, 3, 4, 2 - avg=(2+3+4+1+3+2)/6 = 2.5
...A has the lower score so he "wins".

Method 2: 1 counts 0, 2 counts 2, 3 counts 4, 4 counts 6, 5 counts 8, a
missing value (absence) counts 10.
Person A: 1, 3, , , 1, 4 - avg=(0+4+10+10+0+6)/6 = 5
Person B: 2, 1, 3, 3, 4, 2 - avg=(2+0+4+4+6+2)/6 = 3
...B has the lower score so he "wins".

You have to decide how you want to calculate each person's score before
you can figure out how to make that calculation in Excel.

--- "Vercingetorix.XIII" wrote:
I need some help creating a formula to calculate who was at #1 status the
most throughout the month. Some people were not on list everyday and some
were, so I have to take into account for that. example:

a b c d e f g h i j k l m n o p
1 Rep1 1 1 1 2 1 2 2 2 1 3 1 2 3 3 1
2 Rep2 2 1 2 3 3 1 2 2 2 1 4 4 4
3 Rep3 3 1 1 3 3 1 3 3 1 1 1

I am sure you get the picture, I tried a few different ones but I have to
have an accurate avg score at end of month, can someone please help????