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 am making a spreadsheet for my fantasy baseball league and I have it set up how I want it, minus the correct formula(s) to make it work.
Basically, I have 15 different tabs in one .xlsx file. The first 14 are each team and their players. The 15th is a huge ranking list of all the players in the league basically.
What I want to happen is, as I enter a name in any of the first 14 tabs, somehow on the 15th the corresponding name with be crossed out, colored, etc.
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
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 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?
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.
I am trying to build up an excel file for the Portuguese Soccer League.
It has 18 teams.
Problem 1:
I already figured out a way for the untie criteria to be used during the regular season, that is, for the first 33 days (rounds) - criteria d) to f).
d) Biggest difference between Goals For and Goals Against on all the games they have played;
e) Most wins on all the games they have played;
f) Most Goals For on all the games they have played.
For the last round (34th round), the criteria are more complex. They include the first three criteria that concern the games played between the tied teams.
a) The most points won by the tied teams on the games played between each other;
b) The biggest difference between Goals For and Goals Against between the clubs that are tied on the games they have played between each other;
c) The most goals scored on the away games on the games the tied teams played between each other;
To summarize:
- For the day-to-day standings the criteria that applies is d) to f).
- For the final standingsthe criteria that applies is a) to f).
Problem 2:
I created a sheet named "Evolution" from which I want to extract streaks information: for example current streak, longest win streak, longest draw streak, longest loss streak, longest non-winning streak, longest non-losing streak if possible for home, away and global games.
Workbook 1 has 2 spreadsheets. Spreadsheet 1 contains Item and Pass/Fail Columns. under the item column is the serial number of the item tested. the Pass/fail column has the serial number duplicated if it failed tested. what is the formula is to have spreadsheet 2 pick the items from the pass/fail column on spreadsheet 1?
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
I have two spreadsheets, one gives me the beginning and end of civil twilight as a measure of day vs. night. The spreadsheet has Date/Time in the first column, and the value 45 in the 2nd column when it is night. The second spreadsheet has also 2 columns with date/time and body temperatures of a squirrel. I want to get basic statistics (mean and standard deviation) of the squirrel's nocturnal body temperature, that is for times when it is night (value 45). The tricky part is that Date/Time of both spreadsheets are different. The procedure has to recognize that the date/time of body temperature lies between the beginning and end of the value 45 blocks of the first spreadsheet.
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?
Often I need to add data from one spreadsheet to the appropriate places on another spreadsheet. For example:
Sheet A has 10,000 records with these fields: id#, name, address, place of employment.
Sheet B has 5,000 records these fields: id#, GPA, college major, type of degree.
Some of the records in B contain information for the same id#'s as sheet A. I want to add this information together so that a Sheet C will have these fields: id#, name, address, place of employment, GPA, college major, type of degree.
I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.
I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.
The information I need to transfer from Issues is: .....
I have 2 sheets in my excel spreadsheet. One tracks data for a number of projects five different employees are working on. The other sheet is where I want to total up the number of minutes each employee has worked on their individual projects. I tried writing an IF statement like below but I am only getting the total in the first field even if the employee's name is not Employee 1....
[Code] .....
How I can write this so their totals show up in the correct row?
I have a Main Customer Spreadsheet. I want to Auto Populate FROM the Main Customer Spreadsheet to a New Spreadsheet. I want to be able to key in a customer name on the New Spreadsheet and take the info for that customer from the MAIN Spreadsheet and fill in the blanks. I need to be able to do this several times a day.
It also renames the CommanBarPopop with the new filename.This allows the user to open both Projectworkbooks/files (If required) and load each CommandBarPopup for different filenames .Therefor opening the Userforms and worksheets for the CommandBarPopup clicked ...
Wondering if there is an easy way to compare 2 spreadsheets that should have identical data on them? The first spreadsheet (Before) has the output data from 'before' a code fix was applied. The second spreadsheet (After) has the output data from 'after' a code fix was applied. The spreadsheets have 7 columns of data and almost 500 rows.
I've already copied the data from the source datasets provided by my IT folks into Notepad (.txt) files and then used Excel to open them as fixed width spreadsheets. I have 1 workbook with 1 spreadsheet with 'before' data. And, I have 1 workbook with 1 spreadsheet 'after' data. And, I have another workbook that contains both worksheets. So, I'm ready to go whenever I get hints of what to do next. :-)
I need to be able to show my client that we did not impact the data with the code fix that was applied. I want to be able to show my client contacts (business folks) an end result via Excel that confirms that I actually compared the 2 sheets and there were no differences. In other words....I can't just show them a formula with '0' as it end result (even tho that's basically what I'm trying to prove).
I need to write a macro that will import data contained in another spreadsheet, but am unsure how to do this. I have several (about 15) spreadsheets that contain data. I need to import key bits of this data into one central spreadsheet that will be used for reporting purposes. I only need 2 cells worth (values) from each source spreadsheet, to be pasted into the destination spreadsheet, into designated cells.
The source spreadsheets are usually closed down and kept on a file server, which my PC has access to. Ideally I want to activate this macro with a control button - i.e. I press the button once and the macro goes off and collects/updates each field with the latest data stored in each of the source spreadsheets.
