How to Plot Scatter Diagram with unequal/ arbitrary dates on X axis

**ranjana** · 07-05-2010, 07:21 AM

Hi All,

I am stuck with a typical problem while working in Excel 2007. I need to plot the data as follows, in a scatter diagram. The date intervals are not equal in length or in gap! How to manipulate with the display of X-axis?

Gurus/ experts, awaiting your suggestions.

Dates/ Month Price
June 4.58
June-July 5.5
June-Aug 6

--Warm Regards,

**teylyn** · 07-06-2010, 02:35 AM

Hello ranjana,

if you want dates spaced out evenly on the X axis of a XY chart, then you need real dates as input for the X axis, not text.

What results to you expect? That is, where should the data point for

June 4.58

sit? And where would you expect the data point for

June-Aug 6

?? Please post an Excel file with some sample data and maybe mock up a chart with the drawing tools manually, so it is clearer what kind of output you want to achieve.

cheers

**ranjana** · 07-06-2010, 05:25 AM

Thanks teylyn for replying. I am attaching a excel file with this. i want the month/ dates serially as is arranged in the excel table, e.g. July followede by August then July-August and so on.

Please find the attached file.

**teylyn** · 07-06-2010, 07:10 AM

ranjana,

thanks for your file, but it really does not clear things up. Your column A is titled "Month".
Rows 2 and 3 show "July" and "August" respectively. Row 4 shows "July - August" and the next row "July - September".

I think you need to straighten out your data. Either you are charting points in time, like single months, or you are charting time frames like 2 months, three months, etc.

What shall it be?

As a general rule, a XY scatter chart requires NUMBERS for the X axis values. These numbers can be date values (which are basically numbers). But they can not be time frames, like July-September.

So, change your values in column A to a single date per row

1-June
1-July
1-Aug
1-Sep

and make sure these are real dates, then you chart will come right automatically.

cheers

**ranjana** · 07-07-2010, 01:04 AM

Thanks a ton, teylyn, for your lucid explanation!

In fact, the raw data which I have are very peculiar in nature and some of the prices are for one month and some are for a period of time,a s you can see from the excel file I have attached earlier.

So, now I understand form your explanation that if I want to keep the data like this only, it is not possible to plot it in scatter diagram. BTW, is there any other type of chart which can show the data with that time-frame?

Warm Regards,

**teylyn** · 07-07-2010, 02:16 AM

You may want to turn a data point like

June-Aug 6

into several points like

1-June 6
1-July 6
1-August 6

and still plot it in a XY chart.

But you can't plot anything meaningful if the data is one value categorised by a text like
June-Aug. No chart type can do anything with that. You'll need to convert it to dates in any case.