Excel help

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)

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

regards

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

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.

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

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.

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

"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.

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

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

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

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

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)