• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Forecast problem

hello.

Im tring to predict what the chances are off a player getting a certain score based on their past 18 games (3 month period).

B2:B10 shows possible scores a player can get. (POSSIBLE)
C2:C10 shows how many times they got that score. (MADE)

Based on the player history A2:A10 (CHANCE) should show the % chance a player has of getting a certain score.

The formula appears to be working but I dont understand why the % are so high. Shouldnt they just add to 100?

Also, said player got a 6 more times than any other score. Shouldnt that be the highest %? Or 5 which the player had none of - shouldnt that have the lowest %?

Maybe for what im trying to do Forecast isnt what i want. Im not looking for anything fancy with trendlines & graphs, just a simple formula as shown in workbook.

Thanks in advance for the help.
FreakyGirl
 

Attachments

  • predict.xls
    20 KB · Views: 7
Freaky

Your formulas are working precisely as expected
If you look at the chart below which is your data
You will see that the Blue Diamonds which is Made vs Possible has a very low correlation
This is highlighted by the very ow R2 value of 0.0058 which in basic terms means there is less than 1% correlation

upload_2015-1-26_9-48-37.png

The Forecast() function simply models the line of best fit and then calculates a new Y value based on the supplied X values

The Chance column is shown as the Red Squares

In regards to the Percentages, you have formatted the numbers as % but in fact they should be the same unit as the Made Column, which is Units not %.

No They shouldn't add to 100% they should and do add to 16

You say that you don't want to do anything fancy with trend lines and graphs, but in reality that is actually what you have done

Can you explain what Possible and Made actually represent and how they are derived?

In the opening you talk about the "past 3 months data", can you supply that?

It may be possible to model them stoichastically using a Monty Carlo simulation technique if applicable
 
Hi ,

This is just my opinion , so if you think it is illogical , you can go with Hui's explanations.

You are trying to predict future results based on past performance ; is this correct ?

If so , the format in which you have the data is not correct.

Assume that you are trying to predict a player's performance ; the most crucial thing to know in this , is where in his performance curve is the player ; is the player's performance on the rise , has it levelled off , or is it on the decline ?

Actual data of his / her performance in each of the 18 games in chronological order would give a better indication of this , than saying that a score of 6 was obtained 4 times , while a score of 5 was never obtained.

Secondly , even if the scores are plotted against their occurrences using a line curve , the resulting graph shows absolutely no trend , just an up and down chart. I really do not know whether anything can be concluded from this.

Narayan
 
Without knowing what Made and Possible are in reality it is difficult to know if there is / could be any relationship between them

Provided there is sufficient data it may be possible to model this stoichastically
This will give a probability of a result but won't guarantee the result
 
hello.
ty for your help.

when i said i didnt want anything fancy, i just meant i dont need to go the advanced steps of making it into a graph etc. i was just looking for a simple table of numbers.

POSSIBLE: a score a player can get on a given night of play. 0,1,2,3,4,5,6,7,8 only.
MADE: the total of how many of each possible score they got over the previous 3 months.

i have updated the workbook to show the last 3 months history per player. i put in all their games to date rather than just 6 per player. since the CHANCE is decided per player, the more games you have in the better the prediction should be.

to the left of the players name i would like to show what the odds are they will get said number based on their play history.

as i said before, im not sure if this is the correct function i should be using for what i want to do.

the ultimate goal is to try to match up players more evenly for a game. the players are competitive but not above sitting with those who are not as good so they can get a better score for the night.

eventually there will be other criteria such as skill added in.

i realize this wont be completly accurate & thats fine. im just looking for something for the players & myself to have fun with.

i know is are #NA errors in the one column for a score of 0. i know why its doing it but for now its just something i have to deal with until those players have 3 months in.

any suggestions or help in the right direction would be appreciated. ty for your time.
FreakyGirl
 

Attachments

  • predict.xls
    680.5 KB · Views: 6
What about in Record!A3 put =K3/SUM($K3:$S3)
Copy across and down
Format as %

upload_2015-1-28_16-0-17.png

This shows you the chance of getting each score

You will see Boom has a 23% chance of getting a 3 or 6 and shouldn't ever get a 0 or 2

This doesn't mean he won't get a 0 or 2
 
Gosh, who would have thought something that simple would have done the trick.

i spent a few days looking up Forecast, Trend, NormDist & several others & i should have just come here first.

thanks for the help - it works exactly like i wanted.

have a GREAT day!
FreakyGirl
 
Back
Top