SolvedPrivate

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?

Posted on 2015-01-25
12
9 Views
Last Modified: 2016-02-14
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
0
Comment
Question by:hermesalpha
  • 5
  • 5
12 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40569241
just before I inspect the file,
did you make the sub as Public
i.e Public Sub ?
0
 

Author Comment

by:hermesalpha
ID: 40569252
In the macro do you mean?
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40569281
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

Open in new window

any sub or function you need to access from macro should be public
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40569310
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
0
 

Author Comment

by:hermesalpha
ID: 40570386
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").
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40571079
attached a different implementation for requirements,  
it is VBA free, Dynamic and responsive for changes
comments included in the sheet,
you just copy and insert rows for additional sections and horses

have good luck,
and if your horses wins don't forget me :)
FarWestViktad-rankning-1.xlsx
0
 

Author Comment

by:hermesalpha
ID: 40585520
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
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40596593
you are welcome,
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
 

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.
I think there is a way to add hyprelink reference to certain cell in sheet, or in another sheet that contains explanations and comments
0
 

Author Comment

by:hermesalpha
ID: 40602949
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.
0
 

Author Comment

by:hermesalpha
ID: 40603040
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40655809
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now