Formula For Football Predictions Spreadsheet?
Aug 1, 2014
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
View 3 Replies
ADVERTISEMENT
Feb 12, 2009
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?
View 9 Replies
View Related
Aug 7, 2009
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.
View 10 Replies
View Related
Aug 2, 2009
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.
View 11 Replies
View Related
Jan 23, 2012
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?
View 4 Replies
View Related
Jan 4, 2010
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.
View 4 Replies
View Related
Jul 10, 2008
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
View 10 Replies
View Related
Jul 5, 2009
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 Related
Jan 15, 2007
i 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 Related
Aug 8, 2014
I'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"),"")
View 4 Replies
View Related
Oct 6, 2006
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: ...
View 7 Replies
View Related
Dec 6, 2009
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.
View 4 Replies
View Related
Dec 20, 2012
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.
View 9 Replies
View Related
Aug 30, 2013
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 Related
Jan 11, 2009
http://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.
View 14 Replies
View Related
Jun 1, 2006
I recently added a formula to a spreadsheet with som complicated formula. It worked ok and I saved the sheet. Now it takes 5 minutes to open the sheet and when I try to do anything,like delete the inserted column the program locks up giving a no responding message. I can do without this column if I have to.
View 4 Replies
View Related
Jan 17, 2014
I have a set of spreadsheets (Circa 50) that each have around 50 tabs of information on, one set of spreadsheets are from 2012 and another are from 2013.... basically i need to write a formula that goes in to every tab in each spread sheet and basically tells me the entries that are in the 2012 sheets but do not appear in the 2013 sheets. The results of this are the most important thing. All of the names that i need to search are in Column A of each spreadsheet and tab .
View 3 Replies
View Related
Aug 16, 2012
What I am trying to to is if I enter a number between 6-14 the next cell puts in the dollar amount 650.00 if I enter 15-29 it puts 605.00 if I put 30-49 it puts 575.00 if I enter 50-100 it puts 525 and if I enter a number over 100 it puts in 480.00
View 3 Replies
View Related
Apr 23, 2008
I have a rather odd question, is there a way to count how many formula's are being used on a spreadsheet?
View 9 Replies
View Related
Jan 20, 2009
I am creating a spreadsheet and have run into a brick wall. Row 1 contains a list of dates Mon-Fri as far out as I could go. Rows 4, 5, 6, and 7 contain a code. Using those, I want to figure out an if statement that will place a 1 in the cell if the date in row 1 falls between a start and finish date. The data sheet looks like this:
A2- Code
K2- Start Date
L2- End Date
This is the formula I tried, but it does not work completely for me:
=IF(AND(K$1>=Data!$K$2:$K$1000,K$1
View 9 Replies
View Related
Jun 25, 2009
I am trying to set up a cell (E10 on my attached spreadsheet) to act as a key that would collect all of the PO#'s from a seperate column (cells A12:A27 on the attached sheet) and wouldn't repeat any of the repeat PO#'s. I am trying to do this because I currently save each order form by the PO #('s) and at times I find myself typing a long string of PO#'s for the book name. It would be much easier if this key cell would reflect all of the PO's for me and I could just copy and paste the contents into the save as box. This brings up another problem- when I try to copy and paste the contents of the key cell I have now, I just get the formula, not what the formula returned.
View 10 Replies
View Related
Nov 5, 2009
I'm trying to set up an inventory spreadsheet for my work, but i'm running into a snag.
What I want to do is have three columns that interact with eachother.
1. Units in stock
2. Reorder Level
3. Reorder Tolerance
4. Status
What I want, is to have the status column react in one of three ways. Either "In Stock", "Reorder Needed" and "Out of Stock".
So if units in stock is at 110, with a reorder level of 100, and a tolerance of 10%, I want the Status to show "Reorder Needed" with a red cell color. But if stock is at 111 or more, I want it to be shown as "In Stock". Zero should be "Out of Stock".
View 6 Replies
View Related
Jan 5, 2012
Currently I am wondering if this is possible. I have a formula like,
=Total!O8+'Less-1.7'!O8+'Overall'!O8
In total, I have three spreadsheets by the name of that. Then I added a new spreadsheet called DummyTest. I wonder if I can make it so that this DummyTest spreadsheet will automatically go into the formula,
=Total!O8+'Less-1.7'!O8+'Overall'!O8+DummyTest!O8
My intention is to use the INDIRECT formula to put the spreadsheet name in a cell (refer to this topic that I posted earlier today - [URL]. The only problem is to get the formula to automatically add a new spreadsheet into it.
Note: The cell reference O8 is standard for all spreadsheets.
View 3 Replies
View Related
Mar 29, 2014
I have WorkbookA that pull numbers from WorkbookB. In Cell D2 of WorkbookA is the name of WorkbookB. The data to be pulled from WorkbookB is in Cells H364 through S364, H being data for January and S being data for December. In January, the data from Cell H364 is pulled into WorkbookA. In February, the data from Cell I364 is pulled into WorkbookA, and so on.
The formula can be written such that, based on WorkbookA indicating in Cell B14 the current month, each month pulls the correct month's data from these cells in WorkbookB.
View 14 Replies
View Related
Mar 11, 2014
I've tried an if statement with AND function with no luck either.
my spreadsheet looks like this:
A1 = "One", B1 = 17/08/2013, C1 = 1000.00
A2 = "Two", B2 = 16/08/2013, C2 = 500.00
A3 = "One", B3 = 17/07/2013, C3 = 1500.00
A4 = "Two", B4 = 13/07/2013, C4 = 400.00
A5 = "Two", B5 = 14/07/2013, C5 = 300.00
"One" represents the total that needs to be paid and "Two" represents the payments.
What I want to do is recognise when "One" is present and deduct "two" from that amount, but the whole spreadsheet is never the same (the data is being pasted from somewhere else) so it's really, really difficult to do that.
So I want to do =1000-500 (for that period, so in D2 it should say 500) and then =1500-400-300 (for the other period so in D5 it should say 800).
NB: Any payment ("two") that comes in after the 17th of the month will be carried forward to the next months "one".
View 12 Replies
View Related
Sep 6, 2005
"DRHannay" wrote:
> how do I place the sheet tab name in my spreadsheet by formula?
>
> Excel 2003
Try:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
View 14 Replies
View Related
Feb 23, 2012
Im using the below formula to paste information into a new spreadsheet based on the if then statement. However, as i paste the formula down the sheet, it doesnt reference all the cells. The formula stays on the cells it referenced initially. How can I make the formula reference all the cells as i drag it down?
=IF('[All Cases ATL.xlsx]New_All_Case'!$C$2="DA1",IF('[All Cases ATL.xlsx]New_All_Case'!$T$2="A",'[All Cases ATL.xlsx]New_All_Case'!$E$2,""),"")
View 3 Replies
View Related
Feb 6, 2009
I have this formula in my worksheet
=sum (L12*M12*N12)
How can i put this formula into VB code so that users of the spreadsheet cannot delete the formula.
View 9 Replies
View Related
Jul 30, 2014
I am having trouble with the final formula for this spreadsheet.
I want to be able to select "Holiday/Sunday" from the drop down men in column H, and have it multiply the total in column F by 2. There is already a formula in column F that sums column E-D.
View 4 Replies
View Related
May 2, 2007
I have created a non-subjective job evaluation spreadsheet, which takes data obtained from a work daily of an individual and is implemented in my spreadsheet. The columns of areas to be evaluated will average each individuals performance by averaging the individual to the rest of his shifts performance and a point system has been established depending on their work level. The situation I'm running into is I'm using the formula below to establish my point system. In an effort to be fair in every way possible, should a particular column end up with all zeros "0" this would then become the average, which should provide the individuals in this group with three "3" points. My forumula as it is set up will now only give one "1" point, when it should be three. The point system works great other than this one particular problem, when everyone has a zero. I'd sure appreciate anyone's suggestion how I might be able to add one more IF statement to this existing formula. Note: I29 is the cell for this particular column that represents the average of the column with data in it. I28 is 10% above average, I27 is 20% above average, I30 is 10% below average and I31 is 20% below average.
point system is 1 point for 20% below average, 2 points for 10% below average, 3 points for average, 4 points for 10% above average and 5 points for 20% above average.
=IF(ISTEXT(I3),0,IF(I3="",0,IF(I3<=$I$31,1,IF(I3<=$I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27, 5,IF(I3<$I$27,4))))))))
View 10 Replies
View Related
