# Football Statistics From 2 Data Columns

Oct 6, 2006I have a simple spreadsheet recording games played on my football table.

Each match is the first to 10 goals. So each result is a simple: ...

I have a simple spreadsheet recording games played on my football table.

Each match is the first to 10 goals. So each result is a simple: ...

ADVERTISEMENT

I want to manipulate team statistics and having a bit of trouble trying to figure out how to do it. I am relatively new to excel and am using Excel 2007.

I have attached the excel file for your reference (the same file).

What I need to do is first find out the team number using the table on first sheet named "Teams". User will enter team name on call B4 of sheet "Team entry" then in cell C4 there should be a formula to find the team number (is beside name on sheet "Teams) and displays it (on C4). Then on cell D4 of the same sheet "Team entry" there should be a formula that displays cell A1 (shows a statistic for that team) of the team sheet. Each team has its own stat sheet named by its team number (easier for me to keep track). So basically D4 should show cell A1 of the sheet that shows stats of the team entered. In addition cell E4 of "Team entry" should display stat2 (of the sheet "stat2")for the team entered.

I have been looking everywhere for this. In SPSS, it is very easy to recode data, but how do you do this in Excel? For example, researchers often "reverse" their questions on surveys with Likert scales to eliminate respondent errors. In those questions, you want 1=5, 2=4, 3=3, 4=2, 5=1. Is there a plug-in or some other formula that is out there?

View 9 Replies View RelatedI have formed data arrays in VBA after running a time series simulation model. The array is m simulations x n periods. Small example: ...

View 6 Replies View RelatedI am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:

date

employee (who did the work)

team (for whom work was done)

department (for whom work was done)

type (of work done)

Project (which property worked on)

new/revision (was this a new project or revision of old)

time (spent working on this)

I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.

I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:

- This month (i.e., the 1st of the month to present)

- previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.)

- Year to date

I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?

I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:

Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.

1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).

2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.

3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.

4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment

5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.

I created a automated football league with 5 teams in it that updated the team positions

(moved them up and down)as the new data was added in the secondary table and i used this type of formula in the secondary table on a rank system to rank positions according to points/wins/goal/difference/ect this also places the appropriate teams in alphabetical order in case the teams have equal stats

=RANK(O4,$O$4:$O$13)+RANK(G4,$G$4:$G$13)/10+RANK(N4,$N$4:$N$13)/100+RANK(F4,$F$4:$F$13)/1000+RANK(E4,$E$4:$E$13)/10000+1/100000

and this formula in the primary table that updates its self

=SMALL($A$4:$A$13,1) in the ranking

and this type of formula to transfer the data to the appropriate destination from the secondary table to the self adjusting primary table

ie points/ goals/ect

=INDEX($A$4:$O$13,MATCH($A22,$A$4:$A$13,0),4)

my question is i now want to extend my table to 24 teams so can i still use this ranking system as the range of 10/10000 only seems to let me use 10 teams( not tested yet)not sure how it works

I am compiling odds for football matches using the last 16 match outcome data for each team. Basically I need a solution or formula that counts the number of home wins, away wins and draws for each team in the last 16. This would basically mean allowing for the latest match outcome added into and then eliminating the 17th match back so that formulas from the last 16 matches only are always calculated. I envisage having a the data in a row of of 16 columns for each eam.

View 9 Replies View Relatedi m just trying to create a football calculator, just a real basic one, my excel is ok. Just doin it for a mate as he is arranging some charity football event. i have inputted the teams and that and worked out goal differences but would like to calculate games played, wins losses draws and points, can someone advise or quicky have a look, see attachment.

View 6 Replies View RelatedI'm having trouble writing a forumla that will grab a second player from a list.

For example, there are 3 possible QB spots. I need a formula that will grab the 1st QB, the second QB if a 1st has already been selected and a 3rd if a 2nd has already been selected. This is what I have but it's not working right.

=IF(NOT(ISBLANK(B2))="True",IF(QB!E2:E71=Summary!A1,QB!B2:B71,"N/A"),"")

I have created a formula that works on football predictions for my office.

If correct score is predicted - 3 points

or

If correct outcome is predicted 1 point

And completely wrong the cell is to be left blank. I have managed to create this formula ;

=IF(COUNT('Match Results'!$D8,'Match Results'!$E8,Predictions!D8,Predictions!E8)=4,IF(AND('Match Results'!$D8=Predictions!D8,'Match Results'!$E8=Predictions!E8),3,IF('Match Results'!$D8<>'Match Results'!$E8,IF(SIGN('Match Results'!$D8-'Match Results'!$E8)=SIGN(Predictions!D8-Predictions!E8),1)))+0,"")

However the only outcome that will not work is if the match predicted is a 0-0 Draw and people who predicted say 1-1 should get 1 point.

I have attached the spreadsheet : Fixture Predictions Grant.xlsm

I do a football prediction competition at work and need help or the formula to calculate peoples scores.

The scoring works like this

10 points for a completely correct result and score for both teamse.g. 1-0 prediction and 1-0 result

7 points for a correct result but a correct score for only 1 teame.g. 1-0 prediction and 2-0 result

5 points for a correct result but no correct score for either teame.g. 1-0 prediction and 2-1 result

2 points for an incorrect result but a correct score for 1 teame.g. 1-0 prediction and 1-2 result

I have basic Excel knowledge but have no idea how to create a formula which will calculate the above and populate the correct scores for people in the spreadsheet.

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.

An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.

I would like results for my request of 50 matches to be displayed as follows.

HHH

HHD

HHA

HDH

HDD

HDA

[Code] ...........

Where:

H=HOME

D=DRAW

A=AWAY

Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.

A pal at work runs a football predictions spreadsheet but due to a recent surge in numbers, it is taking longer to adminster each week.

Basically, you get 3pts for a spot on home score, 4pts for a spot on draw, 5pts for a spot on away win and only 1pt if you get a correct result but not the actual scoreline, with no points being scored for being completely wrong.

At the moment, he manually inputs 0,1,3,4 or 5 pts each Monday, of course this is open to human error. Is there a way of getting excel to calculate this for him once he inputs the correct score in the first column?

Is it possible to calculate the current optimal fantasy football team from the attached data and constraints listed on the 'solver' tab?

View 2 Replies View Relatedhttp://www.excelforum.com/excel-gene...cognition.html

and im copying and pasting data from a website ( football scores )

and when i get what should be 1-1 it returns 01-jan and this i dont want

i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages

wondered if anyone could work out some syntax to use as a macro button?

claymation had a go but it doesnt work.

I'm creating an (English) football predictions competition for me and my family.

One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.

The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.

Been searching the archives but not finding my answers. I downloaded historical lottery numbers and want to run statistics on them- just no clue where to start. I guess the 1st and most important thing is to figure out which #s have come up most often out of all of them. I assume it's some sort of COUNT or LOOKUP code- but not sure. I have the #s arranged in a sheet like so ...

View 6 Replies View Relatedwant to make a macro to do a 'descriptive statistics data analysis' of column b and put the results in m1 to n18.

basically doing this but in a macro.

add in= tools /data analysis/ descriptive stats

I have recorded it doing this, but it for some reason can't do what it wrote.

On Sheet1 I have 2 cells one has the date 1-mar-09 and the other has 7-mar-09. I need to find this range on Sheet2 which has all the dates for the year in ColumnA and then total the amounts found in Sheet2!ColumnB for the specified date range. Is this possible without VB?

I'm trying to pull some statistics from a workbook I've been sent and am having some trouble working out how to achieve this.

I have a small interface that allows the user to choose a possible answer (drop-down menu) from a questionnaire (Yes, No, Maybe)(cell: C6). On the interface the user also picks the question they wish to see the stats for (cell: C5)

The data I have been sent has been set up with the questions along in row A, and the answers below in each column (the answers run across row A from column G - AH).

So, what I'm basically after is a formula that first looks up the question specified in C5 (I've used HLOOKUP to pull some other data), and then counts how many times the Yes, No or Maybe answers appears in the column where the question data is held.

I have two rows in a sheet with random numbers (1 till 90).

Those numbers in those two rows I change them every 5 minutes with new one still from 1 to 90

I need a "way", in one side of my sheet (lets say in column B) to count how many times a number was repeated after finish my work (example after 2 hour's)

Is that possible with excel 2007?

I have a table of contents page. The TOC is updated each time some one opens the sheet (this is a must have based on the requirements). I'm trying to include some statistics next to each item in the TOC. These statistics are on each worksheet and just need to be copied to the TOC.

Now my problem.

The worksheets are NOT keep in alphabetical order, so After the TOC is created a sort is done to put the TOC links in alphabetical order. After the order is set, I need a formula that will read the worksheet name from a cell on the TOC (which is really a hyper link to a worksheet).

I have this already:

ActiveCell.FormulaR1C1 = "=Address!R[-2]C[-2]"

If "=Address!" (Where address is the name of one of the sheets) could be replaced with a cell reference that (I think) would do the trick.

My TOC is a MACRO not a cell formula so if this can not be done with a cell formula but can be done through VBA that would be fine.

I have running total of bowling scores current & going back from when I first started.

What I’m trying to archive is a formula that will count the most recent 96 scores and still allow me to add new scores each week in keeping the current running total of 96 games.

The statistics are listed from A4:C215 that may have 1 or 2 scores not listed. (missed games)

Each week new statistics will be added in cell A4:C4 anywhere’s of 1-3 games.

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 try to predict some macro economic statistics but any attempt till now didn't make sense. the attached file. Note: when i used the FORECAST function the predicted values showed an unlogical drop while there seems to be a positive trend.

View 4 Replies View RelatedI am creating a workbook to manage golfer scores, teams, winnings, handicap, ect. I am having trouble with the statistics sheet. I Need A Sub To:

1. Column "B" , take the average of the lowest 5 numbers in columns "AB" though "AU"

2. Column "C" , take the average of the lowest 10 numbers in columns "G" through "Z" divided by 0.96

3. Column "D" , take the average of the lowest 10 numbers in columns "AW" through "BP"

this needs to happen for each row where there is a name. (names added daily)

I have included a similar sheet as the one i am working with along with some command buttons typically not included. Should show some of the problems I am having.

I need to record on Sheet 2 how many times enteries in Coloum B on Sheet 1 appear per date range.

View 3 Replies View Relatedi need to do the following on the attached spreadsheet using a macro: SHEET 1. This is an example of the original data i will be working with i need

1) You will notice that the Min and Max columns are not all together, i need these to all be together. I then only want the Race course, race time, date and type, Min, Max and ratings.

2) I then want the following formula added to Column F, Max minus Min. In column G i would like the following formula Rating minus Min. And finally in Column H Rating minus Max.

3) I would the like to Highlight in Yellow the horse with the highest possible number. I want this done for each column (F,G,H) in each race i would like all Horses that dont have a yellow cell in any of the Columns (F,G,H) to be deleted, leaving me with just the highlighted horses. An example of the required final result is contained in Sheet 2 of the attached spreadsheet.

Copyrights 2005-15 www.BigResource.com, All rights reserved