hermesalpha
asked on
How can I copy this formula so I can use it for 250 sections (each section 3 rows high) in Excel and how can I "transport" the awarded points to a specific cell and then add 3 more functions?
First I wonder how I can copy this formula so I can use it for 250 sections (each section 3 rows high) in Excel?
I have uploaded the Excel here, which should be used for handicapping harness racing horses. The tab "Rankning" (handicapping in English) has the macros in it, the other is empty on macros and contains only the overall layout I wish to have. (The button "Återställ" means "Reset" in English). There are 2 tabs now, but I would like only my layout on tab 2 kept and the macros from tab 1 implemented on it (and then delete tab 1). I tried to view the macros according to these instructions:
http://www.ehow.com/how_5964828_macros-excel.html
But it did not work, the macro window that pops up is completely empty.
The function performed by the macros is this:
By selecting a cell in the upper row in each of the total 6 sections, that cell gets high-lighted in yellow colour and the point in the cell immediately below the cell in the upper row gets summated in cell C27 ("Total poäng": total points in tab "Rankning", the first tab). So finally, after having gone through the 6 sections each horse gets a total point awarded.
There are four additions I'd like to this form:
1. Instead of only these 6 sections I'd like 250 sections (each section with heading row and upper and lower row, lower row contains the point that should be summated).
2. I want to go through these 250 sections for horse 1, after having done that there should be a button for "transporting" those points to another cell designated for horse 1 in a table for all the horses in the race (see second tab: "Layout for Rankning: row 53, cell c53 for horse 1). Then I go on and complete the procedure for horse 2 etc. until a maximum of 15 horses.
3. In the table for all the horses in the race (cell c52 to q54), I'd like to be able to manually adjust these automatically awarded points by means of a up and down button for each horse in increments of 1 tenths decimal place. So if horse 1 has been awarded a total of 95 points, I might want to manually change this to 95.3 points. The awarded points are in cell C53 to Q53, C53 is for horse 1, D53 for horse 2 etc. So here the up and down adjuster should be placed.
4. Finally, there should be a button for arranging the maximum 15 horses in order according to these points (after they have been manually adjusted in some cases) on row 53, so that the horse with most points is ranked as number 1 etc. This should be performed on row 54 ("Viktad rankning": handicapping in English). So that the horse with most points (for example horse number 12) should be in cell C54, the horse with second most points (for example horse number 6) should be in cell D54, etc.
5. Also, there should be a check box for each of the 250 sections so that I can choose to tick this check box to inactivate particular sections (these sections would then be greyed out and not possible to select any cells in those sections).
Can I have these operations performed in Excel only with Excel commands, or for which of above 5 functions do I need to use macros?
Some translation of words in the form:
Poäng: points
Häst nr: horse no.
Totalpoäng: total number of points
Viktad rankning: horses ranked (handicapped) in order
Återställ: reset
From-EE-20140710-Viktad-rankning.xlsm
I have uploaded the Excel here, which should be used for handicapping harness racing horses. The tab "Rankning" (handicapping in English) has the macros in it, the other is empty on macros and contains only the overall layout I wish to have. (The button "Återställ" means "Reset" in English). There are 2 tabs now, but I would like only my layout on tab 2 kept and the macros from tab 1 implemented on it (and then delete tab 1). I tried to view the macros according to these instructions:
http://www.ehow.com/how_5964828_macros-excel.html
But it did not work, the macro window that pops up is completely empty.
The function performed by the macros is this:
By selecting a cell in the upper row in each of the total 6 sections, that cell gets high-lighted in yellow colour and the point in the cell immediately below the cell in the upper row gets summated in cell C27 ("Total poäng": total points in tab "Rankning", the first tab). So finally, after having gone through the 6 sections each horse gets a total point awarded.
There are four additions I'd like to this form:
1. Instead of only these 6 sections I'd like 250 sections (each section with heading row and upper and lower row, lower row contains the point that should be summated).
2. I want to go through these 250 sections for horse 1, after having done that there should be a button for "transporting" those points to another cell designated for horse 1 in a table for all the horses in the race (see second tab: "Layout for Rankning: row 53, cell c53 for horse 1). Then I go on and complete the procedure for horse 2 etc. until a maximum of 15 horses.
3. In the table for all the horses in the race (cell c52 to q54), I'd like to be able to manually adjust these automatically awarded points by means of a up and down button for each horse in increments of 1 tenths decimal place. So if horse 1 has been awarded a total of 95 points, I might want to manually change this to 95.3 points. The awarded points are in cell C53 to Q53, C53 is for horse 1, D53 for horse 2 etc. So here the up and down adjuster should be placed.
4. Finally, there should be a button for arranging the maximum 15 horses in order according to these points (after they have been manually adjusted in some cases) on row 53, so that the horse with most points is ranked as number 1 etc. This should be performed on row 54 ("Viktad rankning": handicapping in English). So that the horse with most points (for example horse number 12) should be in cell C54, the horse with second most points (for example horse number 6) should be in cell D54, etc.
5. Also, there should be a check box for each of the 250 sections so that I can choose to tick this check box to inactivate particular sections (these sections would then be greyed out and not possible to select any cells in those sections).
Can I have these operations performed in Excel only with Excel commands, or for which of above 5 functions do I need to use macros?
Some translation of words in the form:
Poäng: points
Häst nr: horse no.
Totalpoäng: total number of points
Viktad rankning: horses ranked (handicapped) in order
Återställ: reset
From-EE-20140710-Viktad-rankning.xlsm
ASKER
In the macro do you mean?
yes
Public Sub Reset_Values()
With Range("B4,B8,B12,B16,B20,B24")
.Value = ""
End With
With Range("C4:X4, C8:Q8, C12:N12, C16:M16, C20:L20, C24:K24").Interior
.Pattern = xlNone
End With
End Sub
any sub or function you need to access from macro should be public
I almost understand what you need, everything can be done
but, to make it business case speaking,
1- you have horses that you want to make points and ranking for them based on races achievement,
2- Maximum number of horses will be 15
3- Each horse will have up to xx Races
4- Each race will have 6 monitored values
5- each value will be translated to points based on index table you have (if prizes are 40K then 29 points is awarded, if rank in the race is 2:a then 25 points awarded .. etc
6- you will have a total for every horse for all points awarded, and can be adjusted in 1/10 up and down
7- you want a list of all horses have ranked by total points of each race descending
is this correct, please modify if necessary and replace value for xx
Regards
but I recommend that instead putting
but, to make it business case speaking,
1- you have horses that you want to make points and ranking for them based on races achievement,
2- Maximum number of horses will be 15
3- Each horse will have up to xx Races
4- Each race will have 6 monitored values
5- each value will be translated to points based on index table you have (if prizes are 40K then 29 points is awarded, if rank in the race is 2:a then 25 points awarded .. etc
6- you will have a total for every horse for all points awarded, and can be adjusted in 1/10 up and down
7- you want a list of all horses have ranked by total points of each race descending
is this correct, please modify if necessary and replace value for xx
Regards
but I recommend that instead putting
ASKER
1. Yes, but not only on previous achievements: I want to handicap the horses based on previous achievements in harness races plus current conditions (which post/starting position they have in the current race, driver's winning percentage, etc.). But the important is that these handicapping factors are in these 250 sections and I give some of these handicapping factors more weight than others (more points awarded for some handicapping factors).
I also found out now that I'd like to relate some of the different handicapping factors with others within these 250 sections. For example, the handicapping factor no. 1 could be "The 3 best achievements during the 5 past races" where "1-1-1" denotes a horse that won 3 times during the past 5 races so that horse is awarded 30 points. However, this horse might very well have taken those victories in races of low class (met with horses that has earned much less prize money), so these achievements should then be weighted against handicapping factor no. 50 for example ("Has the horse in 3 or more of the past 5 races been in races with low claiming level for this horse's total earned prize money"). If the answer is "Yes" it means this horse has won over horses with much lower class so these victories should be awarded less weight than the full 30 points (let's say only give 18 points).
2. Yes, maximum 15 horses in a race (sometimes 12, 11, 13, 9, 6 etc.).
3. It's not so relevant: some handicapping factors relate to past races, some don't. These 250 handicapping factors main purpose is to rank all of the horses (handicap them) in order of who is most likely to win this race.
4. I'd like each race to have 250 monitored values FOR EACH HORSE, so total is 250 monitored values times maximum 15 horses.
5. Yes, each value will be translated to points based on an index table. But see also the second paragraph for 1 above (that each handicapping factor might be in need of weighing this index table a little based on correlations with other handicapping factors such as victories taken in low class races versus the horse's total earned prize money in those low class races in which case these victories should be awarded less than the total 30 points).
6. Yes, I'd like a total for each horse automatically calculated based on the awarded points, but this total should finally be possible to adjust manually in tenths (1/10) by means of up and down arrow in the cell containing the total awarded points.
7. Yes, I'd like a list in order of which horse has most points (after manual adjustment) and this horse should be first in the list, this horse has the best chance to win this race. This horizontal list (from left to right) is on row 54 ("Viktad rankning").
I also found out now that I'd like to relate some of the different handicapping factors with others within these 250 sections. For example, the handicapping factor no. 1 could be "The 3 best achievements during the 5 past races" where "1-1-1" denotes a horse that won 3 times during the past 5 races so that horse is awarded 30 points. However, this horse might very well have taken those victories in races of low class (met with horses that has earned much less prize money), so these achievements should then be weighted against handicapping factor no. 50 for example ("Has the horse in 3 or more of the past 5 races been in races with low claiming level for this horse's total earned prize money"). If the answer is "Yes" it means this horse has won over horses with much lower class so these victories should be awarded less weight than the full 30 points (let's say only give 18 points).
2. Yes, maximum 15 horses in a race (sometimes 12, 11, 13, 9, 6 etc.).
3. It's not so relevant: some handicapping factors relate to past races, some don't. These 250 handicapping factors main purpose is to rank all of the horses (handicap them) in order of who is most likely to win this race.
4. I'd like each race to have 250 monitored values FOR EACH HORSE, so total is 250 monitored values times maximum 15 horses.
5. Yes, each value will be translated to points based on an index table. But see also the second paragraph for 1 above (that each handicapping factor might be in need of weighing this index table a little based on correlations with other handicapping factors such as victories taken in low class races versus the horse's total earned prize money in those low class races in which case these victories should be awarded less than the total 30 points).
6. Yes, I'd like a total for each horse automatically calculated based on the awarded points, but this total should finally be possible to adjust manually in tenths (1/10) by means of up and down arrow in the cell containing the total awarded points.
7. Yes, I'd like a list in order of which horse has most points (after manual adjustment) and this horse should be first in the list, this horse has the best chance to win this race. This horizontal list (from left to right) is on row 54 ("Viktad rankning").
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had a look now and this is just genial! Thanks a lot for this solution FarWest, precisely what I had in mind. Some questions after my first look:
1. I get this name error in the table in the first tab (where the horses should be ranked in order of best chance of winning the race). Shouldn't "horse 1" and "horse 2" be displayed here (in ranking order)? Also connected with this is a reference error in the second tab. I have attached screendumps here showing how it looks in my Excel.
2. Can I copy this main table on the first tab (A1..N9) sidewards so I get it for all 15 horses?
3. Because I will have around 250 sections it would be best to move the table "Rankning Results" in the first tab to cell A1..B16 (and consequently the main table would need to be moved 2 columns to the right in the first tab). Can I do that now, will the references to the second tab "Rank" still work?
4. How do I edit values and add or remove values from the drop-down menu in column "Value" in the first tab? I suppose the values in these drop-down menues reference to the different tables in tab 3 "RankningC", so that I would need to add the values here also when I add all my 250 sections. But I need to create new drop-down menues for each section, won't I? Or will they be copied when I copy from this initial table with 6 sections? So I only need to edit the drop-down menues?
5. The column "Correction" in the main table in the first tab: This is completely manual, right? The table doesn't retrieve value according to any formula? Whereas for the column "Points" retrieves the value from a table in the third tab "RankningC".
6. Can I insert a new column between column A and B in the first tab without ruin your formulas and references? Because I would like to label this new column "Main type of section" so as to subdivide the 250 different sub-sections. I then could sort the column "Section name" according to "Main type of section" in order to get all "Section name" that belong to the same "Main type of section" sorted after each other.
7. How I can I add internal links between several different sections in the main table in the first tab and also comments? So that if there is some type of correlation between Section name 5 and Section name 198 and Section name 236, I can connect these sections for my own reference and add a comment/table what this connection is about. Just like in Adobe Acrobat Pro.
Name-error-in-first-tab.png
Reference-error-in-second-tab.png
1. I get this name error in the table in the first tab (where the horses should be ranked in order of best chance of winning the race). Shouldn't "horse 1" and "horse 2" be displayed here (in ranking order)? Also connected with this is a reference error in the second tab. I have attached screendumps here showing how it looks in my Excel.
2. Can I copy this main table on the first tab (A1..N9) sidewards so I get it for all 15 horses?
3. Because I will have around 250 sections it would be best to move the table "Rankning Results" in the first tab to cell A1..B16 (and consequently the main table would need to be moved 2 columns to the right in the first tab). Can I do that now, will the references to the second tab "Rank" still work?
4. How do I edit values and add or remove values from the drop-down menu in column "Value" in the first tab? I suppose the values in these drop-down menues reference to the different tables in tab 3 "RankningC", so that I would need to add the values here also when I add all my 250 sections. But I need to create new drop-down menues for each section, won't I? Or will they be copied when I copy from this initial table with 6 sections? So I only need to edit the drop-down menues?
5. The column "Correction" in the main table in the first tab: This is completely manual, right? The table doesn't retrieve value according to any formula? Whereas for the column "Points" retrieves the value from a table in the third tab "RankningC".
6. Can I insert a new column between column A and B in the first tab without ruin your formulas and references? Because I would like to label this new column "Main type of section" so as to subdivide the 250 different sub-sections. I then could sort the column "Section name" according to "Main type of section" in order to get all "Section name" that belong to the same "Main type of section" sorted after each other.
7. How I can I add internal links between several different sections in the main table in the first tab and also comments? So that if there is some type of correlation between Section name 5 and Section name 198 and Section name 236, I can connect these sections for my own reference and add a comment/table what this connection is about. Just like in Adobe Acrobat Pro.
Name-error-in-first-tab.png
Reference-error-in-second-tab.png
you are welcome,
please note due to excel automatic reference update take a copy from first line in RANK sheet and paste it for how many lines that will be in results sheet or more after each update that contains inserting new rows
1. I get this name error in the table in the first tab (where the horses should be ranked in order of best chance of winning the race). Shouldn't "horse 1" and "horse 2" be displayed here (in ranking order)? Also connected with this is a reference error in the second tab. I have attached screendumps here showing how it looks in my Excel.The file I had submitted was ranking ok, is this result from the file I gave to you before any change?
please note due to excel automatic reference update take a copy from first line in RANK sheet and paste it for how many lines that will be in results sheet or more after each update that contains inserting new rows
2. Can I copy this main table on the first tab (A1..N9) sidewards so I get it for all 15 horses?sure this is the purpose of the whole Idea, when building the sheet I only use copy for horse 2 and validate formulas that will work for copying
3. Because I will have around 250 sections it would be best to move the table "Rankning Results" in the first tab to cell A1..B16 (and consequently the main table would need to be moved 2 columns to the right in the first tab). Can I do that now, will the references to the second tab "Rank" still work?no it has to be changed , just give me some time to update the sheet
4. How do I edit values and add or remove values from the drop-down menu in column "Value" in the first tab? I suppose the values in these drop-down menues reference to the different tables in tab 3 "RankningC", so that I would need to add the values here also when I add all my 250 sections. But I need to create new drop-down menues for each section, won't I? Or will they be copied when I copy from this initial table with 6 sections? So I only need to edit the drop-down menues?The drop down lists are built or referenced automatically using RankningC data, just follow the way sections are defined in the RankningC sheet to add all the 250 sections or so
5. The column "Correction" in the main table in the first tab: This is completely manual, right? The table doesn't retrieve value according to any formula? Whereas for the column "Points" retrieves the value from a table in the third tab "RankningC".yes the correction values is fixed value range
6. Can I insert a new column between column A and B in the first tab without ruin your formulas and references? Because I would like to label this new column "Main type of section" so as to subdivide the 250 different sub-sections. I then could sort the column "Section name" according to "Main type of section" in order to get all "Section name" that belong to the same "Main type of section" sorted after each other..I will think of a way to make placement of rows and columns dynamic to enable position change
I think there is a way to add hyprelink reference to certain cell in sheet, or in another sheet that contains explanations and comments
7. How I can I add internal links between several different sections in the main table in the first tab and also comments? So that if there is some type of correlation between Section name 5 and Section name 198 and Section name 236, I can connect these sections for my own reference and add a comment/table what this connection is about. Just like in Adobe Acrobat Pro.
ASKER
It should be very useful also if there are two "RANKING RESULTS" (the table on the first tab, in cell A14..B29): one before correction and another "RANKING RESULTS" for after correction.
ASKER
I will have these 6 main types of section (and each of the 250 sub-sections should be sorted under one of these main types of section):
1. Final true speed
2. Current fitness level
3. Class
4. Trip
5. Trainer
6. Driver
1. Final true speed
2. Current fitness level
3. Class
4. Trip
5. Trainer
6. Driver
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
did you make the sub as Public
i.e Public Sub ?