Linear chart of info not in continous cells

[nigelog]

Hi I have no experience above basic block charts generated automatically by excel but wish to create the following output

In sheet 1 I have data relevant to the MPG of a vehicle over variable distances(might not see vehicle in same timescale as others) starting with the overall average MPG from new.

I have drawn in sheet2 what I think the output should like but do not know how to acheive this.

From that start figure and with a datum line as a guide I wish to trace linearly the increase/decrease in MPG over distance. Hopefully the length of the linear line will reflect the distance travelled and the percentage increase/decrease measured by the deflection of the line from the datum line. If it could also box the mileage figures on the chart even better.

This is to be a continuous record and accessible through a macro straight to print. Can anyone give me an initial pointer in the right direction. Rgds nigelog

[nigelog]

Hi I have no experience above basic block charts generated automatically by excel but wish to create the following output

In sheet 1 I have data relevant to the MPG of a vehicle over variable distances(might not see vehicle in same timescale as others) starting with the overall average MPG from new.

I have drawn in sheet2 what I think the output should like but do not know how to acheive this.

From that start figure and with a datum line as a guide I wish to trace linearly the increase/decrease in MPG over distance. Hopefully the length of the linear line will reflect the distance travelled and the percentage increase/decrease measured by the deflection of the line from the datum line. If it could also box the mileage figures on the chart even better.



This is to be a continuous record and accessible through a macro straight to print. Can anyone give me an initial pointer in the right direction. Rgds nigelog

Moved data to remove the incontinuous problem but even at that I cannot get excel to treat the info in the deviation from a linear line. Any one any ideas as to how to acheive the shape highlighted in second sheet in sample. Ta Nigel

[Andy Pope]

This is a xy-scatter of the data. Not exactly a match for your example chart.

In order to do that you would need to determine the Y value at which to plot each driver. Then calculate the difference between mpg's and add offset.

Also as you have discovered you will need to change the way the data is laid out.

[nigelog]

Hi Andy

I have after experiment decided on what is the important information needed to be displayed and have narrowed it down to average since new and then measurement points 1,2,3,4 etc.

I have managed to display what I am looking for, for an individual driver but would like to simplify the change of data source. ie. Can the individual reg number be picked from a list or can 1 or more be selected for comparison as in second chart.

I have 48 vehicles to track divided into groups by age, largest group being 15, can this be displayed clearly on A4 for group without the recourse to different coloured lines.(can the data be displayed per vehicle but with only main axis rather than repeats of axis.

Can the chart also generate a horizontal red datum line equal to its av mpg from new.

Sorry for all questions and would appreciate help with any1 of the above so I can start building the solution. Many thanks Nigel - sample attached

[Andy Pope]

See the attached.

The chart is setup to report all records. You can use the Autofilter to reduce series plotted.

There is addition series included in the chart to display truck/drive and datum lines.