Trying to compare % to date across several years

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I have a simple need. I have calculated cummulative % of
full year's sales for several years and want to compare
the sales patterns. The chart would show each year's
sales from 0% up to 100% with % as Y axis and day of year
as X axis. Can't make any date formats allow such a
comparison, so I have calculated a day of year by
subtracting the date of each sale from the 12/31 date of
the prior year, therefore each data point is a value
between 0 and 100% and a "date" between 1 and 365. Seems
like a routine chart, but nothing seems to make the x
axis display correctly.

Surely this is a common need? Would appreciate any help.
 
Richard -

How is your X axis displaying? How would you like it to display?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Originally I wanted the X axis to be month/day
(regardless of year) but could find no way to do that.
So I calculated day of year (from 1 to 365). I tried
various ways to set that up which seems simple, but got a
whole set of odd results ranging from things like 1/11,
1/19 etc (not relating to the month/day); to a single
number (seems random) to odd sets of other numbers.

The original data are in two columns: one with the %
value and one with the day of the year. Each year's data
is together in groups, one above the other. And, of
course, the random dates of each large sale mean that
each year's dates are different from the other years'.
 
Richard -

I think we can fake it. You might be off ± a day depending on leap year,
but let's see how it looks.

Put dates for your first series in the first column, and values for the
first year in the second. Put the values for subsequent years in the
next columns. Use the years for the column headers.

2001 2002 2003 etc.
1/1/2001 20 22 21
1/2/2001 25 27 28
1/3/2001 28 29 33
1/4/2001 31 31 34
etc.

Keep the top left cell blank. Select the range and make your Line chart.
The first series' dates provide the ability to format the date as
month/day (use a custom number format of "mm/dd" or "mmm/dd").

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks. Using your idea, after an extreme amount of
manipulation and sorting, I was able to make a graph for
which I am grateful. This was a substantial
disappointment for me in Excel's graphing. It is
requiring that all of the data points be ordered within a
constant x axis order, rather than reading the x axis
point within a column to correspond with that particular
y axis data.

I appreciate your help.

Richard
 
Back
Top