| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Large and if function!
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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 -- Gary''s Student - gsnu200727 "Yan" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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! "Yan" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
That doesn't follow the points structure.
Biff "Teethless mama" wrote in message ... 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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Large and if function!
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| large function help please? | Excel Worksheet Functions | |||
| max or large function | Excel Discussion (Misc queries) | |||
| IF Function too Large | Excel Discussion (Misc queries) | |||
| Large function | Excel Worksheet Functions | |||
| Help on Large Function | Excel Worksheet Functions | |||
Linear Mode
