I run a small town pool league and use excell to track player handicaps, ect. we use a simple system for handicapping. Players can be from 2- to 7+ (2- 2 2+ 3- 3 3+ 4- 4 4+) and so on. if a player is a 2+ and wins they go to a 3- and so on. I want to be able to just type a W or an L and have it look up the players previous handicap and return a new handicap in the next field. I've allready figured out how to count the W's and L's and return the total for a teams weekly wins on another page, and give stats on a players wins and losses. I then print each sheet out weekly. Teams have 8 players but only play 5 each week so some players might not play for 2,3 or more weeks. I also use access to keep track of all the people that have played in the league. some players play a few sessions then quit for a few. this way i can keep track of their handicaps when not playing and havew a list of all the people that have played in the league. Can i get excel to transfer a players handicap to access each time it changes?
This might be a little long/difficult to explain so I have attached the excel sheet for viewing.
I am trying to adapt cell I11, which now equals cell B10 or B12 (depending on where you type in the "x"). I am trying to formulate it to be either cell B10, B12, B18 or B22, I want it to read the highest seed number (5, 6 or & 7) again depending on where you place the "x". Let me know if I have left anything out.
I have Excel file A with a full list of IDs that were selected to take part in a study - about 5000 IDs with only one field "ID". I also have Excel file B with about 1500 ID of people from file A BUT who already took part in the study. Now what I need to do is filter out from file A all the people in file B so in file A I am only left with those who didn't take part.
How to set a formula to calculate distances (by using a function I provided) among all the observations (each one with a latitude and longitude value expressed in decimals) in an excel sheet. Here it is the previous thread.
I slightly modified the solution MarvinP suggested to me by using my own function to calculate distances among coordinates.
Attached you can find the excel with this. You will see that on the right side of the sheet, in yellow background, there is a matrix whose non-blank cells each one reporting a case of close coordinates (e.g.: 4-5 means that observations 4 and 5 are less than 15 Km apart one from another). Cells of the left-side matrix contains the function used to calculate distances among each observation with any other. Matrices are 116X116.
However, I am now jammed because I don't find an easy way to pool the observations found to be close each other (I have used a distance threshold of 15 Km - you can see it in the formula inside each cell of the "yellow" matrix").
The ideal way to establish new coordinates values for observations close one another would be by using coordinates which are at an intermediate position, however, this is not my priority, it would be OK also using one arbitrary value for all of them, i.e. picking up one pair of coordinates of one of the close observations and attribute this to the other. I have seen that, by excluding nonsensical similarities (e.g. 1-1, 2-2, etc.), there are groups of close observations of size 2 (e.g. 4-5) up to 10 (nine observations very close to a given one).
I need to use VBA read all historical data files, then, copy all the data from those files to Sheet[FX Historical Data] and Sheet[Position Data] respectively.
First, I copy a code from the text book, which allows me to copy one data files every time. Now, I have only 6 files need to copy, but if there are thousands of files? I try lots of codes but I still cannot find a way to solve my problem.
Second, can I use the function of "folder dialog open" to select the data files I want, and then copy the data inside to Sheet[FX Historical Data] and Sheet[Position Data]?
I have a football pool I am doing with my family. I would like a macro that displays a message box that tells me the leaders of the pool using the grand total number. So in my attachment, the message box would say something like:
Sue is in first place with 12 points, Bob and Dave are in second place with 9 points, Larry is in third place with 3 points
It doesn't need to be exactly like that, but you get the gist of what I am looking for. The catch here is that the grand total row changes each week as I add games in, so the row moves down every week. I need the macro to stay with the grand total row from week to week.
My friend and I have created a Wallyball league (volleyball played on a squash court). Every week we have more and more players and making the teams is more and more complicated and time consuming. I created this Excel file to try and generate random teams faster.
The file consist of :Jersey no. and player (A and B)rating if playing (C)rating (D)playing on a given day 0 or 1 (E)wallyball courts to generate random teams with a team rating and differential rating.
The only thing it needs is a non-repeating function (as of now, one player can be selected many times)
Also, the players should not be selected if they are not playing on a given day (column E = 0)
I have to do this WITHOUT using macro (1. I'm not familiar with macros ; 2. I have a free starter version of Excel, I don't think macros are enabled).
I am trying to create and calculate player statistics using Vlookup in Excel. This is what I am trying to do:
BCD 5PlayerGoalsAssists 6Jordan Eberle00=VLOOKUP(TEXT(B6,"@"),B13:G14,4) used in C6 7James Neal 00=VLOOKUP(B7,B13:G14,4) used in C7
cell format is "general" in B6 thru B14
Lookup TableCol ECol FCol G Player NameTeamGPGAPts 13James NealPITT4325 14Jordan EberleEDM1404
Imported Data from Yahoo Sports and have sorted in ascending order a-b-c-d
(pasted as text and than changed to General in Excel formatting)
I have tried using 2 different vlookup formulas structures, but no success. I have tried using numeric reference (i.e using numeric 1 in for both in the lookup table and the original cell) and that works.
I'm having some trouble with some VBA code written for Excel (Office 2003). I have a video file (in .wmv format) and a list of time stamps in an excel file. When I click a timestamp, I want to pop open the WMV and play it at that given time.
I've made some headway thanks to a few others, but I'm getting this "Run-time '424': Object required" error that I can't seem to fix. I've attached the file, and it occurs at the line "frmPlayer.Show" and I have no idea why!
We have an archery league that is getting very popular, and very time consuming. We have a spreadsheet now that works, but so much of it isn't annomated that it's not saving a bunch of time for us. It is, however, very accurate, so we keep using it, but I think there is a better way out there to do it, and I bet one you excel pro's know the answer.
Here is how it works:
We have three man teams. Let's say we have 20 teams again this year (we did last year). These teams will have their own unique schedules of other teams that they will play against. We have ten weeks of head to head team matches.
Every week, every shooter's average is computed. So, week one their average is whatever they shot. Week two is the average of week 1 and week 2 combined, and so on. This revolving average keeps shooters from sand bagging.
The team head to head match ups are handicapped. Let's say that team 1 is up again team 2 in week 1:
Team 1 is consisted of bill, bob, and barry. Bill's average at this time is 300, bob's is 290, and barry's is 295.
Team 2 is Larry, Leonard, and Louie. Their averages at this point are 290, 280, 300.
The head to head team match up is consisted of three individual head to head's. The shooters with the best averages, at this point in time, face off, the middle averages face off, then the lowest average shooters on each team also face off. You get 2 points for winning your head to head match. You get 1 point for tieing. Zero points for losing.
The handicaps kick in like this. Bill is the high average shooter on Team 1. He faces Louie since Louie is high man on Team 2. Since their averages are both the same, this match is a head's up, scratch, match with neither shooter getting spotted points.
The middle shooters are Barry with his 295 against Larry with his 290. We use a 80% handicap. So, Larry gets "spotted" 80% of 5 points, or 4 points. The same goes for the matchup of Bob against Leonard. Leonard gets 8 points in his match (80% of the ten points that his average is lower).
Shooters shoot their games, scores are taken, points are spotted points are added in, then winners are declared. Then, the points each shooter wins is added into a team total for that week, then on to the next week's match.
My biggest problem is that the #1, #2, and #3 shooter on each team, each week, changes sometimes. In my previous example, lets say that in week two, lets say louie blows it and shoots a 270. So now his average isn't the highest on his team, so the pecking order of the team changes.
So, my challenge to this board, if you wish to accept it, is how do I set this bad boy up? Lots of issues here. The biggest one is how to get the teams to self rank themselves each week?
I'd love to have a tab where I enter each shooter's score in, and excel takes it from there giving points to winners of adjusted matches.
I'm after a league table spreadsheet that automatically sorts after entering results but I also want a spreadsheet that doesn't use macros (pcs at work prevent the use of macros - I understand why but its a pain still!).
I know this is possible from looking at this excellent freeware world cup 2006 spreadsheet: [url]
Ideally I'd like to adapt this spreadsheet for my needs but having unhid all of the worksheets I'm blown away by just how complicated it is: For each world cup group the worksheet is 9 PAGES LONG of what seems like duplicated tables!
I started back in august doing the fixtures of my local rugby league league. I have created a league table using excel but i have been having to enter each teams points in manually adding and subtracting the points differences. I was hoping that you would be able to enlighten me in how I could just enter the results and the league table would automatically update.
I dont know if I can upload the file to show you if you can do this please let me know
I have a sample spreadsheet (uploaded to this thread) in which I have 10 command buttons named 'Video 1' to 'Video 10'. Next to these buttons is a Windows Media Player Object. I require the code that upon clicking any of the video command buttons, the relevant video is opened and shown in the windows media object within the same sheet (sheet1).
I'm sure the code for each button will be the same apart from the cmd button reference number/name and the link to the file to be played.
If we assume all the videos are called as per their buttons i.e. Video 1.wmv, Video 2.wmv ... Video 10.wmv; and the location of these files is under 'C:Films', can someone post up some code for button 'Video 1' making it clear which variables to change for linking to the different files.
Just starting to use excel and have a problem I hope someone can solve. Trying to set up handicap system for golf league. Column A is players. Column B shows average of last 5 rounds played and rounded to nearest whole number. column C shows the same without rounding. column D and beyond show scores with column D being the most recent. Have this all this working well with one problem. Each week I insert new column D, enter scores, and everything is recalculated. How do I deal with absent players? Is there a way to enter perhaps "A" for absent and excel will know to count the last 5 cells with numerical values only?
My league table just stopped adding the scores up as of week 22. prior to that they worked fine. I input scores in the "Our Players" sheet, per player per week.... simple. but like i said, as of Week 22, it just inputs that specific weeks scores. See attached file.
I have a football pontoon league that works on goals scored, what I am tring to do is to be able to auto-sort the league on the right of the document when I amend the numbers on the table on the left of the document.
I have collate the results from 3 companies all whom sell a series of products. Each day they submit there sales and productivity results to me and I collate them to a weekly return that I then manually rank each area in a league table to show 1st, 2nd, 3rd....
I'd love to be able to automate this so that the league table page updates and sorts top to bottom automatically from the data entered on the other pages, but don't know how. I think macro's are the answer but I have no idea how to make a macro perform the actions I need.
This is for a call centre and sales office. there are 3 tabs. Tab 1 represents the values that we update at the end of each week which then gives a running total over an 8 week period and then an average below. there is a seperate table for each staff member. Tab two is for one team of people and tab two is for the other team. Namely LG team and Sales Team. the tab 2 and 3 are to show league tables that both collect data from tab 1, but then create a league table showing who is top dog over the 8 week period. But this also has to self adjust to be in order.
on tab 2 and 3, the tables on the left will be hidden once complete, but are there to work out the league. the table on the right is then there to put and show the league in the right order. I HOPE THAT MAKES SENSE, COS EVEN I'M LOST NOW. I've followed previous advice on this and inputted what I think are the right formulas, but for some reason it's coming up with the wrong answers and several names are being repeated which are wrong. For example even when ben perhaps is number 2, it will show rahim as number 2 and number 3. why is this?
also, how do I get by the problem of on the odd occassion when perhaps ben and rahim have the same stats and are joint second, but it will only show ben as 2 and ben as 2 again, leaving rahim out of it. WOW. I'm glad I've got that out of my system. I was doing the washing up yesterday donig Excel formulas in my head. But the washing up got done at least.
For a soccer league table I'm building, I'm trying to add a few columns that display each team's form in the last few matches. So, it will state 'W', 'D' or 'L' for a certain number of recent matches. Like this for each team in the league table: WWDLW.
In the attachment example, I've pretty much got the concept working, having used the invaluable INDEX/MATCH/LARGE functions and a serial date column. The problem is, at the moment the DDW you see at the bottom is taking the whole table into consideration. I'm trying to get it so only Arsenal's recent results are taken into account. I feel like this shouldn't be too difficult, but after numerous attempts of using different functions I cannot get it to work.
i am trying to make a spreadsheet up which will calculate and sort out teams in a league table for my local football team. basically, i have around 6 teams in the league and they need to be sorted via: points, then goal difference, then goals scored and then by the match between the two teams if still equal. I have managed to work out how to get excel to calculate all of the numbers of wins and losses and goals scored, etc but cannot find how to rank the teams in order regarding how i just explained.
I am trying to update a league table from a different sheet. The table is not fixed on how many people with be in it as this can grow. This what I have in mind to do what I need but can work out how to implement it.
I need to loop though column "A" in sheet "U8" range A2:A?
The value in column "A" is unique so from "A2" I then take the value in "K2" for this unique id.
I now need to search column "A" in sheet "U8 League Table" for the unique ID place the value of "U8 - K2" into column "D" (Round2) row will be different in most cases to round1.
If the unique ID is not found in "U8 League Table" then add it.
The U8 sheet is populated a fresh per event (fortnightly) and the data fed (riders score will always be in column K, different row) into the League Table. However. as the rounds mount the data will need to go into next column along in the Table i.e Rd1 in C, Rd2 in D, Rd3 in E etc.