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?

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????