Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Plotting Bands for Task Overlaps
I am trying to create a chart with horizontal bands (similar to a bar
chart) but I need the color of the band to change with different dates. For instance from: 01/01/2000 to 06/01/2000 plot Blue 03/01/2000 to 06/06/2000 plot Red 04/01/2000 to 07/06/2000 plot Yellow My intention is to have 1 band with different colors based on a task length. Then have multiple bands stacked so I can visualy check for any overlaps in colors(tasks) Right now my data is in 2 colums: Start Date Task Name Thanks in advance Steve |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Plotting Bands for Task Overlaps
You need to make a Gantt chart. This is simply a stacked bar chart, where
the first bar is transparent (no border, no fill), then with another bar for a task. Or in your case, multiple bars for multiple colors. This article has some basic suggestions for creating Gantt charts in Excel: <http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 Your chart source data would look like this: Task Start Blue Red Yellow The start date column is the same for all series. The other columns show the elapsed days which should be formatted in that color, or zero if that color shouldn't appear in the chart. Your data as shown would start like this, not as above: Task Name Start Date End Date Blue Task 01/01/2000 06/01/2000 Red Task 03/01/2001 06/06/2000 Yellow Task 04/01/2000 07/06/2000 In the main table, use formulas to determine the elapsed time under the appropriate color label. Fill the range A1:F4 like this: Task Name Start Date End Date Blue Red Yellow Blue Task 01/01/2000 06/01/2000 Red Task 03/01/2001 06/06/2000 Yellow Task 04/01/2000 07/06/2000 To simulate your color criteria, I populated L1:M4 with this lookup table: 1/1/2000 Blue 2/1/2000 Green 3/1/2000 Red 4/1/2000 Yellow Then I selected D2:F4, with D2 as the active cell, and entered this formula: =IF(VLOOKUP($B2,$L$1:$M$4,2)=D$1,$C2-$B2,0) Then I held down CTRL while pressing Enter to enter it into all selected cells. The resulting range looks like this: Task Name Start Date End Date Blue Red Yellow Blue Task 01/01/2000 06/01/2000 152 0 0 Red Task 03/01/2001 06/06/2000 0 97 0 Yellow Task 04/01/2000 07/06/2000 0 0 96 Select A1:B4, then hold CTRL while selecting D1:F4, so both regions are selected. Start the chart wizard, and select a stacked horizontal bar chart. Now simply format the chart to get the colors you want. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "gtslabs" wrote in message oups.com... I am trying to create a chart with horizontal bands (similar to a bar chart) but I need the color of the band to change with different dates. For instance from: 01/01/2000 to 06/01/2000 plot Blue 03/01/2000 to 06/06/2000 plot Red 04/01/2000 to 07/06/2000 plot Yellow My intention is to have 1 band with different colors based on a task length. Then have multiple bands stacked so I can visualy check for any overlaps in colors(tasks) Right now my data is in 2 colums: Start Date Task Name Thanks in advance Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
avoiding plotting zero values on graphs | Charts and Charting in Excel | |||
vertical bands | Excel Discussion (Misc queries) | |||
plotting a single line graph | New Users to Excel | |||
How do I prevent Excel from plotting a cell w/ a formula as a "ze. | Charts and Charting in Excel | |||
How do I prevent Excel from plotting a cell w/ a formula as a "ze. | Charts and Charting in Excel |