Thx Biff!

It is working, but is their an easier way to do it, cause i need to change
the colums and rows number when, i am building the sheet, what should i do,
just change the colums and rows into the program,,,,

Yan

"T. Valko" wrote:

The formulas I suggested will take care of any ties (unless I didn't
understand what you want).

Here's a sample file that demonstrates this:

http://cjoint.com/?gigyy2IK7Z

Notice the ties in the scores and the ranks and how the points are
distributed. The point distribution should equal the total points available.

Biff

"Yan" wrote in message
...
Ok, that seems to work, now the only problem i got is that...when their is
a
tie..

the total pts from booth position as to be count ex: pos.7 is 5 and pos.8
is
4 equal 9 total, now this need to be devide by the 2 position, (4.5 pts
each)
and so on...

so, if we have a team that is tie for 3 place, then is 8pts + 7pts=15pts
/2

can you help?

"T. Valko" wrote:

the largest number score 1 trought 10, since they have 10 teams, then
when
each score is found, depending on the largest number to the smallest,
we
assign pts....10 to 0...

If you have 10 teams and give them points from 10 to 0.....how do you do
that since 10 to 0 is 11? I'm sure you meant 10 to 1?

Assume scores are in the range B2:B10

Use a rank formula in column C:

C2: =RANK(B2,B$2:B$10

Copy down to C10

Create a 2 column table for the point distribution:

H2:H11 = 1,2,3,4,5,6,7,8,910
I2:I11 = 10,9,8,7,6,5,4,3,2,1

Enter this formula in D2 for the points (accounts for ties):

=SUM(OFFSET(I$2,MATCH(C2,H$2:H$11,0)-1,,COUNTIF(C$2:C$11,C2)))/COUNTIF(C$2:C$11,C2)

Copy down to D10

Biff

"Yan" wrote in message
...
Hello!

I Have a problem i try to solve...

I am building a Bowling spreadsheet workbook...
I am trying to do something, since they are assigning PTS, by total
pinfall....

What i want to do is to ask excel to look into a specific colums and
find
the largest number score 1 trought 10, since they have 10 teams, then
when
each score is found, depending on the largest number to the smallest,
we
assign pts....10 to 0...

ex: Team 1 = 3500 pin fall
Team 2 = 3450 pin fall

Then Team 1 will get 10pts, and Team will have 9 pts and so on...

I know thE =LARGE command, but i need to had another command to it...

Ex: =large($a$1:$a$5,1),=large($a$1:$a$5,2), what i need to had is
telling
the formulas to display 10pts for the largest number, and so on....

can someone help me?

Sorry! for my writing, I am French and sometime is does not sound
right!

Yan

is their an easier way to do it

If I knew of an easier way then I'd use it, but I don't.

It sounds like you probably do this everytime your league meets. You could
use dynamic ranges that automatically adjust as you add new data. See this
for instructions:

http://contextures.com/xlNames01.html#Dynamic

Biff

"Yan" wrote in message
...
Thx Biff!

It is working, but is their an easier way to do it, cause i need to change
the colums and rows number when, i am building the sheet, what should i
do,
just change the colums and rows into the program,,,,

Yan

"T. Valko" wrote:

The formulas I suggested will take care of any ties (unless I didn't
understand what you want).

Here's a sample file that demonstrates this:

http://cjoint.com/?gigyy2IK7Z

Notice the ties in the scores and the ranks and how the points are
distributed. The point distribution should equal the total points
available.

Biff

"Yan" wrote in message
...
Ok, that seems to work, now the only problem i got is that...when their
is
a
tie..

the total pts from booth position as to be count ex: pos.7 is 5 and
pos.8
is
4 equal 9 total, now this need to be devide by the 2 position, (4.5 pts
each)
and so on...

so, if we have a team that is tie for 3 place, then is 8pts +
7pts=15pts
/2

can you help?

"T. Valko" wrote:

the largest number score 1 trought 10, since they have 10 teams,
then
when
each score is found, depending on the largest number to the
smallest,
we
assign pts....10 to 0...

If you have 10 teams and give them points from 10 to 0.....how do you
do
that since 10 to 0 is 11? I'm sure you meant 10 to 1?

Assume scores are in the range B2:B10

Use a rank formula in column C:

C2: =RANK(B2,B$2:B$10

Copy down to C10

Create a 2 column table for the point distribution:

H2:H11 = 1,2,3,4,5,6,7,8,910
I2:I11 = 10,9,8,7,6,5,4,3,2,1

Enter this formula in D2 for the points (accounts for ties):

=SUM(OFFSET(I$2,MATCH(C2,H$2:H$11,0)-1,,COUNTIF(C$2:C$11,C2)))/COUNTIF(C$2:C$11,C2)

Copy down to D10

Biff

"Yan" wrote in message
...
Hello!

I Have a problem i try to solve...

I am building a Bowling spreadsheet workbook...
I am trying to do something, since they are assigning PTS, by total
pinfall....

What i want to do is to ask excel to look into a specific colums and
find
the largest number score 1 trought 10, since they have 10 teams,
then
when
each score is found, depending on the largest number to the
smallest,
we
assign pts....10 to 0...

ex: Team 1 = 3500 pin fall
Team 2 = 3450 pin fall

Then Team 1 will get 10pts, and Team will have 9 pts and so on...

I know thE =LARGE command, but i need to had another command to
it...

Ex: =large($a$1:$a$5,1),=large($a$1:$a$5,2), what i need to had is
telling
the formulas to display 10pts for the largest number, and so on....

can someone help me?

Sorry! for my writing, I am French and sometime is does not sound
right!

Yan