Do you know how to label an axis in Excel with date ranges? I’ve never used Excel before and Iim trying to have the ticks on the X Axis indicate date ranges, i.e., 1998-99, 1999-00, 2000-01, 2001-02, etc.). Can anyone tell me how to do this. The answer is not in Excel for Dummies or in the computer question section of SDMB.
Many thanks.
Your going to have to create a range in your workbook that has these labels- either by entering them directly, or by creating a formula that builds a text string based on other data that you are trying to chart . Also note that as far as Excel is concerened these “data ranges” are just pure text, they are not treated as date values. Now if what you are trying to do is summarize a series of data points each of which has an individual date and “lump them together” into a series of date ranges, then you are first going to have to create an intermediate table that summarizes your data, each row of which has the date ranges you want, and then graph that (not graph the “raw data” directly). There is any number of ways to create an intermediate table- you could use a pivot table, you could use formulas using “SUMIF” or “DSUM”, etc. , all of which are explained in Excel’s help or your Excel for Dummies book.
For example say you had a table like this:
date # of widgets
1/1/2005 3
3/3/2004 5
1/3/2005 5
9/1/2006 5
7/4/2007 5
And say this data were in the cells A1 to B6.
You could write a formula like thus:
=SUMIF($A$2:$A$6,">=1/1/2005",$B$2:$B$6)-SUMIF($A$2:$A$6,">=1/1/2007",$B$2:$B$6)
To give you the total of all the widgets sold in 2005-6. In the coulmn left of the cell in which you put this formula , you would put the label “2005-06” (again just text).
In the next row, copy the above formula, except change the dates for the year range you want to represent (and change the label of course), so you would end up with a table that looks like this:
Years Total Widgets Sold
2005-06 13.00
2007-08 5.00
Now you would highlight this new range, and simply chart it (using a “Column” or “Line” graph type chart- NOT an XY (Scatter) type chart). The Category (X) labels will come from the first coulmn, the Values from the second column of this derived table.
Hope this helps.