Solved

Plot a Grpah

Posted on 2014-10-15
10
96 Views
Last Modified: 2014-10-22
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
Comment
Question by:Malli Boppe
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40383671
Can you try re-attaching the spreadsheet?
0
 
LVL 23

Author Comment

by:Malli Boppe
ID: 40383675
Attaching it again
Book1.xlsx
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40383750
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

by:Malli Boppe
ID: 40383894
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

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 167 total points
ID: 40383929
OK. That's a start.  Here is your picture again for all to see.
Capture.PNG
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40384726
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:
milestone chartIt 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

by:Geisrud
ID: 40384828
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

by:Glenn Ray
Glenn Ray earned 333 total points
ID: 40384995
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:
milestone chart
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

by:Malli Boppe
ID: 40385572
Thanks Guys

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

Accepted Solution

by:
Glenn Ray earned 333 total points
ID: 40385598
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:
milestone chart
Example workbook attached.

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now