?
Solved

Plot a Grpah

Posted on 2014-10-15
10
Medium Priority
?
102 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 668 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
 
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 1332 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 1332 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

771 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