Plot a Grpah

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
LVL 23
Malli BoppeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Can you try re-attaching the spreadsheet?
Malli BoppeAuthor Commented:
Attaching it again
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Malli BoppeAuthor Commented:
I drew a picture of what I wanted.
Hopefully you will get  what I am trying to achieve.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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?
Glenn RayExcel VBA DeveloperCommented:
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.

Joe RudSystems AdministratorCommented:

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.
Glenn RayExcel VBA DeveloperCommented:
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.  

Malli BoppeAuthor Commented:
Thanks Guys

can you please tell me which chart to use.
Some brief instructions how to acheive this
Glenn RayExcel VBA DeveloperCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.