Solved

# Plot a Grpah

Posted on 2014-10-15
92 Views
I want to plot a graph with Date on X -Axis starting from 01jan2014 to 31 dec 2014 and Y -Axis with projects and just the pointers for the status.
I have attached the spread sheet.
let me know if I am not clear on any thing
0
Question by:Malli Boppe
• 4
• 3
• 2
• +1

LVL 27

Expert Comment

Can you try re-attaching the spreadsheet?
0

LVL 23

Author Comment

Attaching it again
Book1.xlsx
0

LVL 50

Expert Comment

Hello,

a chart has to have at least one axis that plots numbers. If the X axis has the dates, and the Y axis has the projects, what are the values that you want to chart?

Can you manually (or using the drawing tools) draft the desired chart? If Project A has Status "X" on 1/1/2014, how does that look in the chart? High? Low?

And if the Y axis also has projects B, C and D, how do their status texts for certain dates look in the chart?

If you can draft that and show us what you would like to see as the result, we will know what to aim for and can most probably find a way of doing it.

But right now, I have no idea what numeric values to plot in a chart when all the input is text.

Over to you.

cheers, teylyn
0

LVL 23

Author Comment

I drew a picture of what I wanted.
Hopefully you will get  what I am trying to achieve.
Capture.PNG
0

LVL 50

Assisted Solution

teylyn earned 167 total points
OK. That's a start.  Here is your picture again for all to see.

I see project names on the vertical axis. There are stars on the chart with three different colors.

Can you help me figure out the logic for plotting the stars in the positions of your sample:

- What determines the color of a star?
- the X axis value of a star must be the the date. But what determines the vertical position of a star? There is one red star at the level of project B. And there is another red star between project B and project C.  Why is it there? What is the logic?
- a green star is positioned for January at project A. Another green star shows in April in project B.  What is the data for that?
- The labels on the X axis don't make sense. There are two labels for January. The month labels are not positioned at intervals that would correspond to a time line. Where is February?

How does your Excel table with text values connect and relate to that chart?
0

LVL 27

Expert Comment

I'm wondering if an Gantt chart might be a good starting point for this.  That would allow Project names to appear on the Y-Axis and then the dates could be treated like milestone points.   The tricky part would be getting each point to be assigned the correct color.
Here's a quick sample with the first points for the three projects:
It uses a graphic (a colored star) for the fill property of each point.  But the data points have to be manually updated to the color shown in the data table.  That may need some automation to achieve.

-Glenn
0

LVL 14

Expert Comment

Malli,

The issue with what you're describing, is that you aren't asking for the status of a given project.  What you are describing in your graph is, "During the course of a year, when did the green star move from "Status = Project A" to "Status = Project B"

Part of the problem is that you are trying to graph something qualitatively, which is impossible.  Just think, where on a graph would I measure "red", or where would I measure "chocolate".  They are just adjectives, not something quantifiable that can be graphed.
0

LVL 27

Assisted Solution

Glenn Ray earned 333 total points
Actually, the questioner is not asking for when a status "moves" from one project to another, but rather the show the status on key milestone dates.  Expanding on my earlier example, here are the first two dates for each project:

Again, this is plotted more like a Gantt chart, but I'm using images for the bar plots instead of bars.

-Glenn
0

LVL 23

Author Comment

Thanks Guys

can you please tell me which chart to use.
Some brief instructions how to acheive this
0

LVL 27

Accepted Solution

Glenn Ray earned 333 total points
I used a bar (not column) chart to create the one shown above.  However, it is not automated in anyway and requires a few tricks to make it work:

The data has to be transposed in such a way as to properly position each milestone.
Then the bars themselves are one of two types of image files:  a colored star for each milestone type and a thin line used to connect milesones.
Then the milestone stars have to be individually colored to match the colors shown in the original data.

What I have been able to do is add array functions (special classes of functions that work across multiple cells) that return the nth milestone date for each project.  The formulas are set to look for up to 100 rows of data on the "Original Data" sheet and, regardless of sorting order, they will pick up the correct date.

The "Delta" value is the time (in days) between release dates and is used to space apart the milestone stars.

There is also a global "Star Size" value that can scale the size of the stars (width, anyway).

Here's what the entire original data set looks like plotted:

Example workbook attached.

Regards,
-Glenn
EE-Gantt-Chart.xlsx
0

## Featured Post

### Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.