Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional histogram colors or borders (Excel 2003 SP2)

    I have a histogram/line graph combination - see attachment. The histogram data shows the number of machine failures by week # (thank you Hans V. for the ISO 8601 routine you posted!) and the line shows % of affected machines' (defective) part replacement by week #. I would like to further customize this graph so that the histogram shows or highlights the machines in the histogram that have a failure AFTER it has had a part replacement (via different color, shading,etc...).

    I am looking for some direction, as I have not done any VBA for graphs and would appreciate anyone who could point out the best approach for a solution, assuming the request is feasable.

    Thank you in advance for any help!
    Don Liebman
    San Diego, CA 92115

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional histogram colors or borders (Excel 2003 SP2)

    I am not sure exactly what or how you want to plot. What data would you like to plot and how do you want to make it look?

    Since you already have a stacked column are you just looking to add another stack to the existing ones? This could be accomplished by just adding another range.

    Are you after something else for the chart? If so could you elaborate?

    Steve

  3. #3
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional histogram colors or borders (Excel 2003 SP2)

    Steve,

    I want to change the current histogram to show a stacked column of each individual Machine # - this I know how to do, and I can change the current graph to show. What I am looking for is a way to highlight if a Machine has a failure AFTER the new part (i.e., Change Order) has been installed on that machine.

    Example:

    Machine #5 (Product 1) has a total of 4 failures. Machine #5 has also never had a part replacement to correct the failed part. This is currently reflected OK in the graph. The change comes in the situation when Machine #4, which has had a part replacement in week # 200538, has another failure in week 200549 - I want to see this event highlighted in some manner to stand out on the histogram. So, anytime after a part replacement has taken place on any given machine, if a failure occurs I want to make this stand out. Please note that the data in this example is purely made up, and that our company generally has hundreds of Machines in different product lines installed at customer sights.

    Please let me know if this clears up your questions
    Don Liebman
    San Diego, CA 92115

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional histogram colors or borders (Excel 2003 SP2)

    Still not sure what you want the chart to look like. So I made a guess. It is a stacked chart with each machine a particular color> Each failure after replacement of the machine is the 2 color (white and the Machine color).

    If not what you are after can you elaborate what the chart should look like?

    Steve

  5. #5
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional histogram colors or borders (Excel 2003 SP2)

    Steve,

    The graph looks good, I just need to add the line plot back to it. The only drawback is that there can be hundreds of machines, and the number of spreadsheet cells will be enormous!

    Thanks for the help!
    Don Liebman
    San Diego, CA 92115

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional histogram colors or borders (Excel 2003 SP2)

    I am glad I could help.

    <hr>The only drawback is that there can be hundreds of machines, and the number of spreadsheet cells will be enormous!<hr>

    This makes me think you should decide on alternate ways to present the data. I don't understand what you are trying to show from the chart so I can't offer any suggestions, but if that is what your people want to see...

    Steve

  7. #7
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional histogram colors or borders (Excel 2003 SP2)

    Steve,

    In short, the graph is used as a monitoring tool. The histogram chart is supposed to show the number of machine failures per week. We have the details logged of what the type of failure was. The line (on my original example) shows the fixed part replacement events (a.k.a. change orders, design enhancements, etc...), which should correct the failures. Some fixes are better than others, and sometimes the machines fail again even after the new parts have been introduced. This is where the highlighting of the failures after the fix has been introduced on a particular machine comes in to tell us that we have not completely fixed everything relating to these failures. When we look at the graph we hope to see the histogram bars getting smaller as the percentage of fixed applied grows.

    Thanks again!
    Don Liebman
    San Diego, CA 92115

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •