Average

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

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


It depends on your scoring rules.

Rep1 had 7 #1s over 15 opportunities (47%), average score = 1.7.
Rep3 had 6 #1s over 11 opportunities (54%), average score = 1.9.

Who wins?

rep 1 had 7 #1s over 15 opportunites
rep3 had 6 #1s over 15 opportunites as well

"smartin" wrote:

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


It depends on your scoring rules.

Rep1 had 7 #1s over 15 opportunities (47%), average score = 1.7.
Rep3 had 6 #1s over 11 opportunities (54%), average score = 1.9.

Who wins?

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

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

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

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

I cant find your email address

"Bob Bridges" wrote:

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

I need your email address, I used the one here but the mail was returned,
send to mine please..

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

Sorry; I thought you could click on my name and see it. Use
", only without the word "antispam".

--- "Vercingetorix.XIII" wrote:
I cant find your email address

--- "Bob Bridges" wrote:
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????