Displaying Last X Results For Soccer League Table
Jul 1, 2014
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
View 12 Replies
ADVERTISEMENT
Aug 3, 2013
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
View 1 Replies
View Related
Aug 18, 2014
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.
View 6 Replies
View Related
Feb 5, 2008
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)................
View 9 Replies
View Related
Jun 30, 2006
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!
View 14 Replies
View Related
Mar 4, 2010
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
View 9 Replies
View Related
Jan 22, 2010
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.
View 4 Replies
View Related
Nov 15, 2006
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 Related
Sep 12, 2007
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.
View 3 Replies
View Related
Apr 5, 2008
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.
View 4 Replies
View Related
Jan 18, 2014
I am having trouble with some formulas displaying the results of the logical test as words.. For example.
=IF(E4>F4,100, IF(F4>E4, 200))
That works perfectly but I want "100" to be "Valid" and "200" to be "Invalid"
However...
=IF(E4>F4,Valid, IF(F4>E4, Invalid))
only results in the #NAME? error for me?
View 3 Replies
View Related
Dec 8, 2009
Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.
View 7 Replies
View Related
Dec 18, 2009
i put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
View 4 Replies
View Related
Oct 2, 2009
I have two worksheets in worksheet 1 I have a bunch of data and I want to be able to filter out to only include relevant results.
Eg. If I search for 'Dog' I want search column A in another sheet and find all full and partial matches then display the entire row of each of these matches.
View 6 Replies
View Related
Nov 11, 2011
What I am hoping to accomplish is have cells D3 & E3 display the numbers being cycled as it cycles through the loop statement. Current code is below.
Private Sub CommandButton1_Click()
Dim iRnd1 As Integer, iRnd2 As Integer, n As Variant, strJustice
Dim Counter 'tf
[Code].....
View 5 Replies
View Related
Jul 18, 2013
I have two groups of lottery results. The first group has the last year results of 200 rows, and the second group displays this year results of 100 rows. I want to start comparing the first row of the second group (green group) with all the rows of the first group( blue group)
View 12 Replies
View Related
Jun 23, 2014
I am trying to create a search where the user types into the text box 'ItemDescription' then hits the 'ItemDescSearch' button (see below code) to pollute the list box 'lbSamDesc' with any partial matches from the specified range. Currently when I click on the button it takes about 8 seconds then no results are displayed in the List Box.
View 9 Replies
View Related
Aug 22, 2007
I created a user form that provides a menu that allows users to perform advanced filtering. They can also scroll through the results freely. A problem is that a subsequent advanced filter selection does not always display the top row of filtered records, depending upon what the user has chosen to display prior to selecting the next set of filtered records.
How can I make the top row of filtered records always be displayed so that users will not overlook results of advanced filter operations?
View 9 Replies
View Related
Jan 3, 2013
using a command button to input data back into the spreadsheet at a specific location.
Background: I am building a time clock spreadsheet, of sorts. I have a user form that provides a list of volunteer names in a combobox and then a list of activities they can perform in another combobox. Some activities have Details. (So, VolunteerName Todd can choose Activity Maintenance, which has no details; Volunteer Joe can choose Activity Teaching and then can select Details Intermediate 2.) The volunteer then clicks a SignInCommandButton.
The SignInCommendButton populates a worksheet (VolunteerLog) with the following information:
Column A: Volunteer Name
Column B: Today's Date
Column H: Exact Time In
Column E: Activity
Column F: Details, if populated
The SignInCommandButton also copies a formula from cell L2 into the appropriate row of column C (Time In) that rounds the Exact Time In to the nearest 15 minutes. The SignInCommandButton then does a Copy/Paste Special Values to remove the formula from column C and then resets the Userform.
What I need to do:
1) When the person first selects their name from VolunteerNameComboBox, I want to check 3 things:
a) whether the person's name exists in Column A of the VolunteerLog worksheet;
b) if it does, if the Date associated with that entry = Today; and
c) if it does, if the Time Out column is Blank.
If all three conditions are met, I want the ActivityComboBox to populate with the values in Column E and F, if necessary. The user will then click the SignOutCommandButton. (details in a moment)
I already have the code for if the conditions aren't met (Activity box populates and, depending on the selection in the Activity box, the Details box may appear for a selection to be made, or may remain hidden.)
2) The SignOut Button needs to enter the Time into Column I of the appropriate row found in (1) above. It will also need to copy the rounding formula I mentioned earlier, and do the Paste Special, but I have that already.
For (1), I think I have a start. My thinking is to first check if the Name selected in VolunteerNameComboBox is in the VolunteerLog. Each time it shows up, I'd like to add it to a list (range?) called rngSignedInDate. Then I'd loop through all the entries in that range checking if the Date = Today. If it does, then I'd add it to a list called rngSignedOut and loop through those results to see if Column C (Time Out) is blank.
I think I have the first loop, but am stuck on how to populate the results into rngSignedInDate. Here is the code:
Code:
Private Sub VolunteerNameComboBox_Change()
'Check if the Volunteer has signed in already
Dim strVol As String
Dim rngSignedInVol As Range
Dim rngSignedInDate As Range
Dim rngSignedOut As Range
Dim rngSignedInActivity As Range
Dim rngSignedInMatch As Range
[code]....
(2) I haven't started working on the SignOutCommandButton. The challenge for me with that is directly tied to the challenge in (1). If someone does pass all 3 tests, I need to have that specific instance defined in a way that I can have the SignOutCommandButton put the time in the appropriate row.
View 4 Replies
View Related
Jul 4, 2006
I have a Template which is used to import log data, calculate and displaying the results in several charts. Then all sheets are copied to a new workbook, a Save As dialog apears for the new workbook and the template will close. This is all done with VBA and it works fine.
Problem:
The Worksheets with the Charts on it refer to the data-source at the template file.
Strange because the copied worksheets have the same names as the originals.
(when Opens: This Workbook contains Links to other Workbooks...)
Question:
How can I establish that the Charts will point to their own data sources in the same Workbook rather then linking to the Template file on a Server.
View 4 Replies
View Related
Aug 15, 2012
I have attached a work book example of what i am trying to do.
Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.
I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.
Is there anything i can do to change this?
View 3 Replies
View Related
Nov 28, 2013
I wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
View 9 Replies
View Related
Jun 25, 2012
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HYEYDH/14
222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HDGTEY/56
333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
Supplier P/N
Buyer P/N
YHDHFF/58
555555
I am using Windows 7 and Excel 2007.
View 2 Replies
View Related
May 26, 2014
check the table in the attachment.
I only want the top row / filters (A1 - H1) to be displayed on Table 1 but keep the functionality of it.
Is it somehow possible to hide all the information below A1-H1 or put it on another table and let Table 1 refer to it?
The table is mostly in German but it shouldn't matter.
View 2 Replies
View Related
Jun 6, 2014
I have a data in excel sheet in Table format, in the first column i have added a unique Prod_ID. In form there is a search text box by entering the Prod ID & click of search button i need to display the respective row along with the Headers.
View 1 Replies
View Related
Jul 24, 2008
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies
View Related
May 23, 2013
I have a workbook with two worksheets,
sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score
sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score
i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.
View 5 Replies
View Related
Feb 22, 2007
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies
View Related
Jan 7, 2014
I'm attaching a worksheet I'm having trouble with. Each entry has a total number of LINES (input by hand), a total amount of TIME (calculated by formula), a total number of CAPTIONS (input by hand), and a total amount of TIME2 (calculated by formula). I created a pivot table so I could see these four values by date, and also so I could see totals when I filter by other criteria such show names. But in the pivot table all the values come up as one-digit numbers. In the worksheet the LINES and CAPTIONS are usually 3-digit numbers, and the TIME and TIME2 are set up to display in H:MM format, but in the pivot table they don't appear that way at all.
The other option is to just use the filters in the table on the worksheet, but when I filter anything out the grand totals at the end of the table do not adjust, they continue to show the overall grand totals. Perhaps if I could get these totals to adjust whenever I use the filters, then I wouldn't need a pivot table.
View 3 Replies
View Related
Apr 5, 2013
I am looking to find the best method of pulling data from a Lookup Table I would like to set up a drop down with all the relevant Steel Sections we make it work and have it access the information and display just that information a separate face page. The information I have set up on the lookup and have named ranges fro them all But how do I get the drop down to trigger the process.
View 3 Replies
View Related
