Large and if function!

[Yan]

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

 

[Gary''s Student]

Your English is much better than my French.

Say the scores are in A1 thru A10. In B1 enter:
=11-RANK(A1,$A$1:$A$10)
and copy down thru B10

For example:

3240 3
3026 1
3430 6
3854 10
3493 7
3796 9
3268 4
3667 8
3361 5
3207 2

 

[Michael M]

Hi Yan
Have a look at the RANK() formula.
This will achieve what you need, but make sure you make your range absolute.
RANK(A1,$A$1:$A$10,1)

HTH
Michael M

 

[Yan]

Thx for the response,
what i forgot to say, is if 2 score are equal, we need to divide the total
pts award by 2, depending of the position....

ex: team 1 3200= 9
team 2 3200= 9

therefore, instead of having 1 team at 9 and one team at 8, we need to
divide the pts between each...

thx you!

 

[T. Valko]

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

 

[Teethless mama]

Assume your data in A1:A10
Sort your data in Ascending order

in B1: 1
in B2: =IF(A1=A2,B1,B1+1)
copy down

 

[T. Valko]

That doesn't follow the points structure.

Biff

Assume your data in A1:A10
Sort your data in Ascending order

in B1: 1
in B2: =IF(A1=A2,B1,B1+1)
copy down

 

[Yan]

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]

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]

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]

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