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

  • 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

NCase

Member
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
 

Attachments

  • pH Temp vs Time Quality Control Chart Dashboard.rar
    82.2 KB · Views: 17
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


.
 
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:
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).
 

Attachments

  • chandoo32747_2017-01-14 pH Temp versus Time2.xlsm
    112.9 KB · Views: 22
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

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

Attachments

  • 2017-01-21 pH Temp versus Time.xlsx
    74.2 KB · Views: 4
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?
 
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
 

Attachments

  • Mil PortTemplate.rar
    5.3 KB · Views: 6
Then could we have a file which incorporates several days of data?
Or point us to where these might be downloaded from the internet.
 
Here is an example of more than a weeks sensor readings.

Then could we have a file which incorporates several days of data?
Or point us to where these might be downloaded from the internet.
 

Attachments

  • deviceReport-2017-01-22 15-23-34.rar
    17.6 KB · Views: 6
See attached; button on Control sheet.
 

Attachments

  • chandoo32747_2017-01-14 pH Temp versus Time4.xlsm
    129.5 KB · Views: 6
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
 

Attachments

  • Log Files 6.rar
    22.8 KB · Views: 6
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.
Known as milking it.
See attached.
 

Attachments

  • chandoo32747_2017-01-14 pH Temp versus Time5.xlsm
    133 KB · Views: 10
Known as milking it.
See attached.

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