Betfair Community

Home › General Betting Forum › Excel help

Switch to Community Classic App Directory

General Betting

‹ back to topics

There is currently 1 person viewing this thread.

Excel help

starfish and coffee

13 Mar 12 21:18

Joined:

Date Joined: 03 Sep 08

| Topic/replies: 8,055 | Blogger: starfish and coffee's blog

Hi excel helpers

Is there a way to insert a 'line of best fit' column in excel based on data in other columns

For example if you were assessing ratings and had columns with rating / runs / wins / strike rate, and the strike rate was not exactly linear, could you add a fifth column with a suggested linear pattern

I want to do this without using graphs or charts if at all possible

Thanks!

Switch to Live View refresh Post Reply

Post your reply

sort by:

Show per page

Replies: 13

By:

DivideByZeroError When: 13 Mar 12 21:36

You could use FORECAST if there is only one pair of variables.

For example your new column of linear fit values would be

=FORECAST(rating,column of strike rate, column of rating)

By:

starfish and coffee When: 13 Mar 12 22:01

thanks DBZE, i'm messing about with it at the moment and will let you know

regards

By:

starfish and coffee When: 14 Mar 12 00:29

I've realised I used the wrong term in my OP, i dont want the results to be linear, rather they should represent the line of best fit from the data

FORECAST isnt working for me, the strike rate column is running into negative values whereas it should obviously stick at zero or a bit higher for low ratings.

any alternative or fix?.. many thanks

By:

the silverback When: 14 Mar 12 16:57

You can use the INSERT tab to insert a graph of the data. And then if you right click on the line you can "add trendline" and then fiddle about with the best linear, logarthimic, exponential, polynomial fits. Tick the box to show the equation and the R squared and off you go.

And you just delete the graph afterwards if you want.

By:

the silverback When: 14 Mar 12 16:59

Depends on how your variables of course. If there are several then might the regression tool in data analysis add-on help?

By:

the silverback When: 14 Mar 12 17:00

NB I may be off the mark with those suggestions as I'm self taught and just muddle through this kind of stuff with what are probably wonky techniques.

By:

jabmast When: 14 Mar 12 17:05

LINEST is the function you want. It will match a trendline exactly.

By:

JLivermore When: 14 Mar 12 17:20

"i dont want the results to be linear, rather they should represent the line of best fit from the data"

of all the possible lines the one which fits best is a line exactly through all your data points. If this is what you want then the scatter chart can do one, either smoothed or unsmoothed.

If you want a fitted line then you need to give the computer a choice of the type of line - probably either linear or from eye-balling using the method described by silverback.

By:

top2rated When: 14 Mar 12 17:43

starfish and coffee - is your data something like this?

	A	B	C	D
1	Rating	Runs	Wins	SR%
2	79	23	2	8.7
3	80	43	3	7.0
4	81	36	2	5.6
5	82	42	4	9.5
6	83	64	0	0.0
7	84	66	3	4.5
8	85	113	11	9.7
9	86	134	10	7.5
10	87	127	9	7.1
11	88	109	8	7.3
12	89	147	13	8.8
13	90	189	11	5.8

By:

I am the one and only223 When: 14 Mar 12 18:36

I use CurveExpert to find the line of best fit. It's free , easy to use, and has loads of different models.

By:

starfish and coffee When: 14 Mar 12 18:50

thanks for everyones input during the day, yes thats what we're talking about toprated but big sample size so not erratic

also the second rated is fixed at 0 with very large sample size so thats going to be more or less 'accurate'. a straight line is not going to represent that properly

i dont want to mess up with a chart, an example would be :-

rating 5 (21%) 4 (20%) 3 (17%) 2 (20%) 1 (19%) 0 (15%) -1 (14%) -2 (13%) -3 (12%) -4 (10%) -5 (11%)

the sample sizes in this set of data are 5000 for zero, 1000's for the lower ratings, 500-800 for the higher. the sample sizes are smaller for high ratings but they are the ones i need to get more or less 'right'

hope this helps you to help me??

cheers

By:

starfish and coffee When: 14 Mar 12 18:55

i'm downloading Curve Expert and will look at the other suggestions, thanks for the input

By:

starfish and coffee When: 14 Mar 12 19:41

that download looks perfectly manageable. 'lowess smoothing' does it for me ..never thought i'd say that in public

Still doesnt quite hit the mark .. in practise i will be keeping the zero value from the raw data described above, then use the tool twice, one for positive values, one for negative values)