not all months have 31 days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a "floating bar" chart that has the "# of Techs" as my
catagory axis, months as my value axis. The chart displays the # of techs
required to perform the job in each month (1 year). However, my months are
all 31 days in my chart so my data gets a little skewed as the year goes by.

I have created a formula to correct this, BUT only if the information in my
"start date" column are consecutive, which they may not always be.

My series data consists of the "start date" as series 1 and "# of days to
complete" as series 2.

Any way to "fool" the chart?


-- I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!
 
To clarify, could you post a sample of your data? Not a worksheet, just copy
and paste in a reply. Indicate what you're using for X and for Y, and verify
that you're using a "bar" chart and not a "column" chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Total Hours Start Date Techs. Required End Date Projected End
Date Hours/Month Work Days to Complete Tech Hours Each Actual
Days Techs./Month
1000 1/3/2005 6 1/31/2005 168 20.83 166.67 28 5.95
600 2/3/2005 4 3/1/2005 160 18.75 150.00 26 3.75
450 3/1/2005 3 3/25/2005 184 18.75 150.00 24 2.45
300 4/1/2005 2 4/27/2005 168 18.75 150.00 26 1.79
1200 5/2/2005 7 5/31/2005 176 21.43 171.43 29 6.82
500 6/1/2005 3 6/29/2005 176 20.83 166.67 28 2.84
765 7/1/2005 5 7/28/2005 168 19.13 153.00 27 4.55
275 8/1/2005 2 8/24/2005 184 17.19 137.50 23 1.49
450 9/1/2005 3 9/27/2005 176 18.75 150.00 26 2.56
230 10/3/2005 2 10/21/2005 168 14.38 115.00 18 1.37
830 11/1/2005 5 11/29/2005 176 20.75 166.00 28 4.72
1000 12/1/2005 6 12/29/2005 176 20.83 166.67 28 5.68


Chart type "bar" or "floating bars"

right now I'm trying your Arbitrary axis scale and I've
downloaded the XY Chart Labeler and I'm not sure how to use.

typically I would use "# of techs" as my Y and the "months" as my X
 
You can use an area chart. I made a list of all dates, the first and last of
each month, and put the Techs/Month value for the endpoints of each month:

1/3/2005 5.95
1/31/2005 5.95
2/3/2005 3.75
3/1/2005 3.75
3/1/2005 2.45
3/25/2005 2.45
4/1/2005 1.79
4/27/2005 1.79
5/2/2005 6.82
5/31/2005 6.82
6/1/2005 2.84
6/29/2005 2.84
7/1/2005 4.55
7/28/2005 4.55
8/1/2005 1.49
8/24/2005 1.49
9/1/2005 2.56
9/27/2005 2.56
10/3/2005 1.37
10/21/2005 1.37
11/1/2005 4.72
11/29/2005 4.72
12/1/2005 5.68
12/29/2005 5.68


I plotted this in an Area chart. The X axis became a date axis. This
represents the different lengths of each month well, but at the change of
each month, you get a sloping line, where for example, from 1/31/05 to
2/3/05 goes from 5.95 to 3.75

To get vertical edges at the end of each period and a gap between for
example 1/31/05 and 2/3/05, you need to insert more dates, actually
duplicates of each date, sorted. Keep the Techs/month across each
within-month span, and zeros for each inter-month span, and repeat the area
chart. Here's the data:

1/3/2005 0
1/3/2005 5.95
1/31/2005 5.95
1/31/2005 0
2/3/2005 0
2/3/2005 3.75
3/1/2005 3.75
3/1/2005 0
3/1/2005 0
3/1/2005 2.45
3/25/2005 2.45
3/25/2005 0
4/1/2005 0
4/1/2005 1.79
4/27/2005 1.79
4/27/2005 0
5/2/2005 0
5/2/2005 6.82
5/31/2005 6.82
5/31/2005 0
6/1/2005 0
6/1/2005 2.84
6/29/2005 2.84
6/29/2005 0
7/1/2005 0
7/1/2005 4.55
7/28/2005 4.55
7/28/2005 0
8/1/2005 0
8/1/2005 1.49
8/24/2005 1.49
8/24/2005 0
9/1/2005 0
9/1/2005 2.56
9/27/2005 2.56
9/27/2005 0
10/3/2005 0
10/3/2005 1.37
10/21/2005 1.37
10/21/2005 0
11/1/2005 0
11/1/2005 4.72
11/29/2005 4.72
11/29/2005 0
12/1/2005 0
12/1/2005 5.68
12/29/2005 5.68
12/29/2005 0


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top