Point plotting problems!

June 22, 2006 12:28 PM

Excel 2003 graph woes. My friend is looking to make a simple x:Date; y:Value chart but with one problem.

(Please be gentle, I'm helping a friend and my Excel/graph-fu is nil)



This is a mock up of what he's trying to achive. What he has is a set of values that he needs to chart on this type of graph along with dates, but he needs the graph to be spaced as though he had values for all the dates. The graphs we've been able to make all stack the dates evenly.

I think the mock up explains it all (at least a lot better than I can, this just gives me a brainfreeze). I don't actually have Excel, so I'm unable to help him out by tinkering around.

posted by jedrek to computers & internet (6 comments total)

Do you mean that you don't want the dates with no values to appear at the bottom of the page? I just created a simple spreadsheet with the following values and it looks like yours, above, with only 4 data points, except there are dates all along the bottom:

2/6/2006 12
4/1/2006 14
6/6/2006 13
6/7/2006 10

I would make sure that the column with dates is in date format. Right click on those cells and do "Format Cells" --> Number Tab --> Category: Date
posted by scarabic at 12:32 PM on June 22, 2006

Here it is. I just selected all 8 cells and did "insert --> chart"


posted by scarabic at 12:35 PM on June 22, 2006

Actually, he needs the dates where he doesn't have values to take up space as if they were there... but not be there. Sorry, I see I forgot to mention that.
posted by jedrek at 12:41 PM on June 22, 2006

So he needs scarabic's graph but without the date label where there is no data for that date? I can sorta do it by messing with the number formatting on the X axis.

For example, I did it using the following custom format in the Format Axis/Number menu: [=38113]m/d/yyyy;[>38143]m/d/yyyy;" "

The 5-digit numbers represent dates, obviously. I wanted to specify all three dates, but Excel wouldn't let me do three [=00000] conditions for some reason, so I just used the "greater than" symbol and then under the "scale" tab I set the X-scale maximum to equal 6/7/04.
posted by mullacc at 12:51 PM on June 22, 2006

I got it to display with only the 4 dates by going through the Insert->Chart wizard. On the third step, select the Axes tab. Under "Primary Axis", then under "Category (X) axis, change the selection from Automatic to Category.
The result is a bit different from your mockup - the dates are not spaced apart nor angled, but it's a start.
posted by Meagan at 1:03 PM on June 22, 2006

It's not an elegant solution by any means, but when I'm faced with this type of annoyance I'll often do something along the lines of removing x-axis labels from the graph, and manually inserting text boxes with the labels I want to include. Certainly a PITA, and takes some tweaking to make it look presentable, but in a pinch it works.
posted by nixxon at 5:12 PM on June 22, 2006