Use Excel To Create A Formula To Predict Future Outcomes?
Jan 17, 2014
So I have some stats I have been tracking for NHL hockey games. I want to find a formula that is predictive of future outcomes, based on whether or not a team has a statistical advantage. Obviously, there are so many other factors, and there is no golden formula to accurately predict the winner of ever game. I want to find maybe a 70% success rate here. I want to be able to plug in all of the stats, and essentially have it tell me who statistically should win the game.
I have 11 independent variables (Stat A, B, C, D, E, etc.). I have a running total of how often the winning team had the stat advantage for each of these, and I think that should factor into the formula. My dependent variable is whether the team wins or not. how to do that with multiple independent variables.
I used three different dates. In which the outcomes were available. The first three sheets.
Is there a possiblity to use excel to calculate a future outcome of the next month(Sheet 4). The figures are in a way linear, because the previous months are in it.
So it's possible for the future outcome to go up or stay the same. It can't be less then previous months.
See attachments.
It's about a holiday (combinations) of arrangements. So it'll be influenced by the date (because of holliday plannings), finance etc etc. But the last factors/variables we will use and test in SPSS and other programs.
The question is more: Is there a program/formula in excel which will predict the outcome in the last sheet with maybe a factor of outliners (this coincendence of a better salary in some parts/or the date that people will book a holiday). And how the formula would be formulated in the last sheet?
if h5 = "Supply Only" or "Supply Only With IWA" then I5 should be "S/O" however if h5 = "Supply Only With Survey" or "Supply Only With Survey With IWA" then I5 should be "S/o & S"
h5 could also be "Supply & Fit" or "Supply & Fit With Iwa" which should return "S/F"
but if h5 = "Supply & Fit With Survey" or "Supply & Fit With Survey & IWA" then I5 should be "S/F & S"
I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).
Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?
How can I create a formula to calculate a future date, when it is not a “specific day” in the future? I mean, I have today's date (Jul 15/08) and I need to calculate from that, when it will be the first Monday of November in 2009.
Reference a worksheet that has not yet been created, avoiding the #REF! result?
The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.
I have 3 columns of random numbers... it the first 2 columns the numbers range from 01 to 19... and in the 3rd column they range from 01 to 15 The total number of numbers in each column is 30... and i want to know is there a way to pridict the 31st set of numbers?
I have 11 months of sales commission data, and need to estimate the value for December. However, the catch is, for the first 7 months, the values are significantly higher than the most recent 4 months. I'm currently using the TREND function to guesstimate the December value, but with the wide fluctuation between the 2 time periods, I'm thinking the result of the TREND value may be way off.
Is there a way to 'weight' the data to reflect the higher values earlier in the year, with the much lower values later in the year?
I need to create a formula using 2 different tabs within an excel workbook. I need to search column A on the first tab and column A on the second tab and if they match I need to compare column F on the first tab to column F on the second tab. (This column contains my staff names but the problem I am running into is on the first tab their names are listed first name last name and on the second tab they are listed last name, first name). If column A and F match I need to search column H on the first tab to column H on the second tab. If all columns match up to this point I need to take the data in column Q on the second tab and in put it into column K on the first tab. Is this even possible?
I'm trying to update a the summary sheet that will run through the 10 worksheets and bring back the entire row (or the columns I specify) that matches the creteria MS.
MS will appear against each row in the same column in all the tabs.
So in the summary sheet I would see all the rows individually that appear in tabs 1-10.
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
I have an If statement I am trying to get to work but not sure of the flow. Each part works seperately but not together. What I need is if column E is blank leave blank, if cell DI2 is Yes then use columns DJ to DP for the lookup if DI2 is No than use columns O to U for the lookup. The first 2 lines are the individual formulas that work independently. The 3rd was my last attempt at combining them.
I have a master worksheet with due dates for various forms to be completed. These forms are on separate worksheets within the workbook.
I'd like to highlight the master date entry in red if the form hasn't been completed by the due date, but also highlight in green when it has been done.
I recently posted work of my attempts to create a league table, i feel i have probaly chosen the worst way to record the actual results of matches, causing the need for complicated formula which donkyoite has been very helpful in doing and i thank him, but i dont understand whats going on, was wondering if there was a simplier way?
I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.
I need to compare the number value of 3 cells to 64 possible outcomes, of the three cells they can only have one of four possible values, How do I write the formula to do this. if cell a1 is 1,a2 is 1, a3 is 1 then thave it display the number 64, but I also need it to able to look at the same three cells, 64 different combinations, like 0,0,1 displays 21 etc.
I seem to struggle with excel formulas when time is involved. I am trying to create a spreadsheet that reviews outcome measures (walking tests in this case) to outline firstly if someone has improved or not (Outcome in the blue columns) which seems to be working alright. This takes into account the 2 aspects of time taken to complete the test and the walking aid required.
What I would now like to be able to do is compare each individuals 'After' score with a normal range for that test. I have put a list below the table of the ranges I am looking for. I have tried to put time formats into a long IF formula to try and get the ranges covered for the TUAG test, but doesn't seem to be working.
The 10 metre walk test is a little trickier as this is looking at change in velocity between the before and after scores. I think I need to be able to work out the velocity for each test and then do a comparison, but again bit stuck with the time formatting.
Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2
I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.
This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?
I have a table that includes 2007 data and 2008 data. When I was entering Jan 1 for 2008, it defaulted to 2007. I had the whole table completed with all the 2008 dates before I realized this. Is there a quick way to highlight all the 2008 data and change only the year?
I have a list of african countries and their C02 emissions from 1990 to 2010. The question I'm asked is, who will be the top 5 emitters in the year 2020 given the current trends. I have done a lookup command and compiled a list of the top 5 emitters. My concern is though i do not know how to get the 2020 forecast of the top 5 emitters rather than the current datas.
Does anyone know a formula that calculates the future value of an unequal payment stream? I am attempting to calculate the value of an investment plan where the periodic payment increases by 1% each period. I can get the answer by using the FV formula for each payment and then adding them up, but would like to be able to do it without having to do so.
