I'm creating a league table that does not need to be sorted.
I already have Overall, Home and Away standings but I also want to add a Last 5 games standing
Here is my spreadsheet:Soccer League Table.xls
The dates in the results tab
I have a spreadsheet that tracks the score of youth soccer games. For the team standings, the teams get the following points per game based on final score.....
+6 points for a win
+1 point for a shutout
+x points goal differential no greater than 3 (so a 3-0 win would be 10 total points, as would a 4-0 or 5-0 win)
-1 point for goal differential greater than 5 (6-0 would be 9 pts, 7-0 would be 8 pts, etc)
+3 points for a tie
+0 for a loss
I've tried CSE, ARRAY, SUMIFS, OR, IF and nothing works.
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.
Book1.xlsx
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.
is it possible to play dos games through excel? i want to somehow play my old championship manager games in excel whist at work (i don't actually want to work!!)
View 4 Replies View RelatedI have a list of nine teams from B13:B21 with Wins in column C, Losses in Column D, Ties in Column E. Pts are in Column F (all of these run row 13 down to 21). The team listed in B13 has a cell name of One in the Name Box, team in B14 has a cell name of Two, etc up to Nine.
The pts total for each team is also named, so F13 is called ptsone and has a current value of 8. F14 is called ptstwo and has a value of 10, etc.
I'm trying to do is in a different area of the spreadsheet put a formula that will compare the pts values and then list the team standings based on those points.
I am wondering if there is a way to automatically update my pick 3 lottery files by using excel. I use lottostrategies.com to obtain my results. If anyone knows how I might start to instruct excel to perform such a task, please respond. Or if I am going about this the wrong way in as far as thinking excel can perform such a task.
View 2 Replies View RelatedI am trying to create a golf table that shows how many games are played how many points are given for 1st 2nd 3rd and so on we give 10 pts for 9 for 2nd and so on......
View 12 Replies View RelatedIve got an example below of how i'm sorting fixtures and how to determine winners/losers and draws.
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1H1F2G2H2F3G3H3F4G4H4=
ABCDEFGH102/03/2007Team*A**Team*B000202/03/2007Team*C**Team*D000309/03/2007Team*A**Team*D000409/03/2007Team*B**TeamC000Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I'm struggling with is the ability to change dates and still be able to add the teams figures up.
When adding, for instance, Team A's wins I'm using
=f1+f3
and similar for defeats and draws.
But when I change dates, for instance game 3 to the 10th, Team A is still counting F1 and F3 yet its games now in row 1 and row 4.
How can i get it to change with the sorting.
Im sorting fixtures via the sort feature in data.
Back to test you with a few problems as I work through a project I'm carrying out, based on past results data of the English Premier League.
I have some of the solutions/formulas I require already, but there are still some gaps to be filled in!
***
the spreadsheet is set out as follows:
Key Columns / Sub-Title (Remarks)
B Date (the date a match was played - the s.sheet is sorted by this column, A>Z)
C Home Team
D Away Team
E Home team goals scored
F Away team goals scored
I-M Indicates via U or O whether the match contained under or over 0.5,1.5,2.5,3.5,4.5 goals
N Points obtained for Home team (i.e. 3 for a win, 1 for a draw, and 0 for a loss)
O Points obtained for Away team (i.e. 3 for a win, 1 for a draw, and 0 for a loss)
There are 381 rows in total for each worksheet (season) - 1 for the titles, and 2-381 for each match played in the season...................
I would like to make a soccer game in excel, there are two divisions of eight teams, and 77 non league teams. I want to be able to enter the teams and then randomdly do the fixtures and when that is done i would like to be able to enter the scores and on another worksheet have the table.
View 3 Replies View RelatedI currently have two columns one for each of the following formulas and then another column that's works on the result of the two together but what I want to know is how do I get the two combined?
=COUNTIFS('08-09 Scores'!$C:$C,$B6,'08-09 Scores'!$CB:$CB,">="&$AY$5,'08-09 Scores'!$CB:$CB,"<="&$AZ$5,'08-09 Scores'!$AT:$AT,">"&$BA$5)
=COUNTIFS('08-09 Scores'!$C:$C,$B6,'08-09 Scores'!$CB:$CB,">="&$AY$5,'08-09 Scores'!$CB:$CB,"<="&$AZ$5,'08-09 Scores'!$W:$W,"="&$F$5)
I've put the bits that I want to be "or" in red, The first bits are obviously the same and just the last bit needs to be or.
I am basically trying to find out how many games played by the selected team had the first goal scored after a certain time(the first formula)
or the first goal was scored by the away team(the second formula)
Create a macro for a soccer table.
image : http://i25.tinypic.com/1tk0na.jpg
sort all characteristics (Win,Draw,Lost and and so forth)
Formulas:
D2 =IF(B9>C9;1)+IF(B11>C11;1)+IF(C13
D3 =IF(C9>B9;1)+IF(C12>B12;1)+IF(C14>B14;1)
D4 =IF(C10>B10;1)+IF(C11>B11;1)+IF(B14>C14;1)
D5 =IF(B12>C12;1)+IF(B10>C10;1)+IF(C13>B13;1)
E2 =IF(B9=C9;1)+IF(B11=C11;1)+IF(C13=B13;1)
E3 =IF(C9=B9;1)+IF(C12=B12;1)+IF(C14=B14;1)
E4 =IF(C10=B10;1)+IF(C11=B11;1)+IF(B14=C14;1)................
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 want to make a cup draw, but i don't want to use RAND, I want to use VBA.
What i want is to make a English Football League Cup, so all 92 leagues teams are in it, but as follows
First Round 72 teams
Second Round 50 Teams
Third Round 32 Teams
Fourth Round 16 Teams
Quarter-Finals 8 Teams
Semi-Final 4 Teams
Final 2 Teams
for a team list see attached excel spreadsheet
If you are not sure what i mean google search English League Cup or try soccerway
I want a command box or similar for each round, i dont want it to change unless i click on the command button again.
i will be inputing the scores myself.
Sorry i forget one team in the First round "Darlington"
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?
View 14 Replies View RelatedMy 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.
View 4 Replies View RelatedIf the number in one cell is larger than the number in another cell then i would need a 2 added to yet another cell.
I the first two cells are equal then I would need to add a 1 to that 3rd cell.
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.
View 9 Replies View RelatedI 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.
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?
View 9 Replies View Relatedi 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.
View 9 Replies View RelatedI 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 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.