arc90 lab | tools: Link Thumbnail
Excel Help Forum
ExcelTip.com
Account Icon Account Icon Account Icon
ExcelTip.com

Go Back   Excel Help Forum > Microsoft Office Application Help - Excel Help forum > Excel Programming > Excel Charting
not all months have 31 days not all months have 31 days
Register FAQ Forum Rules Members List Calendar Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 11-21-2005, 01:35 PM
jsc3489
Guest
  
Posts: n/a
not all months have 31 days
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!
Reply With Quote
  #2  
Old 11-21-2005, 01:50 PM
Jon Peltier
Guest
  
Posts: n/a
Re: not all months have 31 days
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/
_______


"jsc3489" <jsc3489@discussions.microsoft.com> wrote in message
news:9079DEB1-0BA4-45AA-BA39-A09FF1FA75F2@microsoft.com...
> 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!
Reply With Quote
  #3  
Old 11-21-2005, 02:10 PM
jsc3489
Guest
  
Posts: n/a
Re: not all months have 31 days
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

--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!
Reply With Quote
  #4  
Old 11-22-2005, 09:00 AM
Jon Peltier
Guest
  
Posts: n/a
Re: not all months have 31 days
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/
_______


"jsc3489" <jsc3489@discussions.microsoft.com> wrote in message
news:F280AC58-FBE3-4B0B-BDBD-DA3DC99C3C3F@microsoft.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
>
> --
> I reject your reality and substitute my own.
>
> Promote hydrogen - one of the best "clean" fuels there are!
>
>
>
Reply With Quote
  #5  
Old 11-22-2005, 11:25 AM
jsc3489
Guest
  
Posts: n/a
Re: not all months have 31 days
Thank you so much. Keen
--
I reject your reality and substitute my own.

Promote hydrogen - one of the best "clean" fuels there are!


"Jon Peltier" wrote:

> 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/
> _______
>
>
> "jsc3489" <jsc3489@discussions.microsoft.com> wrote in message
> news:F280AC58-FBE3-4B0B-BDBD-DA3DC99C3C3F@microsoft.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
> >
> > --
> > I reject your reality and substitute my own.
> >
> > Promote hydrogen - one of the best "clean" fuels there are!
> >
> >
> >
>
>
>
Reply With Quote
Reply

Bookmarks

New topics in Excel Charting

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump


All times are GMT -4. The time now is 07:42 PM.

ExcelTip.com - Archive - Top

Powered by vBulletin® Version 3.7.4
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0