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
Solved

Plot a Grpah

Posted on 2014-10-15
10
98 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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