I need to create a "true" scatter graph in excel.
Lust like school were you plotted variable x angainst variable y.
That way, you just get a feel for the trend, if any.

I need to do this in excel as I have 200 variable to plot, and they come from excel in the first place.
Basically a bus/coach service, with total KM, and cost per KM.
I would expect that the longer the service in KM, the lower the cost per km.
Scatter diagram would be perfect for this, as it will show outliers.

I thought that I would do a test in excel with car data, as we all live & breathe cars.
Basically 0-100 mph times should *generally* be related (correlated ??) to bhp/tonne.
Generally ??
I would expect a scatter diagram to show this quite well.
In fact my manual plot of 12 cars does this very well - except the CCX.

However, on the below data, excel fails to produce what I want.
I want Excel to produce 'the school plot' as mentioned above.
Any ideas ?

NB - I'm camping this weekend, so don’t expect an answer till Monday !!

Make 0-100 mph bhp / tonne
Ultima 5.5 666
Enzo 6.7 484
CCX 7.7 694
GT3 996 10.3 271
RS 993 11.2 240
M3 E36 12.8 215
330ci 16.6 154
M3 E30 17.8 178
Elise si 18.5 164
x Type 3L 19.8 151
MR2 21.2 141
Puma R 23.2 132

 

try posting on here mate

MrExcel Message Board ::

these guys know everything excel related

 

What's wrong with Chart Type> Scatter (XY) for just two variables?
A trend line can then be displayed to a predicted formula.

If you have a lot more variables and want to find which variables are closely related then perhaps run ANOVA on the dataset.

Nick

 

I've tried the Chart Type> Scatter (XY) but it plots both variables, whereas I want to plot the intercept of the 2 variables.

And the trendline, asks you to base it on one of the variables.
I tried this on bhp per tonne.
This would then be a trendline based on that one variable.
So, if I had a graph with just bhp per tonne, the trendline would be the same.
So therefore that would just tell me the average trend of my 12 cars.
So if I add a 1960's (morris) mini with low bhp per tonne, my trend line would change. But the *usefulness* of the trendline would be zero.

However, if I had a trendline on the intercept plots, that would be useful.
Which points me in the direction, maybe there is an excel function you can use on the data, then plot the result of that. Like correlation, or regression.
I shall explore.

But Mr Excel is worth a shot, too.