1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quality Control Chart from Online Data

Discussion in 'Discuss Data Visualizations and Charting' started by NCase, Jan 15, 2017.

  1. NCase

    NCase New Member

    Messages:
    9
    Hello,

    I have data from a sensor that records pH and temperature every five minutes for monitoring an experiment. I want to create to a quality control chart using a excel but I need the ability to import the raw data in csv file format and create a pH/Temp vs time chart with upper and lower control limits. I have included a manual created version of what I am trying to do. I hope someone can help.

    Best,

    Neil

    Attached Files:

  2. bobhc

    bobhc Excel Ninja

    Messages:
    3,348
    If you had read the rules, which only takes a few moments you would have read:-

    • Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out.
    • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
    • If you do cross-post, please put that in your post.
    • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
    • Please don't attach or post links to copyright material including Books, Videos etc. Your post will be deleted
    http://www.excelforum.com/showthread.php?t=1169804


    .
  3. p45cal

    p45cal Well-Known Member

    Messages:
    718
    I want people seeking help to be aware of and adhere to rules.
    NCase/Frank, if you're interested, I'll post the code and a workbook which will produce multiple sheets and charts like below with a single click of a button after you've updated the other forum site(s) with link(s) to all other locations you've cross posted this question to.
    upload_2017-1-16_21-16-21.png
    Last edited: Jan 16, 2017
    NCase likes this.
  4. NCase

    NCase New Member

    Messages:
    9
  5. p45cal

    p45cal Well-Known Member

    Messages:
    718
    Everything blue in the charts produced by the attached is related to pH and everything red to temperature. The SD levels and average are all touching their respective y-axes only. The +/- 3SD levels are very faint (you can only just see the temperature -3SD level in the screenshot in my last message at just above 18.5).

    Attached Files:

    Thomas Kuriakose and NCase like this.
  6. NCase

    NCase New Member

    Messages:
    9
    Hi,

    I found your setup extremely useful and it can multiple files from different sensors a the same time. I was wondering if the chart format in the attached file could be automatically applied when using your macro.

    The time range chosen to highlight the start and end of the work day (05:30AM to 05:30am the next day).

    Best,

    Neil

    Attached Files:

  7. p45cal

    p45cal Well-Known Member

    Messages:
    718
    So far, the data that you want to chart have always come in sets that cross over the 5:30 am point. You're saying that you want 2 plots in this case; 1 for the day 1 and one for day 2, based on a cut-off point of 5:30 am?

    At first glance this seems a little odd.
    Would it be acceptable to plot one set of data on one chart and have that chart's x-axis start at 5:30am before the data starts and that same x-axis finish at 5:30am after that data fininshes - so potentially the chart could be spanning 48 hours?
    If this isn't acceptable, how about if the data was still plotted on one chart, but I provided you with a button to toggle between seeing only the first 24 hrs and only seeing the second 24 hours?

    Regarding the more general chart format, I see no custom chart styles in the last file you attached; what aspects of the charts would you like to be included every time?
    NCase likes this.
  8. NCase

    NCase New Member

    Messages:
    9
    Hi,

    Thank you. Here is the logic of what I am trying to do.

    The work day is 6am to 6pm everyday. I using 5.30am as a start time to provide a baseline of what the sensors read before work begins and the readings change dramatically.

    After work stops we need to monitor how the system behaves overnight until 5.30am the next day. The system not in control at the moment but once it is stabilized it will become easier to monitor.

    The data is being downloaded from the internet and we will would like need to see 1 day, 2 day and 7 day chart to the overall trend on how the system is behaving. So maybe a toggle switch to display different day ranges might be best.

    I am including a excel chart template for the chart we are trying to create. It has to have arial font, size 16 for axis and 12 for title and legend.

    Best,

    Neil

    Attached Files:

  9. p45cal

    p45cal Well-Known Member

    Messages:
    718
    Then could we have a file which incorporates several days of data?
    Or point us to where these might be downloaded from the internet.
    NCase likes this.
  10. NCase

    NCase New Member

    Messages:
    9
    Here is an example of more than a weeks sensor readings.

    Attached Files:

  11. p45cal

    p45cal Well-Known Member

    Messages:
    718
    See attached; button on Control sheet.

    Attached Files:

    NCase likes this.
  12. NCase

    NCase New Member

    Messages:
    9
    Hi,

    This is awesome. Is it possible to:

    (1) have the graphs chart without the x and y axis gridlines. Its easier to see without them.

    (2) have a button to turn on and off the average, upper and lower limit lines.

    (3) if I import data from 3 separate sensors. Plot the same min max range for pH and Temperature. It makes daily comparison much easier to interpret.

    Best,

    Neil

    Attached Files:

  13. p45cal

    p45cal Well-Known Member

    Messages:
    718
    Known as milking it.
    See attached.

    Attached Files:

    NCase likes this.
  14. NCase

    NCase New Member

    Messages:
    9
    Milking is also known as pushing my luck. I totally appreciate this and you have dramatically reduced my data crunching so I can focus all my efforts on helping people solve their pH/Temperature process problems.

    Best,

    Neil

Share This Page