Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting days to months | Excel Worksheet Functions | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
6 months of working days | Excel Worksheet Functions | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
difference between two dates in years, months and days. | Excel Worksheet Functions |