Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. transapp - Member

   Seems to be a few of these recently. Mine is pretty simple (i'd have thought) trying to plot a long line of data, 75 columns (weeks) long. It's showing shipped product for the last 6 months (data source one) moving to arrears (data source 2) then firm/forecast (data source 3) however nothing I do can stop them from plotting on tp of each sodding other!
   Is there a way to plot so effectivly I can have one long bar chart so I can see trends without combining data sources?
   I'd like to keep seperate as generating them will be done via links into my main manufacturing system so trying to keep it simple!

   Cheers

   Posted 7 months ago #
2. Pyro - Member

   Plot one of your series on a secondary axis?

   Posted 7 months ago #
3. CaptJon - Member

   so you want three columns per week?

   Posted 7 months ago #
4. transapp - Member

   Ah, seconday axis that could do it.

   No, looking for one column per week, but 3 different series. Something like this

   1/1/2011 - 30/6/2011 Series One
   1/7/2011 Series Two
   7/7/2021 - 31/12/2011 Series Three

   Am now playing with scondays axis.

   Posted 7 months ago #
5. CaptJon - Member

   If it were a one off you needed it for i'd use the graph tool Illustrator to draw three column charts and then stick them together.

   Given the help i've been given for excel i'd draw it for you if you wanted.

   Posted 7 months ago #
6. Pyro - Member

   Alternative would be to plot the three series (assuming you've got relatively similar values) as a clustered column graph.

   Since you're essentially comparing three six month periods (irrespective of whether that's past, present or future), that would probably be my choice. You could then experiment with (thinking Excel '07) changing one series to a line graph (select one series, right click, select "change series chart type"). I'd probably do that for your forecast line.

   Posted 7 months ago #
7. CharlieMungus - Member

   Waddya mean on top of each other?

   Posted 7 months ago #
8. CharlieMungus - Member

   Try having three columns of data, for all weeks, with zero values where you don't want data, which essentially plots 3 series, but then you go to the options in Format data series and set the series over lap to 100% ( overlapped) so it all sits centrally in the columns. Then shift your gap width to about 50% for prettiness

   Posted 7 months ago #
9. matthewjb - Member

   What CharlieMungus said.

   Except if you use a stacked columns chart you don't need to bother formatting it as he describes

   Posted 7 months ago #
10. transapp - Member

    Thanks guys, all sorted.

    Posted 7 months ago #
11. CharlieMungus - Member

    Go on then, tell us what you did!!!

    Posted 7 months ago #
12. transapp - Member

    Charlie + Mathew + macros to sort / arrange data = happy daze!

    Just thought that given the same x axis, that goes from 1 to 100, that I would be able to plot 3 different graphs. One from 1-49, one at 50 and one from 51-100.

    Ah well, it's all come up smelling of roses. Thanks again

    Posted 7 months ago #
13. prettygreenparrot - Member

    Looks like I got to the party too late. Glad you solved the problem. If you hadn't I would have written:

    I have one word: Spotfire.

    Having got that out of the way, it sounds like you need to represent them as three distinct series. And that's where I'd have turned to SAS Graph in the past, R if I had time to meddle or Spotfire if I wanted to do this graph and get on to something else. I have found excel to be one of the most counterintuitive graphing tools available.

    Do you think you'll be able to repeat this in a few weeks time when you need to do it again?

    Posted 7 months ago #
14. CharlieMungus - Member

    You would have used R to plot a simple column chart??

    Posted 7 months ago #
15. transapp - Member

    Sure will. All my data I write once and once only, makek sure it's updatable with a few mins work at most. My job is to use the data not create it so I don't want to spend several evening working on it for a single hit. I'll be handing this off to a minion to keep updated in the morning!

    Will check out spotfire though

    Posted 7 months ago #
16. Travis - Member

    I'm needing some help too...

    I'm creating a simple Paretto chart, and I would like for the bar chart to change colours for anything that represented over 80%.
    How do I do that?

    Posted 6 months ago #
17. MrsMugsy - Member

    Travis, you should be in bed rather than working at 5:50 in the morning like me

    Posted 6 months ago #
18. Travis - Member

    It's lunch time here in Beijing, and my little boy is asleep, so it's one of the few moments that I can get some work done

    Posted 6 months ago #
19. Stoner - Member

    Travis, its not pretty but, to gain conditional formatting one way is to look at creating a condition driven table first and then stacking bar charts of specified formats:
    http://peltiertech.com/Excel/Charts/ConditionalChart1.html

    Posted 6 months ago #
20. CaptJon - Member

    Is it worthwhile pointing out how horrible most excel charts look?

    Posted 6 months ago #
21. Stoner - Member

    Some can look better than others - depends on the amount of effort you put in.

    Posted 6 months ago #
22. Travis - Member

    Thanks for that.
    I'll give it a go.

    Posted 6 months ago #

RSS feed for this topic

Reply

You must log in to post.