Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Points Ranking Formulas
I have a spread sheet in which I enter players names and the place they
finished for a certain event. I would like the sheet to calculate their points according to where they finish and assign it to them along with a formula that calculates a bonus point value according to the number of players in a certain event. 1st place-450 pts + 30% bonus 2nd place-300 pts + 20% bonus 3rd place-180 pts + 12% bonus 4th place-120pts + 8% bonus 5th place-105 pts + 7% bonus 6th place 90 pts + 6% bonus 7th place 75 pts + 5% bonus 8th place 60pts + 4% bonus 9th-16th place 15 pts + 1% bonus 16th place 15 points Bonus point formula is (# players X 5)x % Any help would be appreciated |
#2
|
|||
|
|||
Points Ranking Formulas
Here's one crack at it ..
Sample file with the construct at: http://www.savefile.com/files/4535198 Points Ranking Formulas_Cabo_wksht.xls In Sheet2 -------- Table below is in A1:C17 Placing Points Bonus 1 450 30% 2 300 20% 3 180 12% 4 120 8% .... 15 15 1% 16 15 1% 2 defined names are created via: Insert Name Define Name Refers To -------------------- Bonus =Sheet2!$C$2:$C$17 Points =Sheet2!$B$2:$B$17 In Sheet1 ------- Table in cols A to F comprises: Name-Placing-Points-Bonus-Bonus Points-Total Play1 1 450 30.0% 24 474.0 Play2 2 240 16.0% 12.8 252.8 Play3 2 240 16.0% 12.8 252.8 Play4 4 120 8.0% 6.4 126.4 etc where Name col: Assumed maximum of 16 players (= # of placings in Sheet1) Placing col: Placings are manually input and assumed to include possibility of ties. Tied placings are assumed treated in the same way as RANK(...). For example, if the placing 2 appears twice, placing 3 would be skipped. And so on. Formulas placed in cols C to F: In C2: =SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2) In D2: =SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2) In E2: =(COUNTA(A:A)-1)*5*D2 In F2: =SUM(C2,E2) C2:F2 selected and copied down to F17 Note that Points and Bonus % are assumed pro-rata apportioned in the event of ties -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cabo Wabo Jim" <Cabo Wabo wrote in message ... I have a spread sheet in which I enter players names and the place they finished for a certain event. I would like the sheet to calculate their points according to where they finish and assign it to them along with a formula that calculates a bonus point value according to the number of players in a certain event. 1st place-450 pts + 30% bonus 2nd place-300 pts + 20% bonus 3rd place-180 pts + 12% bonus 4th place-120pts + 8% bonus 5th place-105 pts + 7% bonus 6th place 90 pts + 6% bonus 7th place 75 pts + 5% bonus 8th place 60pts + 4% bonus 9th-16th place 15 pts + 1% bonus 16th place 15 points Bonus point formula is (# players X 5)x % Any help would be appreciated |
#3
|
|||
|
|||
Points Ranking Formulas
Max,
I appreciate it, it seems to work expcept one thing. I may have up to 100 players but only 16 getting points. Is there anywhere I can input the number of players as to calculate the bonus in correlation with that number? "Max" wrote: Here's one crack at it .. Sample file with the construct at: http://www.savefile.com/files/4535198 Points Ranking Formulas_Cabo_wksht.xls In Sheet2 -------- Table below is in A1:C17 Placing Points Bonus 1 450 30% 2 300 20% 3 180 12% 4 120 8% .... 15 15 1% 16 15 1% 2 defined names are created via: Insert Name Define Name Refers To -------------------- Bonus =Sheet2!$C$2:$C$17 Points =Sheet2!$B$2:$B$17 In Sheet1 ------- Table in cols A to F comprises: Name-Placing-Points-Bonus-Bonus Points-Total Play1 1 450 30.0% 24 474.0 Play2 2 240 16.0% 12.8 252.8 Play3 2 240 16.0% 12.8 252.8 Play4 4 120 8.0% 6.4 126.4 etc where Name col: Assumed maximum of 16 players (= # of placings in Sheet1) Placing col: Placings are manually input and assumed to include possibility of ties. Tied placings are assumed treated in the same way as RANK(...). For example, if the placing 2 appears twice, placing 3 would be skipped. And so on. Formulas placed in cols C to F: In C2: =SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2) In D2: =SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2) In E2: =(COUNTA(A:A)-1)*5*D2 In F2: =SUM(C2,E2) C2:F2 selected and copied down to F17 Note that Points and Bonus % are assumed pro-rata apportioned in the event of ties -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cabo Wabo Jim" <Cabo Wabo wrote in message ... I have a spread sheet in which I enter players names and the place they finished for a certain event. I would like the sheet to calculate their points according to where they finish and assign it to them along with a formula that calculates a bonus point value according to the number of players in a certain event. 1st place-450 pts + 30% bonus 2nd place-300 pts + 20% bonus 3rd place-180 pts + 12% bonus 4th place-120pts + 8% bonus 5th place-105 pts + 7% bonus 6th place 90 pts + 6% bonus 7th place 75 pts + 5% bonus 8th place 60pts + 4% bonus 9th-16th place 15 pts + 1% bonus 16th place 15 points Bonus point formula is (# players X 5)x % Any help would be appreciated |
#4
|
|||
|
|||
Points Ranking Formulas
Max,
I also find that when I randomly enter the player place finish it does not matter if I put in 1 or 16 it calculates the points in the order I enter them, the first entry gets 1st place points the second 2nd place and so on. If I enter place number 5 first it gets first place points? "Max" wrote: Here's one crack at it .. Sample file with the construct at: http://www.savefile.com/files/4535198 Points Ranking Formulas_Cabo_wksht.xls In Sheet2 -------- Table below is in A1:C17 Placing Points Bonus 1 450 30% 2 300 20% 3 180 12% 4 120 8% .... 15 15 1% 16 15 1% 2 defined names are created via: Insert Name Define Name Refers To -------------------- Bonus =Sheet2!$C$2:$C$17 Points =Sheet2!$B$2:$B$17 In Sheet1 ------- Table in cols A to F comprises: Name-Placing-Points-Bonus-Bonus Points-Total Play1 1 450 30.0% 24 474.0 Play2 2 240 16.0% 12.8 252.8 Play3 2 240 16.0% 12.8 252.8 Play4 4 120 8.0% 6.4 126.4 etc where Name col: Assumed maximum of 16 players (= # of placings in Sheet1) Placing col: Placings are manually input and assumed to include possibility of ties. Tied placings are assumed treated in the same way as RANK(...). For example, if the placing 2 appears twice, placing 3 would be skipped. And so on. Formulas placed in cols C to F: In C2: =SUMIF($B$2:$B$17,$B2,Points)/COUNTIF($B$2:$B$17,$B2) In D2: =SUMIF($B$2:$B$17,$B2,Bonus)/COUNTIF($B$2:$B$17,$B2) In E2: =(COUNTA(A:A)-1)*5*D2 In F2: =SUM(C2,E2) C2:F2 selected and copied down to F17 Note that Points and Bonus % are assumed pro-rata apportioned in the event of ties -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cabo Wabo Jim" <Cabo Wabo wrote in message ... I have a spread sheet in which I enter players names and the place they finished for a certain event. I would like the sheet to calculate their points according to where they finish and assign it to them along with a formula that calculates a bonus point value according to the number of players in a certain event. 1st place-450 pts + 30% bonus 2nd place-300 pts + 20% bonus 3rd place-180 pts + 12% bonus 4th place-120pts + 8% bonus 5th place-105 pts + 7% bonus 6th place 90 pts + 6% bonus 7th place 75 pts + 5% bonus 8th place 60pts + 4% bonus 9th-16th place 15 pts + 1% bonus 16th place 15 points Bonus point formula is (# players X 5)x % Any help would be appreciated |
#7
|
|||
|
|||
Points Ranking Formulas
On Tue, 18 Oct 2005 12:46:05 -0700, "Cabo Wabo Jim"
wrote: Ron, That works great but same problem. If the number of players for each event varies that varies the bonus point structure for each event. I need to total up about 10 seperate events per month into one scoring system and ranking. Jim Either I did not understand what you wrote, OR you are not using the function as it was designed. If, as I wrote, "each players 'place' is entered into the data table area" then every player that played will be counted. Of course, only those that placed at level 1-16 will have a score generated. Perhaps if you post some real data that is not giving the correct results, it would be helpful. --ron |
#8
|
|||
|
|||
Points Ranking Formulas
Ron,
I'm not sure you know what I mean. For example the first event had 48 players with only 16 getting points, but the bonus is calculated by mutipling the number of players in the event (48) by 5 and multipling that number by the percentage of bonus they finished. So a person finishing in second would get 300 points and a 20% bonus ((48 x 5)x.20) PLACE LAST FIRST MEMBER # PL PTS Bonus% Bonus POINTS 1st Schaller Jim 20200005277 16 450 0.3 3 453 2nd 2 300 0.2 2 302 3rd 3 180 0.12 1.2 181.2 4th 4 120 0.08 0.8 120.8 5th 5 105 0.07 0.7 105.7 6th 6 90 0.06 0.6 90.6 7th 7 75 0.05 0.5 75.5 8th 6 90 0.04 0.4 90.4 9th 9 15 0.01 0.1 15.1 10th 10 15 0.01 0.1 15.1 11th 12 15 0.01 0.1 15.1 "Ron Rosenfeld" wrote: On Tue, 18 Oct 2005 12:46:05 -0700, "Cabo Wabo Jim" wrote: Ron, That works great but same problem. If the number of players for each event varies that varies the bonus point structure for each event. I need to total up about 10 seperate events per month into one scoring system and ranking. Jim Either I did not understand what you wrote, OR you are not using the function as it was designed. If, as I wrote, "each players 'place' is entered into the data table area" then every player that played will be counted. Of course, only those that placed at level 1-16 will have a score generated. Perhaps if you post some real data that is not giving the correct results, it would be helpful. --ron |
#9
|
|||
|
|||
Points Ranking Formulas
On Tue, 18 Oct 2005 14:16:15 -0700, "Cabo Wabo Jim"
wrote: Ron, I'm not sure you know what I mean. For example the first event had 48 players with only 16 getting points, but the bonus is calculated by mutipling the number of players in the event (48) by 5 and multipling that number by the percentage of bonus they finished. So a person finishing in second would get 300 points and a 20% bonus ((48 x 5)x.20) PLACE LAST FIRST MEMBER # PL PTS Bonus% Bonus POINTS 1st Schaller Jim 20200005277 16 450 0.3 3 453 2nd 2 300 0.2 2 302 3rd 3 180 0.12 1.2 181.2 4th 4 120 0.08 0.8 120.8 5th 5 105 0.07 0.7 105.7 6th 6 90 0.06 0.6 90.6 7th 7 75 0.05 0.5 75.5 8th 6 90 0.04 0.4 90.4 9th 9 15 0.01 0.1 15.1 10th 10 15 0.01 0.1 15.1 11th 12 15 0.01 0.1 15.1 From what you write, it seems I understood exactly what you want. However, in the above example you show a worksheet set up entirely different from the way I showed you to do it in my post. Had you set it up and entered data the way I had described, then the formula would have done exactly what you requested. Because EACH player that participated would have had a finishing number; the first 16 would have gotten scores, but the bonus would be computed based on the total number of players, who would be identified because they would all have some rank. The function I wrote can be easily modified to handle other similar situations. However, if you are going to set up separate worksheets (or tables) for each event as in the example you give above, there's really no need for anything fancy. All you need to do is enter in some cell the number of participants for each event. Name it, for example, NumPlayers, and then use the formula: =PTS+5*NumPlayers*BonusPerCent to compute the score for each player. You could then have a summary sheet where you added together VLOOKUP formula results for each sheet, using the player number as the lookup_value. --ron |
#10
|
|||
|
|||
Points Ranking Formulas
Try this enhanced sample file, which addresses both your questions. I've
provided 2 sheets depending on whether lower scores are better (eg: golf), or higher scores are better (eg: almost all games other than golf <g). You just need to input/maintain the names and scores into cols A and B (Just clear away the dummy data). The final results will auto-compute in cols H to M. Cols C to G are helper cols (can be hidden away). See whether the sample better suits your needs. http://www.savefile.com/files/4835997 1_Points Ranking Formulas_Cabo_wksht.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cabo Wabo Jim" wrote in message ... Max, I also find that when I randomly enter the player place finish it does not matter if I put in 1 or 16 it calculates the points in the order I enter them, the first entry gets 1st place points the second 2nd place and so on. If I enter place number 5 first it gets first place points? "Cabo Wabo Jim" wrote in message ... Max, I appreciate it, it seems to work expcept one thing. I may have up to 100 players but only 16 getting points. Is there anywhere I can input the number of players as to calculate the bonus in correlation with that number? |
#11
|
|||
|
|||
Points Ranking Formulas
"Ron Rosenfeld" wrote: On Tue, 18 Oct 2005 14:16:15 -0700, "Cabo Wabo Jim" wrote: Ron, I'm not sure you know what I mean. For example the first event had 48 players with only 16 getting points, but the bonus is calculated by mutipling the number of players in the event (48) by 5 and multipling that number by the percentage of bonus they finished. So a person finishing in second would get 300 points and a 20% bonus ((48 x 5)x.20) PLACE LAST FIRST MEMBER # PL PTS Bonus% Bonus POINTS 1st Schaller Jim 20200005277 16 450 0.3 3 453 2nd 2 300 0.2 2 302 3rd 3 180 0.12 1.2 181.2 4th 4 120 0.08 0.8 120.8 5th 5 105 0.07 0.7 105.7 6th 6 90 0.06 0.6 90.6 7th 7 75 0.05 0.5 75.5 8th 6 90 0.04 0.4 90.4 9th 9 15 0.01 0.1 15.1 10th 10 15 0.01 0.1 15.1 11th 12 15 0.01 0.1 15.1 From what you write, it seems I understood exactly what you want. However, in the above example you show a worksheet set up entirely different from the way I showed you to do it in my post. Had you set it up and entered data the way I had described, then the formula would have done exactly what you requested. Because EACH player that participated would have had a finishing number; the first 16 would have gotten scores, but the bonus would be computed based on the total number of players, who would be identified because they would all have some rank. The function I wrote can be easily modified to handle other similar situations. However, if you are going to set up separate worksheets (or tables) for each event as in the example you give above, there's really no need for anything fancy. All you need to do is enter in some cell the number of participants for each event. Name it, for example, NumPlayers, and then use the formula: =PTS+5*NumPlayers*BonusPerCent to compute the score for each player. You could then have a summary sheet where you added together VLOOKUP formula results for each sheet, using the player number as the lookup_value. --ron Ron, Thanks again for your help, I just get a bit confused with all the functions and how to exactly set them up. I'm still a novice at this, I am not sure how to set up a summary sheet, seperate worksheets/tables or use the VLOOKUP formula. But I will work with the information you provided, which makes sense to me, and try to understand it as it relates to my specific situation to assure I am setting up the functions correctly as to have no errors. |
#12
|
|||
|
|||
Points Ranking Formulas
Here's the set-up in the enhanced sample file
In sheet: Lower Score is Better (eg Golf) --------------------- The set-up in cols A to M (data/formulas in row2 down): A Name B Score C TB1 D Name E Score F Rank G TB2 H Name I Placing J Points K Bonus L Bonus Points M Total Cols A & B are for input of player names & scores Cols C to G are helpers Cols H to M are the results In C2: =IF(B2="","",B2+ROW()/10^10) In D2: =IF(ISERROR(SMALL($C:$C,ROWS($A$1:A1))),"", INDEX(A:A,MATCH(SMALL($C:$C,ROWS($A$1:A1)),$C:$C,0 ))) D2 is copied to E2 In F2: =IF(E2="","",RANK(E2,OFFSET($E$2,,,COUNTA(E:E)-1),1)) In G2: =IF(F2="","",F2+ROW()/10^10) In H2: =IF(ISERROR(SMALL($G:$G,ROWS($A$1:A1))),"", INDEX(D:D,MATCH(SMALL($G:$G,ROWS($A$1:A1)),$G:$G,0 ))) In I2: =IF(ISERROR(SMALL($G:$G,ROWS($A$1:B1))),"", INDEX(F:F,MATCH(SMALL($G:$G,ROWS($A$1:B1)),$G:$G,0 ))) In J2: =IF(E2="","",SUMIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2,Points) /COUNTIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2)) In K2: =IF(E2="","",SUMIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2,Bonus) /COUNTIF(OFFSET($I$2,,,COUNTA(B:B)-1),$I2)) In L2: =IF(E2="","",(COUNTA(B:B)-1)*5*K2) In M2: =IF(E2="","",SUM(J2,L2)) D2:M2 selected and filled down In sheet: Higher Score is Better (Others) --------------------- Exactly the same set-up & suite of formulas in cols C to M, except for a small change made to col F's formulas: In F2: =IF(E2="","",RANK(E2,OFFSET($E$2,,,COUNTA(E:E)-1))) F2 copied down In sheet: Point n Bonus Structure --------------- Table below is in A1:C17 Placing Points Bonus 1 450 30% 2 300 20% 3 180 12% 4 120 8% .... 15 15 1% 16 15 1% 2 defined names are created via: Insert Name Define Name Refers To -------------------- Bonus =Sheet2!$C$2:$C$17 Points =Sheet2!$B$2:$B$17 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#13
|
|||
|
|||
Points Ranking Formulas
Cabo, I've since responded to your feedback in the other branch, with an
enhanced sample for you to play with (.. spent the better part of the early morning over here getting that up, gawd!<g..). Spare some time for a look-see there as well .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#14
|
|||
|
|||
Points Ranking Formulas
Max,
I appreciate you help and I have played with your solution, but I find that when I randomly enter the player place finish it does not matter if I put in 1 or 16 it calculates the points in the order I enter them, the first entry gets 1st place points the second 2nd place and so on. If I enter place number 5 first it gets first place points? If I use a seperate column to enter the number of players in an event how can I incoprporate that into the formulas? Thanks again! "Max" wrote: Cabo, I've since responded to your feedback in the other branch, with an enhanced sample for you to play with (.. spent the better part of the early morning over here getting that up, gawd!<g..). Spare some time for a look-see there as well .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#15
|
|||
|
|||
Points Ranking Formulas
"Cabo Wabo Jim" wrote
Max, I appreciate you help and I have played with your solution, but I find that when I randomly enter the player place finish it does not matter if I put in 1 or 16 it calculates the points in the order I enter them, the first entry gets 1st place points the second 2nd place and so on. If I enter place number 5 first it gets first place points? These were your earlier comments to the 1st sample. I've since provided a new, 2nd sample file (new link) in my response to your reply in the other branch. Pl confirm that we're talking about the 2nd file. In case it was missed earlier, here's the link to the 2nd sample I posted over the http://www.savefile.com/files/4835997 File: 1_Points Ranking Formulas_Cabo_wksht.xls Which sort of game is your interest ? I've provided a choice of 2 for you to choose, in the 2 sheets: Lower Score is Better (eg Golf) Higher Score is Better (Others) If I use a seperate column to enter the number of players in an event how can I incorporate that into the formulas? You don't even need to do that. The relevant formulas will implicitly auto-calc the number of players from col B ("Score" col ) in either of the 2 sheets mentioned above in the 2nd sample file. The list of players in col A can be left "permanently" there, once entered. Just clear (or leave empty) the scores in col B for those players who are not participating in that round. Remember that the results are read-off / displayed within cols H to M in either of the 2 sheets: Lower Score is Better (eg Golf) Higher Score is Better (Others) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#16
|
|||
|
|||
Points Ranking Formulas
Thanks Max, I missed that earlier, I will take a look at it ASAP.
Jim "Max" wrote: "Cabo Wabo Jim" wrote Max, I appreciate you help and I have played with your solution, but I find that when I randomly enter the player place finish it does not matter if I put in 1 or 16 it calculates the points in the order I enter them, the first entry gets 1st place points the second 2nd place and so on. If I enter place number 5 first it gets first place points? These were your earlier comments to the 1st sample. I've since provided a new, 2nd sample file (new link) in my response to your reply in the other branch. Pl confirm that we're talking about the 2nd file. In case it was missed earlier, here's the link to the 2nd sample I posted over the http://www.savefile.com/files/4835997 File: 1_Points Ranking Formulas_Cabo_wksht.xls Which sort of game is your interest ? I've provided a choice of 2 for you to choose, in the 2 sheets: Lower Score is Better (eg Golf) Higher Score is Better (Others) If I use a seperate column to enter the number of players in an event how can I incorporate that into the formulas? You don't even need to do that. The relevant formulas will implicitly auto-calc the number of players from col B ("Score" col ) in either of the 2 sheets mentioned above in the 2nd sample file. The list of players in col A can be left "permanently" there, once entered. Just clear (or leave empty) the scores in col B for those players who are not participating in that round. Remember that the results are read-off / displayed within cols H to M in either of the 2 sheets: Lower Score is Better (eg Golf) Higher Score is Better (Others) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#17
|
|||
|
|||
Points Ranking Formulas
Oops, forgot Sheet2 was renamed as: Point n Bonus Structure
Lines: Name Refers To -------------------- Bonus =Sheet2!$C$2:$C$17 Points =Sheet2!$B$2:$B$17 should read as: Name Refers To --------------------- Bonus ='Point n Bonus Structure'!$C$2:$C$17 Points ='Point n Bonus Structure'!$B$2:$B$17 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#18
|
|||
|
|||
Points Ranking Formulas
"Cabo Wabo Jim" wrote:
... I missed that earlier .. Aha! guessed as much <g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#19
|
|||
|
|||
Points Ranking Formulas
Max,
Sorry I have been out of town for meetings and did not have time to work on the spread sheet. I have a layout of what I want but can't seem to get it to work, if you'd like to e-mail me at I'd like to send you a copy of what I have? "Max" wrote: "Cabo Wabo Jim" wrote: ... I missed that earlier .. Aha! guessed as much <g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#20
|
|||
|
|||
Points Ranking Formulas
I've got your mail & file, Jim
Will follow up here .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#21
|
|||
|
|||
Points Ranking Formulas
Easier to start all over, Jim <g. Now that I see your detailed set-up,
here's the formulas you need to compute the points. I've implemented these in your file and will send it back to you. Placed in F7: =IF(OR(E7="",E$4=""),"",INDEX(Sheet2!$B:$B,MATCH(E 7,Sheet2!$A:$A,0)) +E$4*5*INDEX(Sheet2!$C:$C,MATCH(E7,Sheet2!$A:$A,0) )) Copied F7, then with CTRL key held down, selected cells H7,J7,L7,N7,P7,R7,T7,V7,X7 Then right-click paste special formulas OK This pastes the same formula adjusted relatively where required into all the required cells. Then just copy the formulae down the respective columns. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#22
|
|||
|
|||
Points Ranking Formulas
Thanks Max,
I think I'm starting to get it now. It takes awhile to understand how the formulas work. You did a great job on this! Cabo Wabo Jim "Max" wrote: Easier to start all over, Jim <g. Now that I see your detailed set-up, here's the formulas you need to compute the points. I've implemented these in your file and will send it back to you. Placed in F7: =IF(OR(E7="",E$4=""),"",INDEX(Sheet2!$B:$B,MATCH(E 7,Sheet2!$A:$A,0)) +E$4*5*INDEX(Sheet2!$C:$C,MATCH(E7,Sheet2!$A:$A,0) )) Copied F7, then with CTRL key held down, selected cells H7,J7,L7,N7,P7,R7,T7,V7,X7 Then right-click paste special formulas OK This pastes the same formula adjusted relatively where required into all the required cells. Then just copy the formulae down the respective columns. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#23
|
|||
|
|||
Points Ranking Formulas
You're welcome, Jim !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Cabo Wabo Jim" wrote in message ... Thanks Max, I think I'm starting to get it now. It takes awhile to understand how the formulas work. You did a great job on this! Cabo Wabo Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
giving points by a result (function) | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |